Virtual Lists are a flexible tool to generate a list of data that can be displayed in a portal or on a layout. Because of its flexible nature, you can use a virtual list to display different sets of data using the same table structure. I use this technique quite frequently when setting up pickers that require a searching or filtering function. The conventional method of generating the data set for the virtual list is by using the ExecuteSQL function. The ExecuteSQL function is excellent for this purpose because it is not context dependent. You can query any table within your solution without having to navigate between layouts and found sets. The unfortunate downside to ExecuteSQL is its speed. A simple query can be relatively fast; but, once you start adding LIKE or JOIN statements, the efficiency of the query decreases. In a recent project, I was tasked with creating a searchable picker. The picker was intended to allow searching for a person’s name (first or last) from a Contact table and then displaying the results in a portal using a virtual list. (If you are not already familiar with using a virtual list for a picker then I would highly recommend doing some research before hand.) The curveball came when I found out that my Contacts table would eventually contain over 600,000 records! Of course, I did not obtain this information until well after I had built the picker and used the ExecuteSQL function to build my virtual list. After importing the 600,000 records and attempting to use the search function the script fell flat on its face (even when using Perform Script on Server) and FileMaker would repeatedly hang. This would not be an acceptable user experience. So, I set out to find a different method to optimize the performance of my virtual list. Ultimately, I came up with an elegant method for optimizing virtual lists by dropping the ExecuteSQL function and switching to quick finds. Combining quick find with a new calculation and summary field I can generate a list of data that is correctly formatted for my virtual list.
Since I knew exactly which fields I would need to search on I set up a new layout and only displayed the searchable fields on it. I kept the theme and layout elements to a minimum to reduce the overhead when loading the layout and records. The other benefit to using a Quick Find is that you can get a much broader set of results. Rather than have to perform a find on the First Name field, and another find on the Last Name field, I can perform a single Quick Find and get results from both. This is useful when doing a multi-field search.
The Calculation Field:
This new calculation field needs to be set up to calculate how you would like your virtual list row data to be formatted. Generally, my row data is formatted in a manner like the following: ID & “|” & First Name & “|” & Last Name This is the same result formatting that I would use in my ExecuteSQL statement.
The Summary Field:
The summary field is used to generate our list of row data from the found set. Essentially it is just a List Of summary field pointed at the calculation field. The will give us a return-delimited list of our row data based on the current found set.
Since our new method works off a found set the quick find does have to be scripted. It is not as elegant of a solution as using ExecuteSQL due to having to navigate to a new layout then performing a quick find then navigating back to your picker. I typically approach this by opening a new window off-screen, performing the find, retrieving the result then closing the off screen window. This keeps these extra windows hidden from the user and makes the whole process feel seamless.
Performance on this large found set was a substantial improvement over the executesql method. The picker search went from unusable to very very quick! The performance increase is so good that I will likely use this method from now on in place of the ExecuteSQL method when making a picker for large data sets.
I have created a simple solution to demonstrate the method I described above. The demo file contains a large set of repeating contact data that you can compare both the quick find method and the ExecuteSQL method. You can download the demo file by entering your name and email on the left. Calvin Cooper is a FileMaker 15 Certified Developer at Skeleton Key. 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.