If you work with the separation model, or any other multi-file configuration where a layout in file A is based on a table in file B, you may have encountered the following situation: while FileMaker makes it easy to transfer a found set from file A to file B…
…there is no obvious mechanism to go the other direction. Stated in other words, you can easily transfer a found set from an interface (a.k.a. UI) file to a data file, but not vice-versa. Nor for that matter, is there an obvious way to transfer a found set from one UI file to another UI file.
An example of when you might want to do this would be if you have a client who is somewhat FM-savvy and wants to design their own layouts for PDF, printing and/or reporting purposes. Rather than giving them layout access in the main system, you could instead give them their very own “Reporting” interface file, with TOs based on external tables. This way they can design layouts, write scripts, be creative on the (Reporting file’s) relationships graph, etc., without endangering schema in the main system.
But be aware that if you do this, the day will come when the client says, “Okay, I’ve painstakingly assembled a found set in the main system — now how do I transfer it to my reporting file?”
Well today we have three sets of demo files…
…each consisting of a data file (“Data”) and two interface files (“Interface” and “Reporting”). The first demo shows a bare bones method for transferring a found set from one interface file to another; demos 2 and 3 not only transfer the found set, but replicate the sort order as well.
Note that in this demo, as well as in demo 2, tables live in the data file only…
…the other two files are pure interface, and contain no tables.
However, the two interface files can access tables in Data by first adding it as an external data source…
…and then placing a TO (table occurrence) from that external source on the Relationships Graph. Reminder #1: external TO names are italicized on the graph.
Reminder #2: if you hover your mouse pointer over the little arrow to the left of the TO name, an informative drop-down will appear.
I asserted at the outset that you can easily transfer a found set from an interface file to a corresponding data file — let’s take a quick look at how this can be done.
Since the “contacts” TO in the interface file is based on an external table that actually lives in the data file, we can GTRR (“go to related records”) like so:
But what happens if we naively attempt to try this trick from the other direction, i.e., from the data file to the interface file? Let’s give it a try. First we’ll add an external data source from the data file to the interface files…
…but what are we going to place on the graph?
Nothing, that’s what. And of course if we don’t have any external TOs on the graph, then “Use external table’s layouts” is going to be greyed out.
So, how can we transfer a found set to a UI file? Let’s go back to the Interface file and take a look at one possible approach. In this case we want to transfer the found set from the Interface file to the Reporting file…
…and this will be facilitated by these two highlighted fields…
…as well as a relationship in the Reporting file from contacts to a second occurrence of the same table via zz_g_mlk.
The script in the interface file, pushes the contents of zz_s_list_id into the global field (the “mlk” stands for “multiline key”)…
…and then calls this subscript in the reporting file…
So, does it work? Indeed it does.
But notice that the record order has not carried over to the Reporting file. Yet.
Here, in addition to transferring the found set, the record order is mirrored as well, thanks to a technique adapted from Tom Fitch (Portal Sorting That Doesn’t Suck).
As we saw in Demo 1, we use a summary list field to populate a global multiline key. Well it turns out that the key values appear in the order of the original sort, so if we define this calculation in the contacts table in the data file…
…we can then sort on this field to replicate the original sort order. Note: You can read about ValuePosition here (ValuePosition: The Function FileMaker Forgot).
As often happens in the FileMaker world, there is more than one way to skin the proverbial cat. This method is more convoluted than its predecessor, but I decided to include it as an example of a very different approach.
Here the found set is transferred by importing into a shadow table, and the sorting… well, we’ll get to the sorting in a minute. The shadow table is a utility table defined in the Reporting file…
…and here’s the reporting file relationships graph:
When the user clicks the button in the interface file, this script is invoked in the Reporting file:
The UUID ends up in the tempKey field, serving as a “session key” to ensure that the technique is multiuser safe. The contact ID is imported into the Shadow table, and serialSorter is populated via auto-enter calc.
The found set is transferred from shadow to contacts via GTRR, and finally, we sort on a multiuser-safe related version of serialSorter.
The methods we’ve looked at today seem a bit convoluted, don’t they? If you have a better approach, I’d like to hear it.