Found-sets of Invoices & related LineItems

[Mandu]

I'm working on a script to import Invoices and their related LineItems from a small, "staging" database to my main database. The staging database contains Invoices and LineItems tables, just as does the main database.

[Note: I'm using relational terminology ("database", "table") but this IS a FM6 database]

The script, running in Invoices_Main.fp5, imports from a previously-created found-set in Invoices_Staged.fp5. No problem there. But then I want to import into LineItems_Main all LineItems_Staged records that are related to records in the found-set of Invoices_Staged.

- - -

I guess my problem really isn't about Importing as much as it is this: given a found-set of Invoices, how do I then create a LineItems found-set containing all records related to records in the Invoices found-set? (Once that's done, it's trivial to import just the records I want)

Thanks for any hints -

Chap

[Wim Decorte]

You could loop through the found set in 'invoices' and set a global text field to a return delmited list of the invoice IDs.

Through a relationship from that global to the items file (on invoiceID) and a Go To Related Record script step you'll create a found set in the invoice items file that matches all items of all the invoices in the found set in the invoices file.

If you don't want to create a new relationship for this, you can script it too by again looping through all the invoices in the found set, taking their ID, running a find script in the items file that extends the found set with items that match the temp invoice id.

[Mandu]

Thank you! That did exactly what I wanted. (I went with option 1 - creating the new relationship.)