Self-referential Formulas

Self-referential Formulas are situations in which a formula field will refer to itself.

Basic Usage

Assume this scenario:

You are creating a form that captures Street Address and Postal Address information. There are 2 key fields: [Street Address] and [Postal Address].

There is 1 checkbox: [Street Address same as Postal Address].

If the situation where the street and postal address should be the same this is the formula that would apply on the Postal Address field.

self-ref-wrong

IF([Street Address same as Postal Address], [Street Address])

That’s quite simple to understand, however there is an issue, if the checkbox is NOT checked the formula will force the postal address to be NULL. An IF will always return a value, and if you don’t provide an alternative value (I.e. else) it will presume you intended this to be NULL.

The formula is treated as if it were written like this:

IF([Street Address same as Postal Address], [Street Address], NULL())

We would like the user to be able to input a value in this case. We can allow this by instead using a simple self-reference (Using the field’s own name ‘Postal Address’ in its formula).

IF([Street Address same as Postal Address], [Street Address], [Postal Address])

self-ref-gif

With this new formula, when the checkbox is NOT checked the field value will be set to the same value as it already was (I.e. unchanged).

It’s a relatively simple solution to the problem, but keep in mind that care must be taken when dealing with self-referential formulas. An poorly written formula could cause infinite recursion and in effect cause the form to not work at all.

Recursion

When a field is updated (either from human interaction or because of a formula in that field), it will in turn trigger an update of any dependent fields.

E.g.

B = A + 1

When A is modified, it will in turn update field B.

If we instead have B = A + 1 and A = B + 1 the fields will trigger a chain of updates A -> B -> A -> B. If we did not have a fail-safe in place, this would be an infinite operation. To prevent this, we limit this to 20 steps before throwing an error.

recursion

Playing with Fire

It is strongly advised that you do not do this.

This field will cause infinite recursion as there is no upper limit stopping the formula from recursion, in effect this will cause [# of Bananas] to be infinite.

IF([More Bananas], [# of Bananas] + 1)

This can be fixed by putting in an upper limit.

The formula:

IF([# of Bananas] < 20, [# of Bananas] + 1, [# of Bananas])

There is a lot of danger in using self-referential formulas as they have potential to cripple individual forms. In situations where they are required ensure that good logic is being used to reduce the effect of recursion.

The key is to make sure that the value remains unchanged in the calculation. In the example above, at [# of Bananas] = 20 it will remain 20 after evaluating the formula. This will cause the recursion to stop.

Related Articles