An Upgrade: Record Looping to Recursive Function

The rate-limiting step of saving and restoring a found set using record looping is—you guessed it—the looping through each record! For whatever reasons, Filemaker performs exceptionally faster when running calculations than it does running certain script steps, especially when navigating from record to record. Thus to side-step the record-to-record navigation when saving a found set, you'd need to find a way to grab a list of all the records' primary keys in a calculation without navigating record-to-record. One approach is to use a recursive custom function that loops through all the records in the found set, calling itself over and over until it has compiled a list of all primary keys.

In our case, the keystone of the recursive custom function is the built-in Filemaker function GetNthRecord(). This function returns the contents of the field you specify of the record in the current found set that you specify. So if the second Invoice record in the current found set contains the value 5.77' in the field 'Total', GetNthRecord(Invoice::Total, 2) returns 5.77. You can see that if you know the total number of records in the found set, via the Filemaker function Get(FoundCount), then you can build a recursive custom function, one that would accept four parameters:

1. The name of the field you want to grab information from.

2. The current iteration (which, when you first call the function, will be 1).

3. The last iteration (this is the found set count).

4. The current list of primary keys (you will pass empty, "", as the initial value, since you have no list at the beginning).

You can even pass the field name dynamically via Filemaker 10's new function GetFieldName.

Restoring the found set is the same as before: use a multi-line key relationship, where the list of primary keys is stored in a global field.

It's All in the Details

Of course, this method, like the previous one, will return the user to the first record in the found set, instead of to the record she/he was viewing before the find. To return to the pre-find record, you would have to store the record's primary key in a variable and then use record looping after the found set has been restored to navigate to the right record. Thus you end up stuck in the same dilemma as before, as the found set of records to loop through could be quite substantial. Even if looping through records were lightning fast, you have one other problem to contend with: the restored found set may not have the same sort order as the original. Filemaker pretty much sucks when it comes to saving and restoring sort orders, so you don't really have any room to maneuver here. Fortunately there is one other method of saving and restoring found sets that does restore sort order.

The Best: Use Different TOs

Not only does using different table occurrences to save and restore found sets maximize speed, but it maximizes usability. Here's how it works:

1. Create a new TO tied to the same table as the TO of the found set you want to save. Name this TO someTableName_foundset.

2. Create a new layout based on the someTableName_foundset TO, and name it someLayoutName_foundset.

3. In your script, before the find, perform a Go To Related Record script step based on the find TO that navigates to the someLayoutName_foundset layout. Ensure only to show related records and match the current record only.

4. Return to the find layout by adding a Go To Layout step after the Go To Related Record step.

5. After the find, in whatever conditional section you have for trapping a found set of zero, perform a Go To Layout script step that navigates to the someLayoutName_foundset layout.

6. Once on the someLayoutName_foundset layout, perform a Go To Related Record step based on the someTableName_foundset TO that navigates back to the find layout.

Viola. Not only is the pre-find found set restored, but also the user is on the record she/he was on prior to the find and the sort order is maintained. This method really has only one user interface faux pas: the front tabs of all the tab controls reset to their default values when returning to the pre-find layout. That's another topic for another day.

What the heck just happened?

You're probably wondering why using two different table occurrences tied to the same table enables you to seamlessly save and restore found sets. The short answer is that Filemaker saves found sets based on TOs, not on tables. Each layout is tied to a TO, not to a table. Layouts are the primary user interface in Filemaker, and as such, are used as the foundation upon which to store interface information, such as that of found sets and front tabs.

The most unintuitive part of using two different TOs is the Go To Related Record steps, both when saving and when restoring. It's not entirely obvious why the step even works in the first place, since your two TOs are not tied to each other via a relationship. Somehow, behind the scenes, Filemaker assumes that each record is related to all others in the found set by virtue of being in the same table. We aren't exactly sure why this works, and although we'd like to know for curiosity's sake, we're happy enough that we have a very fast and reliable way to save and restore found sets.

It All Comes Down to User Experience

Restoring the found set for a user is nothing more than meeting the user's expectations. Who would ever want to be thrown out of context (i.e. in a found set of zero) after selecting a report to run? I sure wouldn't. I want to be in the exact same situation as I was before selecting that report. I don't want to waste 5 seconds bewildered at why the screen looks essentially blank with no record data. And expecting this behavior is even worse. We strive to build solutions that work the way the user does, not the other way around.

The ultimate solution to the save, restore found set problem would be one in which Filemaker builds a new script step, something akin to Go to Layout (Original Layout), like a Restore Original Found Set. Or even a duo package where you save a TO's found set and then restore that TO's found set, just like we're doing now. Regardless, Filemaker really should have a way to save and restore TO found sets on the script level, and it shouldn't have to require opening a new window.

Of the three methods of saving and restoring found sets in Filemaker, the one that outshines the others by far is using a different TO based on the same table as the TO you want to save the found set of. Looping through records is way too slow for any moderately-sized system, and using a recursive custom function—while fast—doesn't return the user to the record he/she was on, and the found set may not be in the original sort order. For the best user experience, maximum speed and quick implementation, using a different TO to save and restore a found set is the way to go.