Populating a Lookup/Calc Field with a Field from Related Records.
Posted on 2006-11-15
I have a slightly complicated issue that i could use some assistance with. First of all I am using FM8.
I have a multi-table database with _many_ crazy relationships. I now have to figure out how to get a single field in the PO_items table to show all the related products from the ProductList_kits table. This is kinda confusing so stop me if i get carried away.
- The "PO" table is related to the "PO items" table, which allows the users to add multiple parts to their PO's via a portal.
- The "PO items" table is related to the "Product List" table which allows for cascading value lists to display value's from the product list allowing users to easily add items to the PO.
- The "Product List" table has two tables related to it "Product List_venderPN" and "Product List_Kits".
- "Product List_venderPN" allows the users to have multiple vendors selling the same part with different vendor part numbers.
- "Product List_Kits" allows for the users to create a kit out of the items they sell. The kits could have any number of parts from the main "Product List" to allow for more efficient ordering of highly used parts.
My problem now lies with creating a calculation/lookup field in the "PO items" table which shows a list of the product descriptions from the related records in the "Product List_Kits" there could be any number of items in the kit, and i need to be able to show the kit contents on the Printed PO.
I would prefer to have this auto-lookup, for accurate ordering at the time. I am afraid that if i had to script this process that if the users adjusted the kit contents at a future date it could change the previously ordered kits when printed.
OK that's a mouthful.. any help would be greatly appreciated, If i did not explain any part of this issue please allow me to clarify.