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

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?

Having explored this problem in my own apps, I’ve pretty much concluded that you can’t use virtual columns in designs like this. I suspect you’ll have to use physical columns with app formulas or initial values, and actions. Consider propagating final gps back up the chain with recursive actions.


Did you try changing the formula for Inherited gps to IFS(ISNOTBLANK([under],[under].[gps input])) ?

I wonder if it would work for you if you reversed it so the bounded gps contains a ref to the parent as opposed to the way you have now. So, rather than inherited gps, you would have a real column called parent and a VC called contains_gps which could be an ENUMLIST of all child, and grandchild records for a parent. Then, final gps would be IF(ISNOTBLANK([parent]), ANY(SELECT(locations[key],IN([key],[parent].[contains_gps]))), [gps_input])

I assume you meant

IFS(ISNOTBLANK([under]) , [under].[final gps] )

Returns the same error.

I’m sorry, I’m not following you here at all. Can you describe your idea a bit differently?

So, if you have locations home, neighborhood, city for example, rather than nesting home under neighborhood, neighborhood under city, I am suggesting to create a parent Ref column that will store the highest parent (in the case of both the home and neighborhood, it would be the city) for any record.

Ahhh, I see!
I think that would work.