Introduction to the series.
Given a FileMaker production setup, such as a given server box and settings, concurrent user count and network connections, what can one do to improve the speed of the database? That is, how can one refactor it: redesign and reprogram to improve speed while keeping the functionality exactly as it is? There are many ways, but before getting into nitty-gritty topics, this post and the series it belongs to, will deal with low-hanging fruit. They give an overview of all the easy tips and tricks that I came across during the past ten years or so.
This post is fully dedicated to the most important tip.
1 Avoid calculation on the fly. Store if you can.
This is perhaps the most iterated one on the internet and still worthy of being mentioned first. Calculations are performed on the fly, and fortunately only performed when needed. However, within FileMaker they occur in so many places that it is hard to keep track of, and the number of locations is only increasing. Two locations warrant special attention because they are where calculation drag can have the biggest implications for speed.
1.A Unstored calculation fields
The most prominent location where calculations can be replaced by stored values is in field definitions. It is rather tempting to just define a calculation field, probably because except for the speed problems that it might cause, it just works in all scenarios. Using a stored version comes with downsides which have to do with how and when the calculation of the stored data is made and updated. There are two options to get a stored field to update with a calculation result: scripting and auto update.
- Use a simple stored value and set it through scripting.
The downside here is that if one wants to maintain the same guaranteed responsiveness as calculation fields, then all possible scenarios where the equivalent calculation field would be used and recalculated need to be covered by a script. Triggered scripts can do a lot here but will only trigger under specified conditions, but not in all situations. For example scripted or automatic setting of fields that are used in the calculation, importing of records and other scenarios will need to be captured through attentive programming. In large solutions this may be quite a challenge.
- The other option is auto-enter of a calculated value.
The ‘Do not replace existing value of field (if any)’ option should not be selected, so that replacing occurs irrespective of the field already having a value or not. Only fields used in the calculation can trigger an update. However, of those fields used in the calculation, only changes in stored fields in the same record and global fields in the same table can trigger an update. Changes in unstored calculation fields in the same record and changes in any field in a related record do not trigger an update. This is a mayor downside of the auto-enter option and easy to overlook (not least because it is not documented). It requires a workaround when needed. Besides scripting, which has the same downside as mentioned under option 1, I see no other solution.
1.B Calculations in layouts
Layouts can be full of objects and triggers that involve unstored calculations directly or indirectly. Typically, the following are the usual suspects of nibbling or gorging calculation time:
- Calculation fields used in the layout
See above
- Summary fields
By their nature summary fields can not be stored, but one can at least make sure that they are based on stored fields.
- Conditional formatting
Try to prevent them altogether, but if they have to be used, their calculations could perhaps be replaced by stored fields. Alternatively, if many controls share the same condition, one could calculate it once and store the result in a global variable ( through OnRecordLoad trigger ), and then reference that variable in the condition. This has similar pro’s and con’s as replacing unstored calculations with stored ones. In particular, all scenarios in which updating is needed, have to be covered by scripting. ( See 1A.1 above )
- Portal filtering
Portal filtering can take up a lot of calculation time, depending on the amount of related records. If portal filtering is unavoidable, then again, try to base the filters on stored fields as much as possible.
- Hide/show conditions
Here too, calculation time may be reduced through the use of global variables. Alternatively, if clusters of controls share the same show/hide condition, they could be grouped or put on a single slide with one show/hide calculation. Obviously, here is a trade off, because groups and slides cause extra overhead within FileMaker.
- Layout calculations ( since version 20 / 2023 )
Layout calculations were introduced in version 20, known as FileMaker 2023. Until then, one had to use merge fields, which would require a field. If many fields had to be added only to serve as merge fields, the layout calculations would reduce the field clutter. But they are still unstored calculations. Like with conditional formatting, the calculation result could be stored in a $$ variable and calculated only once and updated when needed.
1.C General limitations
Some limitations have been discussed above. But storing is problematic when time sensitive or other current status information is needed to perform a calculation. Depending on how fast such information changes, there simply is no option but to use unstored calculations.
The current date changes only once every 24 hours. If a group of users is guaranteed to reside within the same timezone and to not work around midnight, one could consider storing the current date in stored fields and run a timed script at midnight to update them.
For some unstored calculation fields, depending on the structure of the calculation for a field, it might be possible to unravel and split the calculation in (a) stored part(s) and (an) unstored part(s). Each of these would go into their own new field, and the original calculation field then references these new fields. The original calculation field would still be an unstored calculation because it references at least one unstored calculation field, but at least the ‘storable’ part(s) of its original calculation would be stored and the resulting calculation should be faster. Obviously, there is a trade-off because of the extra overhead generated by the new fields, and one pays the price of having extra fields, and more difficult to read calculations.
1.D Price
Storing data costs disk space. This is much less a problem now ( in 2025 ) then say, 30 years ago. However, when pushing the boundaries of an application or a given server, one might still run into disk space limitations.
1.E Source
This is a well known technique under FM developers and all database developers, and is mentioned in a on-line Claris resource titled Performance Optimization of FileMaker Database.
About this post
If I can, I will give references to the on-line resources that first pointed out the tip or trick. If a reference is missing, or if you know an earlier reference, please do let me know through a direct message or comment.
I wrote the post myself, without the help of an AI. The post’ featured images were made with the help of WordPress’ AI driven image generator plus a lot of trial and error with prompting it.
Rubber Boots Data 