Formula fields: circular references

A formula field can read the fields of a linked record, even when that record belongs to the same table. It cannot, however, read its own field on that record: the formula editor rejects it with the message "Circular reference".

The case

A Groups table has a reference to itself, Parent: this is how a subgroup points to its group. The formula field Members selects the people of a country (Country filter), and a subgroup must take its own from among the members of its group:

let country := 'Country filter';
let candidates := if Parent then Parent.Members else select People end;
candidates[Country = country]

The formula editor rejects this code: "Circular reference: Members".

Why

Ninox reasons by field, not by record. To compile the formula of Members, it needs to know the type of Parent.Members, and therefore to compile the formula of Members… the very formula it is compiling. It does not matter that the parent record is never the record itself: the dependency is rejected at compile time.

The solution: a second field

Let Members compute its list without reading the parent group:

let country := 'Country filter';
select People where Country = country

Then hand the restriction over to a second formula field, Effective members, which reads Members on the record and on its parent:

if Parent then
    let parentMembers := Parent.Members;
    Members[contains(parentMembers, this)]
else
    Members
end

Effective members reads Members, but Members does not read Effective members: there is no cycle any more. Inside the brackets, this refers to the person being tested; this is why the parent's members are read beforehand, into a variable.