Evaluate function can speed up a FileMaker application
Replace calculation fields with regular text or number fields
The “Evaluate” function in FileMaker performs a calculation as if it were a calculation field. However, an Evaluate function does not use a calculation field, but regular text or number fields instead. These can be indexed, which leads to better performance.
Calculation fields slow down an application’s performance
Why use regular text or number fields instead of a calculation field? An important reason may be indexation.
Calculation fields are always updated and therefore cannot be indexed. Fields that are always being updated will slow down any FileMaker solution. This is especially noticeable with large amounts of data in list views, as well as with scripts that need to access layouts where calculation fields are located.
Normal text and number fields, on the other hand, are usually indexed automatically and can therefore deliver results quickly – nothing more needs to be calculated. It is useful to trigger calculations in normal text and number fields, but only when necessary. Mostly, data has to be calculated only once and maybe later in a correction. In the majority of cases, a continuous recalculation is not necessary and only puts an unnecessary burden on the system.
If you want to create performant solutions, calculation fields are often a bottleneck. That’s why it can be worth looking for alternatives.
Calculate the text
Texts can be assembled. A typical use case is for address blocks. For example, you can have an address table with the following fields:
<Company>
<Street>
<ZIP> <City>.
This is only a simplified example. Most addresses are significantly more complex. To avoid having to constantly reassemble this information, it may be useful to create a separate field for a <complete address>. In it, you can map the entire address. For letters, invoices and the like, it is only necessary to refer to the pre-calculated field of the <complete address>. Because the calculated field is a normal text field, this field can be indexed. This example is about the concept, not about this particular solution, which is only used as an example.
The result should be written in the neutral text field <Complete address>. There are several options to solve this:
- By script (set field value + calculation in script)
- By field definition (definition in the field itself)
- By field definition (definition in an external reference field)
Thus, the calculation takes place only 1x. However, it should be possible to repeat the calculation if necessary.
This is where the differences lie compared to a calculation field: With a regular text or number field, you have to trigger the calculation specifically, whereas with a calculation field, it is constantly updated. It follows, of course, that development with regular fields is more complex. The reward, however, is a better performing solution and controllable results.
On the left are several address fields. On the right there is the field <Complete address>, where the data has been summed up. The field on the right is not a calculation field, but a normal text field.
Calculation per script
An easy way to insert a calculated value into a regular field is by a script step:
Calculation in field
Another resource-saving method is to use a text field where you specify a calculation:
The calculation is reactivated each time the data fields are changed. After that, no more calculations are performed. The load on the system is minimal, and the result can be indexed.
3. calculation with “Evaluate” function
The automatic calculation during text input (example 2) works well. However, it does not always work. Especially with external references, the “automatic” recalculation can fail. This is logical because a calculation in a regular text field must always be specifically triggered.
The following scenario could apply: Instead of hiding a calculation formula in a field or in a script, I can store the formula openly in a text field. This is then the definition for a calculation, not the calculation itself. Wherever I want to see the result of the calculation, I must reference the calculation definition, then evaluate and write the result in the result field.
If definitions are passed as “text”, they are not automatically evaluated. This is where the Evaluate function comes in.
- In the field where I want to see a result (e.g. <complete address>), I create a reference to an external calculation formula located in a text field. This external formula should trigger the calculation in the field for me:
- Field definition <Complete address> has a “formula” at “input” wherein I specify the external field: [ external definition]
- Unfortunately, this calculation is not performed automatically. This can be corrected with the Calculate function: Evaluate ( [ External Definition ] )
- You can control the calculation with a trigger field. A trigger field tells when the calculation should be executed. A change in the trigger field will do this. This works as follows:
- Evaluate ( [ External definition ] ; [ Trigger field ] )
- Trigger fields activate the evaluation by changing their content.
Here is the calculation for two different address blocks in normal text fields. They are complex calculations. Note: These fields contain only the formula. Nothing is calculated here. However, the goal is to store the calculation centrally and evaluate it at another location.
Here is the actual field for the result of the calculation. Be aware that it is not a common calculation field. The calculation is performed “On data entry” (“Auto-enter”), which can be defined in the field settings. In those settings, the reference to the previously mentioned formula is triggered with “Evaluate ( FORMULA FIELD)”. However, because of the complexity, this is not enough. The calculation often did not work automatically. An extra trigger must fix this. That’s why it says “Evaluate ( FORMULAFIELD ; TRIGGERFIELD )”.
Multiple triggers for the Evaluate function
The evaluate function supports only a single trigger. This is a limitation. The basics are simple: if the content of the trigger field changes, then the evaluation is performed. For complex, nested calculations and for external fields, the calculation may stall. However, a trigger can specifically start re-calculation. This is the task of the trigger.
Only 1 trigger is available. Occasionally, however, there are several fields in a calculation, on which the Evaluate function does not work. These fields can be added to a new field (add for the purpose!) to create a single trigger. If something now changes in one of the added fields, then the sum in the separate trigger also changes. The separate field is therefore only created to cover multiple fields, which should act as a single trigger. It is an auxiliary field.
As a trigger field, I created a number field, where I “added up” the relevant switches for this calculation. The result is not relevant. However, it will be a reliable indicator to reflect any change in the added switch fields. The change now triggers the calculation of the <complete address>.
Areas of application
Solutions like these help optimize an application. The only goal here is to avoid calculation fields because they constantly require resources. Regular text fields, on the other hand, can be indexed. The techniques in this post show how to trigger a calculation on demand in a regular text field.
Example
The following short video shows the result of the last example. The address on the right side of the picture is formatted with different switches. The result is saved in a regular text field. The example shows how to use regular fields to perform calculations entirely without calculation fields on demand.