help with a simple database

[shayori]

I've been using filemaker to make simple forms (ie. purchase orders, work orders)for about 5 years but now I need to add a sort of lookup feature and can't get it to work. One section of my work order form has a place for quanity, part#, description, price, and extension and right now we enter everything ourselves. What I want is when someone enters a part # it fills in the correct description and price. I created a new file and put the part # info in, and in the master file when you type in the part # if it's not in the other file it does leave it blank, but if it finds the part # in the other file it puts the first description and price in rather than the one associated with the part #. I've tried doing this with repeating fields and with a seperate record for each part # and neither work. Can anyone tell me what I'm doing wrong. Thanks

[overrider]

you must have one table called parts and another called for example purchases. now relate these tables using your partnumber. lets say you named the relationship parts_lookup. now in your purchases file, create a field called for example price, and make it a lookup field, looking up information via the relationship parts_lookup -> price. now whenever you make a new record in your purchases file, and fill in a part number, it will lookup the price it finds in your parts table

[shayori]

That's basically what I have, but no matter what part # I type in in the master file, if it finds the part # in the look up file it always puts down the first description and first price instead of the description and price that go with the part #.