Transposing multi line data into one line for export

[Jonathan9]

OK this is a very odd request (clients eh?!)

A system manager requires an export to native XLS Excel file (ready for a very inflexible accounting system) from multi line item records to be shown on one long line for each master record.

Example for simplicity:

[color:blue]Master Record, Orders OrderCode, OrderDate, OrderInfo etc

[color:blue]Related Lines OrderLines(1 or more): Item, Cost, Tax

Now they want an export file from this data which will show the following:

OrderCode, OrderDate, OrderInfo, Item(line1), Cost(line1), Tax(line1), Item(line2), Cost(line2), Tax(line2), Item(lineN), Cost(lineN), Tax(lineN)

Added for clarity - Each field needs to be in a separate cell in Excel, one row for each record, the commas above are just used to describe the requirement

E.g.

A1=OrderCode, B1=OrderDate, C1=OrderInfo, D1=Item(line1), E1=Cost(line1), F1=Tax(line1), G1=Item(line2), H1=Cost(line2), I1=Tax(line2),etc

Although we can put a cap on the lines to 20, it still seems a very tedious process to set-up a large temporary table and transpose the data from the master and line items into a long static record before running an Excel export.

I know I could export in a more traditional format first into excel and then run a VBA script to transpose, but ideally I just want a one-click export from FileMaker...

Any ideas/tips on how to achieve this easier in a script, without just hard coding it all?

Thanks all (this will be an 8.5 and v9 system, so will need to work in v8.5 as well as v9)

Edited April 30, 2010 by Guest
clarify excel export

[TheTominator]

It looks like your data is simple enough for you to "manually" generate a CSV line for each. By simple enough, I mean that the data should have no commas, quotes or line breaks in any of the data fields.

I think you are describing a two-table structure Table: Orders and Table: Items

Try this: (I have not tested it)

In your Items table a Calculation field returning a result of type Text.

cItemLine_Export = Item & ", " & Cost & ", " & Tax

In your Orders table create two Calculation fields each returning a result of type Text.

cItemLines_Export =

Substitute(List(Items::cItemLine_Export); "¶"; ", ")

cOrdersAndItems_Export =

OrderCode & ", " & OrderDate & ", " & OrderInfo & ", " & cItemLines_Export

From your Orders table, export the single field cOrdersAndItems_Export into tab-delimited format.

If you want to more gracefully handle Orders with no items, you can do something like this:

cOrdersAndItems_Export

OrderCode & ", " & OrderDate & ", " & OrderInfo &

Case(

IsEmpty(cItemLines_Export); ""

", " & cItemLines_Export

)

Edited April 30, 2010 by Guest

[Jonathan9]

Ohhh The List with Substitute functions, what a great tip, thanks TheTominator!

Ideally they want a one click from FileMaker into an Excel (native formatted, data in each cell) export, but the CSV could be a great starting point, and worst case if I can't come up with anything else, they can open, format within Excel and save as XLS.

Edited April 30, 2010 by Guest

[comment]

one long line for each master record.

Example for simplicity:

...

OrderCode, OrderDate, OrderInfo, Item(line1), Cost(line1), Tax(line1), Item(line2), Cost(line2), Tax(line2), Item(lineN), Cost(lineN), Tax(lineN)

What is supposed to separate the fields in this "one long line"? If it's a comma, what should happen when the Item field contains a comma?

[Jonathan9]

Ohhh The List with Substitute functions, what a great tip, thanks TheTominator!

Ideally they want a one click from FileMaker into an Excel (native formatted, data in each cell) export, but the CSV could be a great starting point, and worst case if I can't come up with anything else, they can open, format within Excel and save as XLS.

I should have made it clearer, each field is to appear in a separate cell on an excel row. I've edited the original post to add more details.

Hence my first thoughts were to create a large temp table and manually/loop transpose the data in a script, then export this temp table to a XLS file.

Obviously if we were to go down the CSV route with using any delimiters this will be an issue if the file passes into a CSV or another format before moving to Excel.

However if there no other solutions, a script could remove/replace any offending characters first from the actual data set...

Any other ideas?

Edited April 30, 2010 by Guest
Added Excel export detail

[comment]

There's no way to export this directly to an Excel file. You can use a .csv (or, preferably, tab-separated) format as an intermediate.

Another option is to export as XML, using a XSLT stylesheet to transform the output to Excel 2003 XML format. This is not a "regular" Excel file, but it is a native format to Excel.

[Jonathan9]

There's no way to export this directly to an Excel file. You can use a .csv (or, preferably, tab-separated) format as an intermediate.

Another option is to export as XML, using a XSLT stylesheet to transform the output to Excel 2003 XML format. This is not a "regular" Excel file, but it is a native format to Excel.

I agree - I guess not. I will have a look at the XML format / XLST style-sheet, as the users are running Office 2003, thanks comment.

My follow up was just wondering if there were any other ideas of though: between the manual transpose I first suggested (which is the most tedious, but gives perfect data within FMP and then a one-click option to the user to export directly into their required format); and this Line/Subst idea which creates a calc with delimiters to move into an intermediate format.

[When I do this using Access I open up an instance of Excel and script the actual creation of the data from the query in Access directly into the correct cells in Excel, which is the most efficient way via the MS route]

[TheTominator]

You could set up FileMaker as an ODBC source and do everything from the vantage point of Excel.

[comment]

just wondering if there were any other ideas of though: between the manual transpose I first suggested...

You could automate the "manual transpose" - but whether manual or automated, this method still requires a cap on the number of order items.

When I do this using Access I open up an instance of Excel and script the actual creation of the data

I know very little about Windows/Office. Perhaps you could do something similar using the Send Event[] script step and/or VBScript.

[comment]

An afterthought:

Is the Excel stage actually required? IIUC, the ultimate target is an accounting application - probably not a very recent one, judging from the requirement. I would guess there is another export from Excel to some text format in the process. It could very likely be much easier to produce the text format directly from Filemaker.