Solved

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

Posted on 2006-11-15
1
283 Views
Last Modified: 2010-04-27
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
0
Comment
Question by:criss_mce
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 28

Accepted Solution

by:
lesouef earned 500 total points
ID: 17950105
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Conversion Steps for merging and consolidating separate Filemaker files The following is a step-by-step guide for the process of consolidating two or more FileMaker files (version 7 and later) into a single file with multiple tables. Sometimes th…
Problem: You have a hosted FileMaker database and users are tired of having to use Open Remote or Open Recent to access the database. They say, "can't you just give us something to double-click on rather than have to go through those dialogs?" An…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…
This video shows how to use Hyena, from SystemTools Software, to update 100 user accounts from an external text file. View in 1080p for best video quality.
Suggested Courses

740 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question