Solved

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

Posted on 2006-11-15
1
259 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
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Pop up windows can be a useful feature of any Filemaker database.  Though best used sparingly, they can be employed in a multitude of different ways, for example;  as a splash screen at login, during scripted processes to control user input, as pick…
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…
This video discusses moving either the default database or any database to a new volume.
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

760 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now