FileMaker: Export to Excel with custom column names
Is there a problem?
How often did your customer or your boss come to you with this question: “I want an Excel export from FileMaker, but I want readable column names and not the mess you usually put in there!”. As long as the export is not of too much of importance, you turn your head away and hope for time doing its work and make the request magically fade away. But if the export is an important feature of the FileMaker solution, this will come back, and then you need to come up with something clever.
Yes, we have a problem!
The point here is that FileMaker uses its field names as column names in Excel exports. There is no way around this. For related fields, FileMaker shows the fully qualified field names, such as “TO1_TO2::Field2” which makes most users go crazy. To keep them from banging their heads against windows, there are tricks out there that insert a first row into a text file (http://fmforums.com/forum/topic/84008-adding-first-row-with-field-names-…). Other solutions use XML and XSLT but if you are not a geek, that is just not your piece of cake. Also, we want a native Excel document format (.xlsx or .xls) and not trick Excel into opening a text file.
Good news for you
The good news is, that if you want to at least put some effort in a FileMaker native solution, you can create simple yet dynamic solution for your problem.
Could you please be more specific?
Well, OK then, our solution relies on:
- Creating an extra table and appropriate relationships to the source tables for the export
- Field comments to specify the filemaker fields, which may include related fields
- Indirection to get values using the GetField() function.
To be extra specific…
I refer to the attached demo file. Below you will find a download link. The demo has a Customers (CUS) and an Invoice (INV) table. It is possible to export customer or invoice data using custom column names by just populating the ExportTable with the id’s you want.
This is how it works:
- The ExportTable has an __id field which holds the primary keys (pk’s) of the records of the source tables (Customers or Invoices in this case) which you want to export
- There are relationships from the ExportTable to the source table, i.e. from EXT::__id to the pk’s of the source tables
- All other fields in ExportTable are calculations. It is important that you check the ’unstored’ option to make sure FileMaker recalculates values on demand
- The calculated fields each have field comments that refer to the field from the source table that you want to export. This is the only hardcoded part of the solution. It means that if you change a field name in a source table, you also need to adjust the field comment
- The calculations in ExportTable are all the same and dynamically retrieve values from fields in the source table. I use the Self() function to get the current field object, the FieldComments() function to get the value from the field’s comments, the GetFieldName() function to dynamically get the field name as a string from the field object and finally the GetField() function to get the actual value following relationships from ExportTable to source table (or related, you can of course add related TO’s and link them to the source table and refer to them). Add new fields by duplicating an existing one, give it a friendly name and change field comments to refer to the field you want.
- You can export records to Excel using just the columns you need. The demo file has 2 scripts to export Customer and Invoice data.
How to fill in the id’s in ExportTable is up to you. You can use the Import script step, loop through a source recordset and then again in ExportTable to fill id’s, or use a virtual list technique to fill a global variable with a list of id’s and define EXT::__id itself to retrieve values from that list.
I did not make this on my own. Credits for this technique go to the team members Joris Aarts and Jan Stieperaere here at ClickWorks, who came up with the original ideas and tips.
To be honest, the technique for me is experimental. I did not yet take this to a production environment yet, but soon will. If you like and use this technique, have additional tips or see pitfalls, feel free to let me know by leave a comment on our website.