I have an invoice layout (it's a Form) in my database that I need to export to Excel. Fields are sometimes stacked on top of one another, side by side, etc. When I export as Excel, or Save as Excel, it just lays out all the fields as columns side by side in a table. I need to export this data to excel so it looks like it does on the Form layout. What's the best way to do this?

Thanks,

Mike

What's the best way to do this?

Hi Mike,

Excel isn't the best choice here. Excel will always produce rows (which are FM records) and columns (which are FM fields). If you want a reproduction, Save As PDF. Otherwise you will need to use a layout where the fields are not stacked and only the fields you wish to display are on the layout but it STILL will not end up looking like your invoice.

And if you have a portal (lineitems), the first row of the lineitem will have the invoice information but the rest of the portal lines will not.

Thanks, LaRetta. Would exporting as XML and using an XSLT to transform give me the ability to reproduce the layout?

I don't know. :idunno:

Possibly. I am not sure how one stacks fields on top of one another in Excel. Try recreating the form in Excel and saving it as Excel 2002/2003 XML format. If it still looks alright, you can use the file as the basis for your XSLT style sheet.

OK. Thanks, guys. So, if a client needs access to data in an editable format is it kind of standard practice to deliver both a formatted PDF and then an excel doc of just the line items portion of the invoice in a table view?

I could try the XSLT approach to reproduce the form layout, but if it's non-standard I'm not sure I want to take the time to write it.

I am not aware of any standard practice in this area. I think the client has access to editable data in Filemaker? Though if it's past invoices, they shouldn't be allowed to edit them anyway.

Sorry, I should have said "vendor" before instead of client. Yes, the client would most certainly have access to editable data.

I think I'm going to give that XSLT approach a shot. Would come in very handy.

Thanks again!

Why would you want anyone to edit data outside of the database? What good is the data in the database, then?

Well, if the vendor needs to injest the invoice into their own database it would need to be in a format amenable to that. A PDF won't work so well. They would need to re-enter all the data. That's why we're looking to excel.

So, yes, they would not be editing data in our database. They'd just be injesting into their own database.

If they need to import the data into their own system, then the layout is not important. On the contrary, they would want the data to be organized in a standard rows/columns format (spreadsheet, tab or comma delimited, or XML) that can be easily imported.

Edited July 24, 201015 yr by Guest

That's a good point. I'll check with them and see if they'll take a standard columns/rows table, CSV, XML, or tabbed.