I have two FMP tables, Invoices and LineItems. I need to denormalize these (creating one record per LineItem). These flattened records must be exported (.csv or .tab is fine) with their fields in the exact order that is required by the target program.

There are some Invoices and LineItems fields that do not need to be exported; conversely, there are some fields that the target program expects that I can't provide but must account for by a null value.

What is the best way to go about this?

Should I create a "Sales" table in FMP into which to create the flattened records, and export from there?

The frustrating thing is that, unless I've overlooked something, the Export dialog box -- where you choose the fields and the order in which to write them -- does not present the fields in any useful sort of order. I tried creating the "Sales" table and took care to create the fields in the order in which they needed to be exported, but the Export dialog box does not seem to present them in creation sequence. There are well over 100 fields, and I don't want to sort them using drag and drop unless I absolutely have to.

It all led me to think there was probably a much easier way to do the job. Suggestions?

Thanks, Chap

One record per Line Item

Export from the LineItems table

Null Fields

Will creating blank fields within the LineItems table solve this problem for you? Maybe global fields soeach record doesn't have to hold a null value.

Sort order:

File>Define Database>Fields(Tab)>View By(Drop Down)

There are 4 options (including a Custom Order, which may work best for your situation). This will determine how the fields are shown in the Export dialog box.

Null Fields

Will creating blank fields within the LineItems table solve this problem for you? Maybe global fields soeach record doesn't have to hold a null value.

Good idea. Thanks!

Sort order:

File>Define Database>Fields(Tab)>View By(Drop Down)

There are 4 options (including a Custom Order, which may work best for your situation). This will determine how the fields are shown in the Export dialog box.

So it does -- my mistake.

Thank you!

Chap