Suppressing Self-Referencing Auto Enter Fields

Using an auto-enter calculation on a field is a great way to populate its contents when other fields are modified, including itself. But what do you do if you want to use a calculation that references itself without having it trigger its own auto-enter calculation? The answer lies in thinking outside the calculation....

Setup aof an Example

In the attached example file I have concocted a scenario which illustrates this dilemma, although there are many situations where this may become an issue.

Here is a basic table of products. Each product has two rates. In another table I have a product ID field with a value list attached displaying products from the products table. The idea is that when a product is selected, the rate field is populated automatically with the correct rate from the products table. The rules are:

1. If product has a Rate 1 use that, otherwise
2. If product has a Rate 2 use that, otherwise
3. We wish to leave whatever is in the rate field at the time unchanged.

Here are some examples of what we expect when a product is chosen:

There is one more key rule for our Rate field and that is the rate must be able to be modified once a default is auto-entered

Building an auto-enter calculation for the rate field

With all of the required rules known, the first attempt at formulating an auto-enter calculation looked as follows:

Let ( [
TriggerField = Product ID ;
RateOne = Home to Products for Rate Auto Entry::Rate 1 ;
RateTwo = Home to Products for Rate Auto Entry::Rate 2
];
Case (
not IsEmpty ( RateOne ) ; RateOne ;
not IsEmpty ( RateTwo ) ; RateTwo ;
Self
)
)

Note that auto-enter calculations will trigger and re-evaluate when any field that they reference within the same table context is modified. We wish for the rate to change whenever the product ID is changed, however the product ID is merely a means to lookup the correct rate, we don't actually make use of the product ID itself in the calculation results. In order to have it trigger the auto-enter calculation we simply need to reference. This has been done using a Let statement where we set the Product ID field into a trigger variable whose only purpose is to fire the auto-enter calculation into action.

The rest of the calculation is straightforward and follows the rules defined. If there is a Rate 1 use it, otherwise if there is a Rate 2 use that, otherwise leave the field contents unchanged, as evidenced by using Self.

When this auto-enter calculation is used, everything appears fine.. Modification of Product ID fires the auto-enter, and the correct rate from the products table is entered.

But what about the final rule allowing modification of the rate?

This is where this auto-enter calculation fails. Recall that an auto-enter calculation triggers when any of its referenced fields are modified in the same table context. Well this even refers to its own self. If an auto-enter calculation references its own field, then any modification in the field itself will cause itself to auto-enter. Sometimes this can be a very useful behavior depending on your situation, but sometimes this can be a real pain in the arse!

If we were to attempt to modify the rate field now, then because our auto-enter calculation references Self then the auto-enter is re-evaluated. Given the current calculation, it is going to relookup the associated rate from the Products table, thus nullifying any modification we tried to make to the field - the auto-enter is essentially hijacked by our lookup from the products table, preventing any modification to the field by the user. The only time the user is free to modify the rate is if there is no associated default rate in the Products table. Again, this may be what you want, but sometimes it isn't.

So, how do we fix this?

Finally the answer, bit of an anti-climax perhaps after all of this build up :) If an auto-enter calculation triggers when any field it references in the given table is modified, including Self, then is there anyway to reference Self from somewhere other than the table context? The answer is of course yes, and can be achieved using a Self-Join relationship.

Here is a simple self-join relationship matching on primary key. Essentially a self-join is a 1-to-1 relationship with itself.

Now, we make a slight tweak to the auto-enter calculation:

Let ( [
TriggerField = Product ID ;
RateOne = Home to Products for Rate Auto Entry::Rate 1 ;
RateTwo = Home to Products for Rate Auto Entry::Rate 2
];
Case (
not IsEmpty ( RateOne ) ; RateOne ;
not IsEmpty ( RateTwo ) ; RateTwo ;
Home to Home for Self Join::Rate Modifiable
)
)

The change is made on the last line. Instead of directly referencing the field using Self, we instead now reference it via the self-join relationship. By referencing self outside of the calculations context, we are able to use the value of Self, without having it trigger its own auto-enter calculation which is exactly what we want. Now, the only field in the auto-enter calculation which will trigger the calculation is Product ID.

Back on the layout, if a product ID is chosen the rate will be auto-entered if there is one. Once done, the user is free to then modify the rate to whatever they wish, and because the auto-enter calculation no longer directly references Self from the calculation context, it can do so without having the value revert to the default rate.

Script Triggers

Yes, this could have been achieved with a script trigger on the Product ID field, but sometimes a script trigger may not be applicable, able to be used, or easy enough to work with. Because the auto-enter is tied directly to the field itself, then wherever the field is used the auto-enter simply works. With the script trigger it must be attached to every place the field is used in the solution.

I'm not saying a script trigger is not a good way of achieving this type of result, it certainly is and could be used. However I think auto-enters as a means to set a default value into a field, or enforce data entry rules on a field is still a great technique and very useful - especially in solutions Pre-FileMaker 10.

Final Thoughts:

Auto-Enter calculations are a great method for enforcing data-entry restrictions or rules on a field and for pre-populating a field with a default value. However because of their triggering properties there can arise situations in which you wish to reference the fields on contents but not have the field re-trigger itself when modified. A self-join can be used to reference the field in its own calculation - or indeed any field on the table - without having those fields force the re-triggering of the auto-enter calculation.

Example File

Please find attached an example file. This file was used for all the screenshots in this article, and is provided to help you fully understand what is going on in this article, and to let you experiment in FileMaker with this solution.

Download Example File