During my Developing with Perform Script on Server session at the 2014 FileMaker Developer’s Conference, I mentioned a method for restoring the client’s found set within a server-side script. This blog post discusses a general-purpose technique that works well in most situations. The information in this article should help you determine when it’s appropriate and how to objectively evaluate other techniques.

Gathering information about the found set

The first step is to gather information on the client that can be used to restore the found set on the server. Unfortunately, there isn’t a good way to capture the actual find requests used by the client and even if we could, we wouldn’t necessarily know if the user has created additional new records or omitted records from the found set. That means a method based on FileMaker’s internal Record ID is going to be the most accurate way to gather and send this information. If you’re always dealing with small record sets, you can simply loop through your found set and gather a list of primary key (PK) values. However, as the latency between the client and server grows or the size of the found set grows, this will become very slow. The first time you touch each record (by viewing it or looping through it in a script) the entire record is sent from the server to the client. Fortunately, FileMaker provides an easy way to gather a list of internal record ID values for the records in the found set. We can do this with snapshot links. A snapshot link file contains a list of record ID and record ID range values that are perfect for restoring a found set (after all, that’s what snapshot links do best!) All we have to do is create the snapshot link file, import it into a text field, and parse out the values. It’s actually pretty easy to do and it works reliably in FileMaker Pro on both Mac and Windows as well as in FileMaker Go on iOS devices. By the way, Jason Young at Seedcode wrote an excellent article last year about using snapshot links with ExecuteSQL. While he found somewhat disappointing results with ExecuteSQL, it turns out that the technique of parsing a snapshot link file to get a description of the found set to pass to a server-side script is far more efficient than any other method we’ve seen.

Restoring the found set

Now that we have our list of record ID values sent to our server-side script, how can we find those records? Our first thought was to use the ExecuteSQL function with the SQL ROWID function to get a list of primary key values that match the record ID values. Then you could use those primary key values to either do a find with a series of find requests or you could put the list of primary key values into a field and use the Go to Related Record script step to get your found set. We’ve tried several variations of these methods and all of them suffer from performance issues with larger found sets. In addition, using Go to Related Record often requires additional table occurences and extra script maintenance that we’d like to avoid. The method we’re recommending simply requires the developer to create a RecordID calculation field in each table. The calc field is equal to Get(RecordID) with a stored value. With this field, the server-side script can create a series of find requests for the record ID and record ID range values that are parsed from the snapshot link without needing to get primary key values. We’ve found this method to perform very well for up to 10,000 find requests, which could represent millions of records depending on the data. We’ve also found that it continues to work beyond 100,000 find requests, though the process of creating those find requests becomes increasingly expensive.

Measuring performance

To measure performance, we created a test file containing over 200,000 simple records. Then we tested with different found sets and measured the amount of time required to complete various parts of the client and server scripts. The WAN tests were performed remotely on a connection with approximately 80 ms of round-trip latency to the server. The LAN tests were performed with a local connection with less than 1 ms of latency to the server. Here are the total times in seconds for all of the tests. Note that for record counts from 1 to 100,000 we used every other record in the table so that we could avoid getting any ranges from the Snapshot Link file, which means that a find request will be created for each record in the found set. The 200,000 test shows what happens when the found set can be defined in the Snapshot Link file by a range of Record ID values. Following is a series of images that show actual results from specific tests. The images come from a dialog displayed at the end of the test. It describes the time required to complete various parts of the process as well as providing information about the server’s found count and current record so the tester can verify the results. The following image shows the result for a found count of one record. This is the fastest possible scenario. Note that almost all of the time is consumed by latency and overhead. Time spent creating and parsing the Snapshot Link file was just 28 milliseconds and the server-side script execution happened so fast that Get(CurrentTimeUTCMilliseconds) didn’t even change on the server before the script finished.   The following image shows the result for a found count with 1,000 records. The total time was 1.282 seconds. Most of that time was spent by the server-side script during creation of the find requests and then performing the find. The following image shows the result for a found count with 10,000 records. The total time was 13.969 seconds. Note the length of the parameter passed from the client to the server-side script is over 70,000 characters. The following image shows the result for a found count with 200,000 records. The total time was just 1.454 seconds. Note the size of the parameter passed to the server was just 111 characters because the snapshot link file could describe the found set using a simple range of record ID values.The following table and chart show a breakdown of the time spent during specific parts of the scripts for different record counts. The inner circle of the doughnut chart shows the breakdown for 100 records and the outer circle corresponds to the 10,000 record found count. As the found count increases, an increasingly larger percentage of the time is consumed by creating find requests. (Note: all times are in seconds and the chart shows the percentage of the total time for each stage of the test.) The “worst case” test in this file would be do a test with all of the odd records. This would ensure that there are no ranges in the found set and would result in the largest number of find requests. The method we’ve described works well with very small to very large found sets of records. Other than performance, the primary limitation we’ve found is that parameters are limited to one million characters with the Perform Script on Server script step. You could work around this by using a field to pass the parameter, but realistically, if your found set can’t be described in one million characters, you’ll probably want to consider other approaches.

Example file

We’ve created a simple example file that implements the method described above. It’s the same file we used for testing, so feel free to see how it performs in your own environment. You can download this file by entering your email address in the form below. Host the file with FileMaker Server 13 and then try it out by finding a set of records and clicking the Test button. You should see a dialog with information that has been returned by the server-side script. In the example file we created two special case optimizations. If the user’s found set contains no records, we don’t bother creating the snapshot link file, we simply pass a parameter so the server-side script can Show All Records and then Show Omitted, leaving the script with an empty found set. Likewise, if the user’s found set contains all records, we pass a parameter so the server-side script can simply Show All Records. This latter optimization may not be appropriate for certain situations because showing all records is not necessarily the same thing as having a found set that contains all records. When my FileMaker client has a found set, new records created by other users are not added to my current found set. However, if my client is showing all records, new records created by other users are added. The example file also includes a technique for quickly navigating to the client’s current record within the found set on the server. While this isn’t always necessary, it usually takes just a few milliseconds, even with 200,000 records. We previously described this navigation technique in more detail in this blog post. To implement the technique from the example file in your own solution simply copy the RecordID field from the TestData table to each of the tables in your solution and copy the “Save Found Set” and “Restore Found Set” scripts to your solution. Now you can call those scripts from your own scripts whenever you want to restore the client’s found set in a server-side script. We’ll do a more detailed walkthrough of the scripts and how to implement them in a future blog post. Download the demo file.

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.