AnsweredAssumed Answered

Help requested with structure of loop within loop and going into a portal

Question asked by VictorWarner on May 25, 2015
Latest reply on May 28, 2015 by philmodjunk


Help requested with structure of loop within loop and going into a portal


I am new to filemaker and I have imported a database from another database program, which consists of 3 tables. And I need  to replicate a particular type of table based on the 3 tables, I believe I need to use the Loop command (and perhaps a loop within the first loop, perhaps with an If statement too), but I cannot understand how to structure the script to produce what I need.  Help or pointers would be very gratefully receive.

What follows is the briefest description of how the tables are created and what I am look to achieve I could manage.

Table 1
Catalogue number

Table 2 
Catalogue number, name of item, price of item 

If the item has a number of sub-items then the price of item is a calculation (sum) based on the values of price of sub-item in Table 3. But not all items in Table 2 have sub-items.

Table 3
Name of item, sub-item name, price of sub-item

I have created relationships between Table 1 and Table 2 (via Catalogue number), and Table 2 and Table 3 (via name of item). And Table 2 has a portal to Table 3.

I need to create a 4th table (for onward export as a CSV) based on the possibilities :

1. if no sub-item: Name of item, price of item
2. if there is a sub-item and for first sub-item of name of item: (concentated) Name of item - sub-item name, price of sub-item
3. for subsequent sub-items: sub-item name, price of sub-item

An example: If there are two records in Table 2:

002, Computer,24.00

And for the second record there are a list of sub-items in Table 3:

Computer, mouse, 12.00
Computer, keyboard, 11.00
Computer, USBcable, 1.00

Then the result in the 4th Table would be: