By Daniel Wood, 31 March 2022
In this article we're going to talk about circular references - what they are and how they can happen in FileMaker solutions, specifically with two Auto-Enter Calculation fields. We'll cover what actually happens in this situation in regards to the order of Auto-Enter execution and why this does not work with a typical use case.
Often when a circular reference occurs, the developer is actually trying to achieve something that should in theory be possible, and so we'll talk about this situation and offer a solution that will provide some really useful outcomes.
In order to help make all this much clearer, let's jump right in and talk a bit about what a circular reference actually is...
As always we include an example file (packed with info and explanation). We recommend downloading and trying it out for yourself while you read.
In programming, a circular reference is defined as a situation in which two values reference each other. This situation has the potential to be rather problematic if two functions or values depend on another for definition.
In FileMaker, circular references can occur in the Auto-Enter calculation definition of fields. A scenario can arise where two calculations are dependent upon one another.
When this happens, FileMaker evaluates them in such a way that the resulting value for both fields is neither one that is desirable or usable.
Below is an example of two Auto-Enter calculation fields that reference one another. The outcome the developer is trying to achieve is that when the value of one field is 1 the other field should be 0 and vice versa.
As mentioned, the outcome of circular referencing in FileMaker is one you may not expect and this is due to the order in which FileMaker evaluates both Auto-Enter Calculations.
Taking our example from before, let's see what the order of execution looks like:
It's confusing for sure and is best illustrated with an example
Again we have 2 fields we wish to toggle the values in between 1 and 0.
Let's start by changing the value in field1 from blank to "1":
While upon initial inspection it may look like we got the intended result, let's now try setting field1 to 0. We would hope that field2 is set to 1:
Hmm, that's not quite what we want is it? In fact, once the first values are defined in these fields, they cannot be changed thereafter because the Auto-Enter firing on field1 prevents this by overwriting the new value the user just entered.
So that sucks for sure - but can we bend the laws of FileMaker into our favour to provide a beneficial outcome? Turns out we can!
First let's look at what as a developer we might actually want that outcome to look like:
The key here is ONLY. We want the fields to reference one another but to not result in a circular reference by way of only one of the Auto-Enter calculations being evaluated - dependent upon the field being actively modified.
Can we do this? Yes we can!
Going back to our earlier example, here is what the new behaviour will be.
Let's start by changing the value in field1 from blank to "1":
Now change the value in field2 from 0 to 1:
The solution is achieved through creating a Custom Function (to make life easier) called @AE_switch. Now, the code is obviously the most interesting part but let's establish what information is passed to the function first to help set the scene:
@AE_switch ( _f1 ; _f2 ; _self ; _expressionTrigger ; _expressionSelf )
The parameters are:
A couple of important things to note:
Another interesting thing is the usage of _expressionSelf. This is actually optional, but what this allows you to do is define an expression to evaluate upon the field being triggered by the user. This occurs before the Auto-Enter calculation on the other field is executed. Think of this is a way you can still pre-format a fields value, such as ensuring it is a positive number, or filtering out certain unwanted characters.
If you don't wish to use an _expressionSelf then just set this to Self.
Right so we're finally getting to the good stuff. We want to control the order of execution of Auto-Enter calculations and we do this through two special local variables. Firstly let's take a look at the function code (you can scroll the code sideways..)
Let ( [
~self = GetFieldName ( _self ) ;
~me = Case ( ~self = GetFieldName ( _f1 ) ; 1 ; ~self = GetFieldName ( _f2 ) ; 2 ; "" )
IsEmpty ( ~me ) ; _expressionTrigger ;
~me = 1 ; If ( $AE_switch2 ; _expressionTrigger & Let ( $AE_switch2 = "" ; "" ) ; Let ( $AE_switch1 = True ; _expressionSelf ) ) ;
~me = 2 ; If ( $AE_switch1 ; _expressionTrigger & Let ( $AE_switch1 = "" ; "" ) ; Let ( $AE_switch2 = True ; _expressionSelf ) ) ;
Note that this function is called in the Auto-Enter calculations of two fields.
We start by getting the field names of the two fields, and then compare them to the name of the field that this current function is defined in.
This tells us the context in which the current function is being evaluated (field1 or field2).
If for any reason the field is not one of the two defined to be in the circular reference (unlikely) then we just run the expression passed. Think of this as a fallback.
However almost certainly the field will be either that defined in _f1 or _f2.
We use two switch variables called $AE_switch1 and $AE_switch2. When a switch variable is enabled, it is telling both Auto-Enter calculations which field is currently actively being evaluated, and thus suppresses the other field from evaluating its expression at that particular time.
For example if $AE_switch1 is enabled, this means that the user modified _f1. In this case we only wish _f1 to evaluate its defined _expressionSelf.
When _f2 is triggered, it sees that $AE_switch1 is enabled, thus it knows the user modified _f1 first, and so it is to evaluate its defined _expressionTrigger.
Again, these types of things are best illustrated with an example...
Let's return to our toggle example, but this we'll define each fields Auto-Enter calculations as follows:
@AE_switch ( field1 ; field2 ; Self ; not field2 ; If ( Self < 0 ; 0 ; Min ( Self ; 1 ) ) )
and for field2:
@AE_switch ( field1 ; field2 ; Self ; not field1 ; If ( Self < 0 ; 0 ; Min ( Self ; 1 ) ) )
The only thing different in both definitions is the value of _expressionTrigger which is actually our initial calculation definitions.
You'll note an interesting expression for _expressionSelf. This is an expression to pre-format a number entered such that it set to 0 for values less than 0, and 1 for values greater than 1.
Putting it all together now, let's change the value of field1 to 5:
Finally how about setting the value of field2 to -1000?
Brilliant! We have now hijacked the normal circular reference execution in order to achieve our desired outcome!
This technique was originally borne out of the challenge of solving this circular reference issue. We have however utilised this in real world solutions for things other than toggles.
The most common scenario is one where events are being entered. An event generally has a start and finish date. We can calculate the duration of an event based on subtracting the start date from the finish date.
But what if the user wants to provide ability to specify either duration or finish date? Here we have a circular reference:
Both End Date and Duration reference each other. We however only wish for one to Auto-Enter when the other is changed by the user.
We can apply our function very easily to this scenario to allow a user to specify an event either by duration, or by a finish date.
Furthermore, we can use the _expressionSelf parameter to ensure both duration and finish date are safely entered with valid values before proceeding.
We hope you enjoyed this article and learned something about the weird nature of Auto-Enter calculations and circular references. Once you understand how a circular reference is executed, the solution becomes apparent that we need to control execution through some control variables otherwise we can never achieve a desired outcome.
For a while now I've been writing design based articles but I really enjoyed getting back into some more technical stuff. If you like this article and wish to see more technical oriented content in future please let me know
Please have a look at our example file if not already. This has everything you need to implement the @AE_switch custom function as well as a large amount of background info and explanation (which you can also find int his article).