27 Mar 2017: see User-Friendly Excel Exports, part 6 for an updated approach.
[Note: To avoid a huge amount of redundant repetition, this article assumes that you are familiar with part 1 and part 1.1 in this series.]
Today we’re going to take a look at streamlining the approach introduced in part 1 by reducing clutter on the Relationships Graph, going from this…
…to this, with no loss in functionality.
…and you can follow along in today’s demo file, excel exports, part 2, if you are so inclined.
As you may recall from part 1, the user makes choices from popup menus attached to global text fields (zz_g_xl_01, zz_g_xl_02, etc.)…
…and these choices correspond to Input entries in a table called Aliases.
There are ten calculated fields in Contacts, and in part 1 they were defined like so, each reaching across its own dedicated table occurrence on the Relationships Graph.
Those same fields exist in today’s demo, but with very different definitions than in part 1. Now they are defined like so:
…sharing a common definition, except for the first argument in the Let statement.
Here’s the definition of zz_xl_01:
Before there were ten hard-coded relationships between each global and the “input” field in the alias table, and each zz_xl calc used a different one. Now all ten of the zz_xl calcs use the same simple relationship and a very useful custom function called ValuePosition, which you can read about in detail here: http://filemakerhacks.com/?p=2082
In a nutshell, today’s calculation syntax says:
a) if the global is empty, show nothing
b) otherwise, make a return-separated list of all the alias “input” entries
c) figure out what position (i.e., line number) the global occupies in the list
d) grab the “output” code using GetNthRecord and the number derived by the previous step
e) interpret that output code using the Evaluate function
Note: I chose to use GetNthRecord, rather than GetValue in step “d” as insurance against any embedded returns in the alias output field. (An auto-enter calc ensures we won’t have any embedded returns in the input field, but it’s reasonable to expect that there might be embedded returns in complex output code.)
This “virtual relationship” technique can be utilitized in many different situations, and a mind-bending example of not only “virtual relationships”, but also “virtual fields” is Ray Cologon’s Layout Calculations demo.
Well, I think that’s about enough for today. In part 3, we’ll take a look at extending User-Friendly Excel Exports to accommodate multiple source tables.