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:
- Dwindling Value Lists
- Using a variable rather than a hard-coded file reference as an import source
- Adapting the translation table technique to work with Anchor/Buoy (or any other multiple-table-occurrence-group design philosophy)
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.
Hi Kevin,
Are there limit of fields to be exported? I tried to implement to my solution about 20 fields but only first 10 fields are exported. and the next 10 fields showing ” Demo File for 18 Mar 2012 FileMaker Hacks Article: (www.filemakerhacks.com/?p=4065)” and some fields are duplicating my fields that I want to import but without headers.
Thanks.
Thong
Hi Thong,
The original demo cames with ten “zz_g_xl_nn” fields (where nn represents the digits 01 through 10) and ten corresponding calculated zz_c_xl_nn fields. I’m guessing there’s a problem with the way you implemented zz_g_xl_11 through 20, or more likely the zz_c_xl_nn equivalents.
Hope this helps,
Kevin
Hi Kevin,
Thank you for the reply, Can you please give me an advice how to do it, because what I did is I duplicated all the last field of zz_g_xl_nn and zz_c_xl_nn and rename it.
Is there a difference of duplicating and created the fields?
Thanks.
Thong
Hi Thong,
It’s not enough to simply duplicate and rename the fields, though that is a good start. Here are a few things you can try:
a ) Make sure to point zz_c_xl_11 at zz_g_xl_11, e.g., in the Let declaration, make sure
a = zz_g_xl_11
b ) Make sure zz_g_xl_11 is a global text field and that zz_c_xl_11 is an unstored calculation with a result type of text. (Since you created these two fields via duplication, this step should not be necessary, but I mention it because if these settings aren’t correct, the technique will not work.)
c ) Make sure you have a syntactically valid entry in the Aliases table. You can check validity by clicking the “Test Code for Evaluation Errors” button.
d ) For the Alias entry, make sure the correct check box or boxes are checked (“C” or “D” in my demo).
Best wishes,
Kevin