tonyreay

well.. it kinda helped..except that the way I get these files with about 2500 items in each one prevents the feasibility of sitting and typing an a or b in front of each number.. can it really be that there's no formula that simply says to a field.. "look at this "Product" field and if there's an "A" go to the A lookup file to get and post the data. If there's a "B", go to the B lookup file. then we can simply type in the item and mark the "Product" box a or b. I'm sure it can be done, I just don't know how to write the formula relationship/lookup - although I think it involves "if" and 'case" commands. anyway, thanks for the help so far - don't give up on me.. we'll get there ------------------ remember to hug your kids.. it's the answer!

quote: Originally posted by LiveOak: Wow, FileMaker rage! I think I was able to pick out the question . You need to use a new composite field to perform your product lookup. You will also need to import all your product information into one file. Create a the following fields: PartType (text) with value list "Avon", "Betterware" NewPartNum (calculation, text, indexed) = Case( PartType = "Avon", "A" & OldPartNum, PartType = "Betterware", "B" & OldPartNum, "") Replace "OldPartNum" with whatever the name of your current part field is in each file, respectively. These fields are created in both Orders and Products. Drive your lookup from this new composite part number field, not your original part number. This solution assumes you move all your product information into a single file and keep the information separated by using the field PartType. If you don't wish to do this, the solution becomes quite a bit more arcane and really offers no advantage. -bd It is .. wait, firstly THANK YOU for responding so quickly... it is clear that you know what the problem is... and even clearer that you know the solution... now, if only we can get a translator..hahaha if you could kindly rephrase your instructions using the following terminology then I will understand which words apply to me and which to filemaker instructions... in my current two lookup files I have the following fields.. Lookup file #1 contains Item Number Page number Brand/style Item Description Price Price type (sale, discontinued, special.. etc) Lookup file #2 contains Item Number key code It took me some time to get these two companies to be comfortable with the idea that I should have this information BY COMPUTER, (even tho I could obviously type it all out in longhand). They supply it in two seperate files because they come from two seperate departments. Unfortunately, we need ALL the info contained in both files and, as you've probably realised, are not capable of figuring out how to merge them.. so we have two lookup files. Anyhoo.... I type in, on the master... say.... Avon.. invoice, the product number. One set of instructions goes to the first lookup file and produces the wealth of info.. typing it neatly into the correct fields.. and the other goes to the second lookup file and does the same. your advice to... "Create a the following fields: PartType (text) with value list "Avon", "Betterware" NewPartNum (calculation, text, indexed) = Case( PartType = "Avon", "A" & OldPartNum, PartType = "Betterware", "B" & OldPartNum, "") Replace "OldPartNum" with whatever the name of your current part field is in each file, respectively. These fields are created in both Orders and Products. Drive your lookup from this new composite part number field, not your original part number. This solution assumes you move all your product information into a single file and keep the information separated by using the field PartType." unfortunately lost me right after the "Create a the following fields: PartType (text) with value list "Avon", "Betterware" Assuming that the word "PartType" is your version of one of my fields.. which one...?? and, after I make it a text field and call it that, how do I make the value list? I really don't mean to make it appear so goshdarn hard.. I thought it would be a simple matter (and, to you, I'm sure it's mind-numbingly soring to have to deal with goldfish brains like myself wanting instructions on how to reinvent the wheel..but, having said that - thanks again... I guess just type slower. ps the good news is that I should only have to be shown once... ------------------ remember to hug your kids.. it's the answer!

hello there.. well, we have discovered what would appear to be a Filemaker pro black hole of information.. nestled somewhere in the deep fissure between idiot knowledge and hi-tec egghead type computerese. We