27 Mar 2017: see User-Friendly Excel Exports, part 6 for an updated approach.
Well, we’ve reached what may well be the final posting in this extended series of articles. We’ve covered a lot of ground, and this article assumes familiarity with what has come before (in parts 1, 1.1, 2, 3 and 4).
Today we’re going to look at a couple “Virtual List” implementations of the User-Friendly Excel Export technique, which will, among other things, allow us to eliminate these calculated fields from the Contacts table…
…as well as the corresponding set of fields in Donations.
Bruce Robertson’s Virtual List technique has made a couple previous appearances on FileMaker Hacks…
…so I won’t rehash its merits in detail here, but if you aren’t familiar with it, I recommend you check out one or both of these earlier articles.
Basically, we’re going to move the parsing logic out of calculated fields, and into the “export” script, wherein we will construct an array for each column. The reason there are two demos today, is because in excel exports, part 5a we declare a separate $list_xx[repetition] variable for each cell in the spreadsheet, whereas in excel exports, part 5b we build a large return-delimited $list_xx variable for each column, and then use GetValue to parse the row from the column. I happen to prefer the first method, but decided to include the second for the sake of completeness.
This means that in the 5a demo, the “Excel” table fields are defined thus:
…but in the 5b demo, like so:
I’m not going to dwell on the export script at length, but would like want to draw your attention to a few points of interest. First off, I should mention that in both demos, the variables are “dynamically instantiated”, i.e., created at run time (for more on this, see Dynamic Variable Instantiation).
The scripts in both demos are remarkably similar. In both an outer loop walks the records in the found set to be exported, and an inner loop populates array variables for the current record.
Here’s the above highlighted step in 5a:
And here’s the same step in 5b:
In 5a we’re declaring a single $var[rep], but in 5b we’re appending a new row to a growing list $var. Since a hard return is a value delimiter in 5b, we need to ensure that there are no embedded returns in the data itself, hence the substitution on “theRawData”.
Finally, a couple thank yous: First, of course, to Bruce Robertson for coming up with the Virtual List technique, and for tirelessly answering my many questions; Second to Geoff Gerhard who helped debug and refactor the above code in both 5a and 5b.