Jump to content
Sign in to follow this  
Mandu

Flattening parent/children and exporting

Recommended Posts

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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×

Important Information

By using this site, you agree to our Terms of Use.