Nested records, inheriting data from same table, error: "expression appears to be part of a cycle"

[Marc_Dillon]

Hi guys. Hoping someone will have a bright idea on how to get around this issue.

I’ve got this table setup:

[under] is a ref to the same table. Included a valid_if so can’t select itself:

FILTER( locations , [key] <> [_THISROW].[key] )

The full expression for [final gps] is:

IF( ISBLANK([under]) , [gps input] , [inherited gps] )

• Basically I’m wanting records to be nested under records of the same table, i.e. locations inside of other locations.
• Then I want to input a Lat/Long for the topmost location, into [gps input] and have that value get inherited down to all locations underneath it.
• And I want one single column to hold the “final” Lat/Long value for each record… [final gps]

So the setup above works fine for just one layer of nesting, one location inside of another. But if I try to add a third layer, nest a third location under the second location, it won’t work because that 2nd location does not have a [gps input] value to be inherited by [inherited gps].

So I try to change the expression for [inherited gps] to:

[under].[final gps]

And I get the error:

Expression appears to be part of a cycle

Which, yes, it would be part of a cycle, if [under] was pointing to itself (which it can’t because of the valid_if).

Not sure what to do here besides force a rule of only 1 nesting, or make another table for nested locations. Any ideas?