27 Mar 2017: see User-Friendly Excel Exports, part 6 for an updated approach.
Many 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.