How to Quickly Navigate to a Specific Record with FileMaker Go to Record Function
Recently I needed to have a FileMaker Pro 12 script navigate to a particular record within the found set after performing some action. There are several situations where this could occur, such as a scripted Extend Found Set or Constrain Found Set or perhaps when looping through the found set and either omitting, deleting, or duplicating certain records. My first thought was to simply capture the ID of the current record at the beginning of the script and then loop through the found set at the end of the script and exit the loop on the correct record. What I discovered was that this was unacceptably slow with larger record sets.
How to Find a Specific Record in the Found Set
So here’s a better method. (By the way, thanks to Jeremy Bante and Jesse Barnum who both reminded me that this technique is called a binary search.) The solution I came up with looks something like this:
If the records are sorted, unsort them
Navigate to the middle of the found set
If the target record is greater than the current record, navigate to the middle of the second half
If the target record is less than the current record, navigate to the middle of the first half
Continue this pattern, splitting the remaining records in half in each iteration until we arrive at the correct record
If the records were sorted, sort them
The script works by relying on two facts:
1. In an unsorted state, the internal Record ID values are always in order from lowest to highest.
This allows the script to be completely context free. It doesn’t reference any tables or fields, so you can call it from anywhere.
2. When records are already sorted, running the Sort Records script step with no sort order specified will restore the previous sort criteria automatically.
There’s a small caveat here when the records are semi-sorted; you cannot return to that state.
The FileMaker Go to Record Script
Theoretically, the script should never exceed Lg(Get(FoundCount))+1 iterations. If our found set has 100 records, that’s about 8 iterations. For a found set of 200,000 records, it’s only 19 iterations. That’s a LOT faster than a simple looping technique, which might average 50 iterations for 100 records or 100,000 iterations for 200,000 records. In most cases it runs in less than one second. There are a few scenarios where the technique doesn’t work perfectly.
For instance, if another user deletes one or more records from my found set while my script is running, the script could end up a few records off. To address this, there’s a second loop in the script that loops record-by-record in the direction of the target record until it either finds the record or skips over where the record should have been. If the record wasn’t found, the script navigates to the first record in the found set. Here’s a screenshot of what the script looks like. Click here to download a sample file containing the script. If you find a creative use for this, I’d love to hear about it at info@skeletonkey.com.
Greg Lane is VP of Application Development at Skeleton Key and a FileMaker Certified Developer.
About Skeleton Key
Skeleton Key helps turn complex, complicated, and outdated systems into true information platforms. Our team of consultants and developers do this by developing custom-fit software tools and reporting dashboards that help businesses find, use, and understand their data, freeing them to focus on and grow their core business. In addition to custom databases and applications, we also provide training and coaching for getting the most out of your existing systems and understanding your unruly data. Skeleton Key is an open-book management company and active player of the Great Game of Business.
