I created a db to track inventory, assigned my own Product ID to each product. I purchase the same product from different suppliers depending on price then I create a purchase invoice (based on my suppliers invoice)to enter the new purchases.

My problem is this; when entering the new purchases I would like to use the suppliers product ID #'s when available & obviously my product ID #'s when I purchase from the cash/carry. So I want all the different supplier ID #'s & my ID #'s to represent the same product.When I enter either Product ID # I want the appropriate Product Description to appear in the invoice, I don't know how to do this, I've included a sample of what I've done, maybe my relationships are wrong, I'm getting very confused. Can anyone shed some light on my confusion, any help would be greatly appreciated. I'm not very experienced with FM. I'm going to read the Whitepaper for Novices (as soon as I download it) Thanks in advance.

Derek_Roberts_Inventory_REV.zip

You will need another table to remember the Supplier Product Codes and the associated ProductIDs from your system. Something like the attached structure should do.

I'd also recommend you fix the relationship options between your tables so that you can't add and delete parent records from a child record. What you have now will pretty much delete all the records when any record is deleted.

Derek_Roberts_Inventory_REV.fp7.zip

Thankyou so much for the help, I will take your example back to the drawing board & try to impliment it. I will also correct the "Delete related records"