When using a calculation field, you have the option of storing the result or having it unstored. The latter option means that the FileMaker engine will calculate the result “when needed”. This is usually when the field is displayed on screen or used in a printed report.
The default setting for a new calculation field is to store the result (the checkbox is unchecked).
This is an example where the FileMaker interface is confusing. Checking the box (with a tick) means “yes, do not do this”; unchecking the box means “no, do this”. It would make more sense if the option was “Store the calculation results”. Just read it carefully for now.
Sometimes there is no choice to store the result
If you do not have the option to store the calculation result, FileMaker Pro will tell you:
A calculation result cannot be stored where it references any of the following:
- related field
- summary field
- unstored calculation field
- global field
The case for not storing the result (unstored calculation)
An unstored calculation will be forced to evaluate every time it is needed. This means that if the field is displayed on a layout, the calculation expression will be evaluated.
Continual re-evaluation is important when you need a dynamic result such as that based on the current date. The majority of calculations using the Get(CurrentDate ) function should be unstored.
Another advantage of not storing the result is that no disk space is required. With large numbers of records, this can result in significant file size reductions. For calculations that simply concatenate data into a useful format (such as full name or full address), storing these results will simply double the storage space required for the data.
When displaying related records, such as in a portal, the FileMaker client will fetch the entire record except for any of the following not being displayed:
- unstored calculations
- container fields
- summary fields
Stored calculation results are fetched even if they are not displayed. So it may result in better performance to leave the results unstored.
The case for storing the result
Sometimes it is a good idea to store the calculation result.
An example is the line totals on an invoice – calculated by multiplying the quantity by the unit price. Once the line has been created, the result is not expected to change. The layout displaying the portal of invoice lines will draw faster with a stored result for each line total.
Stored calculations can be indexed. An index is used for faster searches on a field and is required if a field is used at the many end of a relationship.
Calculation results can only be stored if the data they reference is also stored. If you want to store a result in field B that references calculation field A, then the field A result must also be stored.
Alternatives to calculation fields
If you need a calculated result, you can also consider the use of auto-entered calculations (field options) or a scripted process to set a field by calculation. Used correctly, these options can provide more control over when and if a calculation is evaluated.
An example is the grand total of an invoice – the total of all the line totals less any discounts plus any taxes. Once the invoice has been created, the result is not expected to change. If the invoice grand total is stored, this will mean that summary fields based on the invoice totals such as those used in monthly or quarterly sales reports, will work faster because they are referencing static values rather than those that need to be evaluated on the fly.
However, if a calculation field is used, the invoice grand total cannot be stored because it references related fields (line totals). If the field is a simple number field, a scripted process can be used to update and store the invoice total when required.
What about other places where calculations are used?
Calculations are used in many places in FileMaker solutions including conditional formatting and visibility, button and tab names, and for record level access in security. In these cases, any calculation will be evaluated when required – the result is never stored. It follows that the judicious use of these features should be carefully considered as it can add considerable overhead to the interface.