User-Friendly Excel Exports, part 4

27 Mar 2017: see User-Friendly Excel Exports, part 6 for an updated approach.

Colleagues and family members are starting to express concern at my obsessive refusal to bring this series to a close. But we’re getting close. Really. In fact, today, we’re going to look at two cool tricks (and one work around) which are utilized in this article’s demo file, excel exports, part 4, but that could prove useful in a variety of other situations. They are:

1. Dwindling Value Lists
2. Using a variable rather than a hard-coded file reference as an import source
3. Adapting the translation table technique to work with Anchor/Buoy (or any other multiple-table-occurrence-group design philosophy)

Dwindling Value Lists

I’m just going to touch briefly on these today because they are fully deserving of their own topic, but the technique is ideally suited to the task at hand. We want the user to be able to flexibly pick “fields” (actually pseudo-fields, as per earlier articles in this series) to be exported, assigning them to columns A, B, C, etc.

Once a field has been assigned to a particular column, we really don’t need or want to see it in the list of choices any more, do we? Ideally the value list should “dwindle” (shrink) as items are chosen, to make things clearer for the user. (You will note that in the above screen shot, the items in columns A through E, having already been assigned, do not appear in the pop-up menu.)

Well, FileMaker value lists don’t normally work that way, but it’s not too hard to force them to do so. Since we’re going to examine this topic in detail in an upcoming article, I will not comment further at this time, except to encourage you to dissect the demo if you are interested in this technique and don’t want to wait for (or have need of) my explanation.

Variable Source for Import

Next up, as many developers have learned the hard way, if a FileMaker solution relies on scripted importing of data from table A to table B within the same file, you can “break” the solution by renaming the file. E.g. the demo for part 3 in this series contains these four scripted “internal imports”…

…and if I simply duplicate that file, and rename it to “excel exports, part 4.fp7”, the four highlighted file references above will still point to “excel exports, part 3.fp7”.

What do to? Well if one owns FileMaker Pro Advanced (and one certainly should), one can always use Developer Utilities to rename the file…

…and the internal imports will be correctly repointed. But what if you don’t have a copy of FileMaker Pro Advanced handy? Or you do own it, but simply forget to use it, or feel like it’s too much trouble? Is there another trick you can pull out of your magic FileMaker hat?

There is. You can push the current file name into a variable…

…and then later in the same script, use that variable name, rather than a hard-coded file name:

There’s one thing to be aware of when you use this trick: if you ever decide to change the import mapping, you will need to temporarily swap in a hard-coded file reference. Otherwise instead of this…

…you will see this:

Thank you Nick Orr for pointing out to me how easy it is to swap out a $variable for a hard-coded file reference, and vice versa.

Translation Tables and Fragmented Graphs

Finally, a brief review of how our translation table (Aliases) is set up. The table contains an Input field for the “user-friendly” export column name, an Output field for the translation code, and check boxes to indicate whether the particular entry applies to Contacts, Donations or (as per the first two rows below) both.

If you compare the main portion of the Relationships Graph in part 3’s demo…

…with the corresponding section of the Relationships Graph in today’s demo…

…you can see that the former comprises a single TOG (table occurrence group), whereas the latter is fragmented into separate TOGs for Contacts and Donations. Without going into the pros and cons of one approach vs. the other, I’ll just point out that the graph fragmentation in today’s demo introduces some additional translation housekeeping work because CON can’t directly “see” DTN and vice-versa.

For example, these four Alias entries only apply to Contacts, so are evaluated from the perspective of the CON table occurence.

In part 3, the highlighted portions were simply “DTN” because CON and DTN were directly related within the same TOG. Here, though, CON can’t “see” DTN, because they are not in the same TOG… and the highlighted portion of each entry shows how the code was modified to make things work.

The other accommodation is more subtle because in some cases we want our output code to be able to evaluate from either CON or DTN. Here are some of the Alias entries where this is the case:

To ensure that Donation exports can correctly translate the output code, the ten calculated Excel export fields in that table…

…have been modified as per this example:

…in other words, translate “CON::” to “dtn_CON::” where appropriate to ensure the translation works properly.

Well, that’s about enough for today. Next time we’ll get rid of the calculated fields in Contacts and Donations, and instead let Bruce Robertson’s Virtual List technique do the heavy lifting… in fact, we’ll look at two different Virtual List implementations.