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.
Kevin
Great article again, thanks
Quick suggestion for simplification
You can remove both of the Set Variable ($counter) steps by making the Exit Loop If to be the following calculation and then move it before the Set Variable ($x) step
Let ( $counter = $counter + 1;
$counter > $theValueCount
)
John
Thanks John. That’s a great tip, and makes perfect sense now that you point it out, but I confess it had never occurred to me. I am going to implement it right now on a client project, so thanks again.
Hey Kevin;
thanks for a very Impressive file(s) again, however the technique is so advanced that it may take me 1 month to get through this one.
LOL
appreciate the homework though.
-i
Thanks, Kevin, for this technique. I found a link to it on the FM Developers forum @ LinkedIn. The timing was ideal, as I just had a request for such a feature. I was able to adapt, integrate, and deliver it in a few hours. On my next go-round, I’ll move the global fields to a settings table, so that they’ll persist across the network for multiple users.
Your fine job on the demo files made figuring out the details a lot easier.
Frank Callanan
Camden, Maine
Hi Frank,
Glad to hear the technique is useful for you. With regards to the globals, I would assume that different users may have their own ideas about what should be exported… so… I would leave the global fields as is, but create a “user” table, and at shutdown, record the globals in the user table, then restore into the globals at startup. Let me know if I’m not making sense.
Regards,
Kevin
Kevin,
No problem. Saving the user’s values for reload at startup makes sense.
However, I have run into a baffling problem. A client user runs FMP 9 on Windows 7, with MS Office 2010. Now despite the fact that my version of this solution runs fine on my development system (FMPA 11, 10, 9; OSX Lion, xlsx) and on my Dell (FMPA 9, XP, xls), she gets a blank spreadsheet. I did add interface selectors for Excel version and file name.
So I wonder if anyone knows of an issue that might inhibit her.
I’ve checked her permissions, her ability to manually Save as Excel, to the desktop, to other locations… All OK. Today I’ll insert some error collection variables that will email me a list of errors, but for now, I’m stumped.
Yours,
Frak Callanan
Camden, Maine
Hi Frank,
Given that she can manually Save As Excel to her desktop, it’s hard to believe this could be a permissions issue.
The one thing I might suggest, from the realm of “FileMaker Voodoo”, would be to insert a short pause into the script… sometimes even a zero-second pause will fix weird problems like this.
So to be clear: when you are logged in with her account/password on your system, the Excel routine works fine? So there’s no way it could simply be a problem with her FileMaker privileges?
Regards,
Kevin
Thank you Kevin et al., for a really slick solution! It is well documented, presented, and being able to start at v1 of this technique and see how optimizations were made along the way is truly invaluable.
One modification I made for myself was to use a single, repeating global field with “enough” repetitions to pick my column selections, instead of a global field for every one. Then, if my users need an extra column, it’s only a matter of adding a repetition to the layout, instead of another field to the schema.
Thanks again for all the hard work!
Michael
Came across your export, great stuff and now using it for general export module across DB.
I’m using 5a, found issue when field contains quotes, so I have to escape it here :
# populate the appropriate $list_xx variable AND [repetition]
Let ( [
theCell = GetValue ( $theListList ; $counter ) & “[” & $theRepetition & “]” ;
theInput = GetValue ( $theInputList ; $counter ) ;
theValuePosition = ValuePosition ( List ( GetField ( $theSourceTO & “_ALI::input” ) ) ; theInput ) ;
theData = Substitute ( Evaluate ( GetNthRecord ( GetField ( $theSourceTO & “_ALI::output” ) ; theValuePosition ) ) ; “\””;”\\\”” ) //escape Quotes in field value
] ;
Evaluate ( “Let ( ” & theCell & ” = \”” & theData & “\” ; \”\” )” )
) // end let
Thanks for the the amazing script!