Populating a Lookup/Calc Field with a Field from Related Records.

Hello,

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.

Thank you,
~ Criss
LVL 1
criss_mceAsked:
Who is Participating?
 
lesouefCommented:
I suggest you create a description field (if not existing yet) for the kits, which is a concatenation of all items description. this could be easily created when you create the kits with a simple loop gathering all linked items description to build up the kit description. You could also use a dynamic list based on linked fields of the kits, but that is rapidly slow if the items are numerous in a kit.
If this is only a matter to get the info at print time (no need to store it), then a simple loop can fetch the items descrition for every kit in the PO and put it in a text field of the PO. Let me know if you need more about this, how to make the loop, etc... the basic principle is:
for a given kit:
goto related records (items)
goto 1st
loop
define variable $desc = $desc & "pi" & items description
goto next record (exit on last)
end of loop
... back to layout where you were
define kit comment field = $desc
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.