Solved

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

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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…
Having just upgraded from Filemaker 11 to Filemaker 12 over the weekend, we thought we would add some tips for others making the same move.  In general, our installation went without incident. Please note that this is not a replacement for Chapter 5…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…

691 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