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?

[Steve]

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.

[Bellave_Jayaram]

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

[Bellave_Jayaram]

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])

[Marc_Dillon]

Bellave_Jayaram:

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]), SELECT([parent].[key],IN([key],[parent].[contains_gps])), [gps_input])

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

[Bellave_Jayaram]

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.

[Marc_Dillon]

Ahhh, I see!
I think that would work.

Thanks

[Marc_Dillon]

Bellave_Jayaram:

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

I assume you meant

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

Returns the same error.