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.
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.
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:
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: