How to Build a Faster List in FileMaker

My team and I have spent our careers building functional and efficient FileMaker solutions. And during that time, it became more and more obvious that the user experience is just as important as the system’s core functionality. After all, if the user hates it, she won’t use it.

We’ve therefore dedicated time to learning what users want and creating best practices for the solutions we build. One area on which we’ve focused is how to build a faster list to keep users from bogging down during one step of their many processes. Here’s an example of a client project we recently managed:

A group of users at an advertising-agency client has a filtered list of records they need to work with regularly. This list displays several values from related records. It also carries some conditional formatting to provide color coding and other at-a-glance information the users rely on to speed their work.

The list contains several hundred records, up to around 2000 records during the busiest times. Users found the scrollability of the native FileMaker list too slow, even with optimizations such as redundant storage of related values and minimization of conditional formatting.

They were used to working with a shared Google spreadsheet, which was lightning fast and showed their desired information quickly. However, after moving into a shared database system, continuing to use that Google document meant they had to do all their data entry twice.

So we began experimenting with ways to use web viewer technology to provide a better list experience while keeping users in the company-wide FileMaker system they use for the rest of their work.

JSON to Web Viewer Using Carafe

We needed a fast-scrolling list, and we needed it to support functionality these FileMaker users had come to expect:

• Click a row to open that record in the detail screen
• Select many rows in order to trigger a batch operation
• Filter/search to temporarily reduce the set of records in the list for the current user

We chose to put a Web Viewer on our FileMaker layout, to integrate it into the main application navigation, and implement DataTables via Carafe to populate that Web Viewer with our list.

(Carafe is our free, open-source tool that simplifies working with JavaScript in FileMaker. Learn more about the tool here.)

DataTables is open-source HTML code that presents data in a tabular format. Carafe is a Soliant solution that makes managing Javascript within Web Viewers easier, with versioning. Carafe generates a script that you can paste into a FileMaker solution. The script accepts JSON data and builds the Web Viewer HTML content and loads the Web Viewer in a layout.

Build the JSON to Present The List

We construct a JSON object in a calculation field on each record, using JSONSetElement to compile the JSON text. If specific HTML or CSS needs to be applied to any data going into that JSON, we created custom functions to help us do that efficiently.

We also used a custom function to abstract the field names to use as column identifiers in our JSON, creating a function that would return us the field name only (without any table name).

CSS style classes are used to give us conditional formatting, as well as the display of simple images on the rows such as checkboxes that are checked or unchecked.

Compiling the overall JSON for the Web Viewer includes finding the records to display, sorting them, and then using FileMaker’s Summary as List to get all of the individual record JSON objects into a variable. Substitution of carriage returns for commas and wrapping the whole thing in brackets allows us to set the entire list of records as a JSON array into the Web Viewer JSON. This is much faster than looping through the records and using JSONSetElement for each record.

Make The List Interactive With Callback Scripts

When you click in a Web Viewer, such as to click a record to open it in a detail, the JavaScript in the web page presented calls a script in your FileMaker solution. To connect to these “callback scripts,” the JavaScript needs the filename of your FileMaker solution and the name of the script in question.

To abstract the names of callback scripts that need to be set in the JSON that will be fed to DataTables, the FileMaker script that compiles the Web Viewer JSON calls each callback script with a parameter. This causes the callback script to exit early and return its name, using FileMaker’s Get (ScriptName) function.

We keep the Web Viewer JSON in a $$variable, so that certain callback functions, like selecting a row, can do a simple substitution in the row’s JSON (provided by the callback script). Then we update the JSON with the modified record using JSONSetElement and the JSON ID (provided by the callback script). This is much faster than recompiling the entire Web Viewer JSON because we are using FileMaker’s Substitute function to adjust only the part of the code we need and then reloading the Web Viewer.

Million-Character PSOS Limit

If you pass the compilation of Web Viewer JSON to the server via PSOS, be aware that with a large dataset, you can easily exceed the million character limit on what the server can pass back. We needed an alternate method of getting the compiled JSON back from the server. We have a User table with a utility text field for this purpose. Server sets the JSON result in the User record; then, on the client-side, we can easily retrieve it. But we only do this when the result is too large to pass back to the calling script on the client, for the sake of simplicity and the slight performance toll of fetching the data indirectly from a User record.

Horizontal Scrolling

If your table is very wide and won’t fit onscreen without horizontal scrolling, you may encounter a frustrating limit. The Web Viewer won’t support horizontal scrolling without using the horizontal scrollbar provided on the Web Viewer itself – while users are accustomed to scrolling anywhere on a web page with their mouse or trackpad. To thwart this behavior, we set our table width to 150%. Now, it will never fit within the available width of the Web Viewer. This forces the Web Viewer to accommodate horizontal scrolling with mouse or trackpad.

Result

The users are thrilled with their speedy new list. Without leaving FileMaker, it automatically shows them the set of records they need, color codes itself the way they like it, and scrolls up and down (and sideways) just as fast as a web page. No more redundant data entry and no sacrifice of usability make for a much happier crew of FileMaker users!

Next Steps in FileMaker

We’ve worked with many organizations with FileMaker solutions that achieve their functional goals but have a handful of user experience issues. We have helped them improve their applications and can help you with yours if you need to make a few changes. Contact us to see how we can support your users today.

Thank you to Ross Thompson for all of his help with the technical aspects of this post!