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:

1. By script (set field value + calculation in script)
2. By field definition (definition in the field itself)
3. 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.

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.

The KISS principle

In the early 1960s, a principle of good design was coined by the U.S. Navy. The idea behind the acronym “KISS” is to keep everything as simple as possible. KISS stands for

• Keep it simple, stupid
• Keep it simple & smart
• Keep it simple & sweet
• and so on.

The principle is to keep the design as simple as possible. Simplifying the design should offer several advantages. The principle can be applied to a wide variety of areas. In a software development environment, one could list possible benefits as follows:

• Improved clarity
• Easier to use
• Easier to maintain
• Uses fewer resources.

Simpler is better for these reasons. The KISS principle has proven to be a useful idea in many situations. The challenge, however, lies in simplification.

Simpler or more complete?

How can the KISS principle be applied? Are there also limits to simplification?

Often you can reduce a function to a single script step. If you want to achieve something and can do it through a single script step – instead of through many complex intermediate steps – you have understood something well. This would be analogous to the KISS principle. Simpler is better.

FileMaker is strong at these things because it often reduces complex issues to a configurable script step. However, while this simplification is purposeful, it is not always maximally clear. I would like to demonstrate this with an example.

Level 1: The minimalist

If you can solve something with just one script step, you show yourself to be a master. Simple is beautiful. However, there are limits to this. Calculations can be inserted at many script steps. In the script overview, these calculations are usually not visible or only partially visible.

Only another click and another window show which calculation is in the background. Here’s how it works in FileMaker. You may not even see that a calculation is stored. Then, while the function is very simple, the presentation in FileMaker also cloaks certain processes. This has advantages and disadvantages, but is not optimal for clarity.

Level 2: The experienced

A possible solution would be to replace the calculation with a variable and presuppose the variable itself to the function. One can proceed in such a way that calculations always take place in variables. The name of a variable can provide a hint about the calculation. It becomes clearer. This would give you two script steps:

• Set variable (with calculation)
• Use variable (in the actual function).

This procedure splits into two steps, which could also be done in one step. The advantage here is not in the “shortest possible implementation”, but rather in the clarity. If you open the script, you will see variable and application in two steps one after the other in the overview. One more step can make it clear that something is being calculated here and thus clarity can be served.

Level 3: The Wise Man

The considerations can go even further. It can be helpful to additionally enter a short comment. Many developers have found that a well-commented software solution is easier to maintain later. Accordingly, a comment is created with a view to later, easier maintenance. We now have three lines:

• Comment
• Set variable
• Use variable (in function)

Evaluation of the development

Which is better now, the shorter direct implementation or the more readable version with an explanation for later? With this example I would like to show that the KISS principle is good, but there may be reasons to implement a task in a little more detail.

What is the difference here? The difference is in managing complexity, but at two different levels.

1. The functionality
   If you can reduce a complex operation to a single correct script step, you have a good grasp of the capabilities of FileMaker (or any other development platform). That is the core issue at stake. Coping with the complexity could be achieved by a strong simplification.
2. The representation
   The two other lines that are now added (comment and variable) are no longer concerned with functionality, but merely improve the presentation. In the overview it is thereby clear at once what is meant (comment) and where one should check a calculation (variable).

There are as many opinions on the subject as there are FileMaker developers. My point here is not to decide what is important or how something “should” be done. This post is just a reflection. Today, I therefore see the three steps mentioned as development approaches:

1. Function (minimalism)
2. Variable (experience)
3. Comment (Wisdom)

Most importantly, the minimalist has good reason to do just that, and not write a line too much. Those who make calculations visible have learned from their experience that this makes scripts more readable. If you also add comments, you have understood that even after a year you probably don’t know why the script was written this way and not another way. There is something to be learned from each approach.

Simplification of FileMaker development

The KISS principle is just one of many ways to strive for simplification. Of course, there are other ways to simplify FileMaker development. In doing so, I find that there are different approaches. Which variant one prefers may depend on one’s own temperament or on special requirements of the current project. What are the variants?

1. More rules
   Some strive for systems in which all details of the development are defined. There are not only “best practices”, but a set of rules is established, which the developer has to follow. There is a definition for everything: how to name things (such as field names, tables, layouts), how to reference them, and what concepts apply to the entire application.
   Disadvantage of a set of rules: it can take on a life of its own and tie up a lot of resources and time.
   Worth considering: a set of rules should help the developer and the developer should not serve the set of rules.
2. Less rules
   Others, probably most developers, follow “best practices” and a reduced set of rules. You won’t be able to do completely without your own specifications, but the focus is not on the set of rules, but on a structure. The structure should help the developer to find his way now and in the future. Not “how” to do something, but “where” to find something is central.
   The disadvantage of a structure principle is precisely the freedom of the developer.
   Worth considering: Developers would do well to consciously establish a structure and use it consistently.

The simplification of the development always has something of an adventure. Which things stand the test of time will only be determined in retrospect. You need the courage to learn not only from your own experience, but also from other colleagues, to make mistakes, and to gradually incorporate proven approaches into your own work.

If you’re a learner, you’ll find that you change, you make better decisions over time, and in retrospect, you’ve made a lot of things a bit messy. It takes courage to take a gap when you realize that. Rarely, in fact, can the old errors be reprogrammed. Sometimes, however, it is essential. This process is exciting and there are methods to keep the pain in check. Simplification is one of these methods.

KISS: Keep it simple & smart.