Import unnormalized data into normalized tables

[Mandu]

I have a file of orders to import into the Invoices and LineItems tables of a FM database. Each record in the orders file contains both a line item and the customer's shipping information. Obviously, the shipping information belongs in Invoices and the line item belongs in LineItems.

Right now I use two Import passes - one to add records to Invoices, and one to add records to LineItems - and finally I remove the duplicate Invoice records.

Is there a better approach? One that eliminates the addition - and subsequent removal - of redundant Invoice records? (Better yet, a single-pass solution.)

Thank you,

Chap

Edited March 18, 2006 by Guest

[Søren Dyhr]

You might find inspiration in this template of mine:

http://www.filemakerpros.com/Splitting.sit

--sd

[Mandu]

Yes... used a two-pass import.

Use a flat FM table ("flatfile") to copy the external file into. Means we only have to ask user ONCE where the input file is. :

Then import once from flatfile into LineItems; remove duplicates from flatfile based on OrderNo (which is kindly provided by the external file); import flatfile again, this time to Invoices.

Thanks!