Print 
PDFMany end users appreciate FileMaker’s ability to export a found set of records in Excel format — it’s fast, straight-forward and empowering. One request that I have received from clients multiple times over the years is: make the exported field names in the top row of the spreadsheet “human-friendly”…
For example, instead of this:
…perhaps they would rather see this:
Another request has been to allow data from multiple source fields to be combined into single export columns, as per columns A and C here:
And a third request has been to provide an interface so that users can pick and choose fields to export, including (where appropriate) related fields, without having to navigate the complexity of the native FileMaker export dialog.
A while back I decided to do something about these requests, and today’s demo file, excel exports, part 1, is the approach I came up with at that time.
(In subequent articles we’ll look at extending and/or streamlining the approach, but today we’re going to start out simple.)
To recap, clients have requested three things:
- User-friendly column headings
- Calculated fields or the virtual equivalent (I call these pseudo-fields)
- An interface to make it easy
Well, with regard to number 1, I have no intention of changing my geeky field names, e.g, “name_first”, to something as warm and fuzzy as “First Name”; and, with regard to number 2, I would prefer not to define a bunch of new fields in the database just so they can, for example, have City, State and Zip in a single spreadsheet column.
The question is: can I have my way, and still give them what they want? And the answer is a qualified yes, if I’m willing to invest some time and effort.
Confession: I haven’t found a way to get the “user-friendly” column names in the top row of the spreadsheet to appear in bold. But perhaps you, dear reader, know a method and are willing to share, e.g., exporting as XML and transforming via XSLT?
The approach used in today’s demo file is…
A. Populate a $$sessionKey variable at startup (helps ensure multi-user friendliness):
B. Create and populate a translation table, called “aliases”, with two fields:
1. Input — will contain “user friendly” column names
2. Output — will contain “translation code” for each input entry
C. Base a value list on the Input entries.
D. Define global fields in Contacts, and provide an interface for the user to populate them from the above value list; each global will correspond to a column to be exported.
E. Create separate relationships from each global field to the alias input field.
F. In the Contact table, define a separate calculated field (unstored, text) corresponding to each global field… the calculation will reach across the relationships defined in step E and use the Evaluate function to translate the “input” into the “output”.
G. Create a utility table called “excel”, which will serve as a clearing house — as per the script in step H, records will first be imported into it, and then exported out of it.
H. Define an “Export to Excel” script along the lines of the following (this is an excerpt; see the demo for the full version).
The script uses “save as excel” rather than “export records” as a convenience. When you use “save as excel”, FileMaker automatically exports all fields on the active layout… and of course the user is not presented with a plethora of file type options when the “Save As Excel…” dialog appears, but instead sees this simple set of choices:
And, finally, here’s what the actual output looks like:
In upcoming articles we’ll look at expanding this approach to handle multiple source tables, and also reducing clutter on the Relationships Graph.
Excellent feature! We have this one in our backlog and might be implementing it soon. Can’t wait for the 2nd installment and I will try to work the demo file and post results/comments here.
Thanks JR — one thing I like about this technique that I forgot to mention in the article is that if the end-user is somewhat FM savvy, you can give them access to the aliases layout, so they can create their own entries.
excellent article as usual.
As you started to mention issues with exporting is if the user wish to use a related field – and uses “Export As Excel” – filemaker will include the children data, with in the export leaving blanks for the parent data fields.
Where as Save As Excel only considers the values on the record or the first value across the relationship.
Thanks Stephen… I hadn’t even considered that. This is what I love about having a blog… how much I learn in the process.
Kev, I don’t know if you’ve ever tried, this, but virtual list layouts will export directly to Excel and look just like what you see in FM.
So if you build an array of VL fields, each becomes a column in Excel. I’ve done a cross-tab report with sub-summaries and totals all built into the variables for the VL, and once exported to Excel, all that is preserved, including blank lines, labels, etc.
The best part is that VLs don’t impact the RG at all, unless you’re gathering related data, and often you can use existing relationships to do that. In my cross-tab, I was totaling summary data for a range of months, with months on the horizontal axis, and companies on the vertical.
And of course, your choice of data to put in each column with the friendly labels will work just fine in a VL.
Thanks Lynn… Virtual List is definitely on my agenda for “part 3″ or “part 4″ of this series.
Can i say Mr. Frank
That the frequency of your posts are admirable and i believe i am learning so much from you and just want to personally thank you for your contributions to the FMP community.
: )
-ian
This looks like what I need, but it terrifies me. Trouble is, my exported fields come from 2 different tables, so some headings show as ‘Client:FirstName’ etc.
Is this approach going to help me? I swear I will work through it and try to understand, but if it only works for single table exports, I really can’t afford to spend the time!
Just a hint? Thanks so much. You have shown me how very, very little I know about Filemaker!
It definitely works with multiple tables, as you will see in part 3.
I’ve used Applescript in the past to format excel exports from FM (and even to calculate URLs in excel from FM data for UPS and FedEx shipments).
That way, the whole excel dictionary is available for formatting, bolding, column width, etc..
I was looking for a more native FM way to do it (and enable my Win based colleagues) and your method may do it, thanks.