Say you’ve got two fields, both number fields, and you want to use them as flag fields, to be set to either 0 or 1. But they should always be opposite from each other. Changing one field should change the other and vice versa.

At first, that seems simple. You just need to make each field an auto-enter referencing the other field, right? Nope. That doesn’t work. FileMaker’s calculation engine does not like that sort of circular references. Field A changes so Field B changes so Field A changes, etc. Nothing terrible will happen, it just won’t work.

I played around with a couple ideas and the one that works is this:

Flag1 =

Let ( [ f1 = Flags::Flag1 Final ;
f2 = Flags::Flag2 Final ] ;
If ( $$f2.active ; not f2 & Let ( $$f2.active = “” ; “” ) ;
Let ( $$f1.active = True ; f1 ) ) )

Flag2 =

Let ( [ f1 = Flags::Flag1 Final ;
f2 = Flags::Flag2 Final ] ;
If ( $$f1.active ; not f1 & Let ( $$f1.active = “” ; “” ) ;
Let ( $$f2.active = True ; f2 ) ) )

You may wonder what the heck is going on here!

In short, two things. The field is checking to see if it’s the one that was edited, then editing itself based on that knowledge and then handling the $$variable that communicates that.

If [the other field was edited] then change this value to the opposite of that field and clean up the variable.

If [this field was edited] set the variable so the other field knows it’s being edited, and just set the field to itself.

Using Let() to define (and clean up) $$variables inside a calculation is not common, but is powerful and handy in certain situations.

Enjoy!

View the full article