Solving Circular References in Auto Enter Calculations

Introduction

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...

Example file time!

As always we include an example file (packed with info and explanation). We recommend downloading and trying it out for yourself while you read.

Click here to download the example file!

What is a Circular Reference?

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.

What happens in Auto-Enters?

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:

1. The value in field1 is modified by the user.
2. The Auto-Enter for field2 is triggered, which immediately triggers the Auto-Enter for field1.
3. The Auto-Enter for field1 is evaluated, and it's current value is overwritten with the result.
4. Now the Auto-Enter for field2 finally evaluates.

 It's confusing for sure and is best illustrated with an example

An example of the issue

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":

1. Value of field1 is set to "1" by the user.
2. Auto-Enter for field2 triggered, and immediately triggers the Auto-Enter in field1.
3. Auto-Enter for field1 evaluates (not "" = 1) and value is set to "1".
4. Auto-Enter for field2 evaluates (not 1) and value set to "0".

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:

1. value of field1 is set to "0" by the user.
2. Auto-Enter for field2 triggered, and immediately triggers the Auto-Enter in field1.
3. Auto-Enter for field1 evaluates (not 0 = 1) and value remains at "1".
4. Auto-Enter for field2 evaluates (not 1 = 0) and value remains at "0".

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.

What do we actually want?

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:

• If the value in field1 is modified by the user, then evaluate the Auto-Enter of field2 ONLY.
• If the value in field2 is modified by the user, then evaluate the Auto-Enter of field1 ONLY. 

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!

A proposed solution example

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":

1. Value of field1 set to "1" by user.
2. Auto-Enter for field2 triggered and evaluated (not 1 = 0) and value set to "0".

Now change the value in field2 from 0 to 1:

1. Value of field2 set to "1" by user.
2. Auto-Enter for field1 triggered and evaluated (not 1 = 0) and value set to "0".

The @AE_switch custom function

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:

• _f1:  A reference to the first field in the circular reference.
• _f2: A reference to the second field in the circular reference.
• _self: A reference to the current field this function is defined in.
•  _expressionTrigger: An expression to evaluate upon this field being triggered by the other field.
• _expressionSelf: An expression to evaluate upon this field being triggered by itself changing.

A couple of important things to note:

1. The references to _f1 and _f2 can just be direct references (not hard-coded). However it's important that in both Auto-Enter calculations where you define this function that the fields you define for _f1 and _f2 remain consistent.
2. _self is simply a reference to the function "self".

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.

How it works

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 ; "" )
] ;
  Case (
    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...

An Example

Let's return to our toggle example, but this we'll define each fields Auto-Enter calculations as follows:

for field1:

@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:

1. field1 changed to 5 by user.
2. Auto-Enter for field2 triggered, and immediately triggers the Auto-Enter in field1.
3. Auto-Enter of field1 evaluates:
1. Case ~me = 1 (Yes):
2. If $AE_switch2 on? (No):
3. Turn $AE_switch1 on, and evaluate _expressionSelf:  Min ( 5 ; 1 ) = 1.
4. Value of field1 is set to 1.
5. Auto-Enter of field2 evaluates:
1. Case ~me = 2 (Yes):
2. If $AE_switch1 on? (Yes):
3. Turn $AE_switch1 off, and evaluate _expressionTrigger: not 1 = 0.
6. Value of field2 is set to 0.

Finally how about setting the value of field2 to -1000?

1. field2 changed to -1000 by user.
2. Auto-Enter for field1 triggered, and immediately triggers the Auto-Enter in field2.
3. Auto-Enter of field2 evaluates:
1. Case ~me = 2 (Yes):
2. If $AE_switch1 on? (No):
3. Turn $AE_switch2 on, and evaluate _expressionSelf:  Self < 0, so result = 0.
4. Value of field2 is set to 0.
5. Auto-Enter of field1 evaluates:
1. Case ~me = 1 (Yes):
2. If $AE_switch2 on? (Yes):
3. Turn $AE_switch2 off, and evaluate _expressionTrigger: not 0 = 1.
6. Value of field1 is set to 1.

Brilliant! We have now hijacked the normal circular reference execution in order to achieve our desired outcome!

Other Uses

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:

1. End date is Auto-Entered based on Start Date + Duration
2. Duration is Auto-Entered based on Finish Date - Start Date.

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.

Conclusion

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 

Example File

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).

Click here to download the example file!