During agile development in FileMaker, sometimes you may notice an unwanted performance difference after adding a new feature, or display a new field to a layout. In this blog, I’d like to address what I have found to be the most likely culprits of the slow and unwanted Find or Sort dialogs on FileMaker layouts: summary fields and “unstored calculations”.
Inside the storage options of the calculation field definition is where developers decide if a calculation is stored or unstored. A checked box here (unstored), means exactly what it says: the data will not be stored and must recalculate every time the field is displayed. This is also how summary fields behave. If a summary field or unstored calculation is displayed on a layout, the data is calculated on record load – which, depending on the complexity of the calculations can cause a noticeable difference for the user when navigating through the records.
When talking about solutions, it is unfortunately not as simple as just unchecking that box to create a stored calculation. If your calculation references a related table or a summary field or unstored calculation, you will be prompted with this error: The calculation cannot be stored or indexed because it references a related field, a summary field, an unstored calculation field, or a field with global storage.
This error message makes sense based on the definition of a stored calculation. A stored calculation cannot simply reference an unstored calculation or a summary field. To improve the performance of a layout, reduce the amount of unstored data displayed on that layout. To do this we use a concept you may be familiar with, caching. Caching is defined as storing away or hiding for future use. A layout of stored data will load faster than a layout that must calculate its data.
In other words, the goal is to get the data from summary fields and unstored calculations into other field types so that your layout does not need to reference any summary fields or unstored calculations. If you have existing data, you will need a script to store the existing calculations data before we set up a method of always storing. Create a text/number/date/etc. field for each calculation, then a script that simply loops through the records setting the new fields with the data of the calculations.
Once that’s done, or if you are starting on a file with no data, you will need to set up the functionality to store the data in real time so that users can see and do modifications in real time. Display the new stored fields on the layout where the unstored data was, and don’t allow the fields to be entered by the user in browse mode.
Consider a two table file with Work Orders that track Services done. Hopefully this example translates to commonly used calculation data. Assume the Service table has a field called “Total”, which is simply “Rate” x “Hours”. The other table, Work Order would have a “GrandTotal” field that sums the related Service “Total”. With this set up, entering the invoice layout causes FileMaker to evaluate the results of ALL calculations and summary fields – which could really slow things down depending on the amount of records.
So, let’s set up some caching – the “Total” calculation in the Service table can be switched to a number field defined with an “Auto Entered Calculated value”. As long as you uncheck the option for “Do not replace existing value of field (if any)”, the data will recalculate whenever the “Rate” or “Hours” data changes. This is a critical step, but you likely will not notice the performance improvements you’re looking for until you make changes to the Work Order table, since that would be the layout/table where related data will be displayed.
Changing the “GrandTotal” field of the Work Order table is not as simple as the changes to the Service table. If you simply change the calculation to a number field and define it in auto enter you will notice it does not refresh the way Service “Total” did. This is because it is referencing a related table occurrence. All you need to do here is include an OnObject save script trigger that updates “GrandTotal” ANYWHERE the Service “Rate” or “Hours” can be modified. Also make sure that a similar trigger updating GrandTotal is incorporated anywhere Services can be deleted. Display the new stored fields on the layout and make sure you prevent modification to these fields- FileMaker will no longer automatically prevent them, since they are not calculation or summary fields. Also check to make sure the layout doesn’t include any value list referencing the “unstored” data fields.
Hopefully after making these types of changes you will improve performance of record navigation and reporting while maintaining accurate data and a normal user experience. Setting up your database to cache data that would otherwise be an unstored calculation or summary field can reduce the workload of a database, decreasing the odds of a user watching loading bars while using their FileMaker solution.
