New Bug Encountered: “Expression appears to be part of a cycle” error from dereferenced value

I have a table, Table, with key, [Key], and a ref column, [Parent]. [Parent] references rows in Table 1.

I have a virtual column in Table, [Ancestors]. It throws an “Expression appears to be part of a cycle” error when using this formula:

[Parent].[Ancestors]

Even if [Ref 1] =/= [Key 1].

I tried this and it got the same error:

IF(
  OR(
    [Parent]=[Key],
    ISBLANK([Parent])
  ), 
  LIST(), 
  [Parent].[Ancestors]
)

It seems like I should be able to access a value from the same virtual column, but a different row without a circular reference.

0 6 349
6 REPLIES 6

LeventK
Participant V

Can you please clarify:

  • What is the column type for [Ancestors] column?
  • Where do you use the given expression?

The [Ancestors] column is a virtual column and the expression is the formula for the column.

You’re trying to establish a de-ref within the same table (Parent) and you are trying to de-ref a de-ref. Isn’t the circular reference error so obvious? What’s your goal here? What that VC serve to?

Sorry, can you explain what you mean by this:

you are trying to de-ref a de-ref

The circular reference would be obvious if I was trying to self-reference the value of a given row and column, but I’m trying to get the value of the same column in a different row.

The goal of [Ancestors] is to get a list of [Parent] elements for a row that includes the row’s parent value, it’s parent’s parent, and so on. Like a breadcrumb trail of the elements nested above it.

In case someone comes across this same issue, I was able to work around it by adding a new non-virtual column in Google Sheets that uses a VLOOKUP to get the value of the same column from the referenced row.

Virtual columns do not contain “hard” data, they contain calculated dynamic data that is expected to always be changing and thus always be re-calculating. Your expression consists of referencing such a value, which references such a value, which references such a value…and on and on forever. Basically, there is no “starting point”. That is why it is circular.

I believe it would be a much different case if you had done similar with a Real column. Because the “first” record could have gotten an actual value from somewhere, which can then be referenced by the expression in future records.

Also, seems to me like the expression for [Ancestors] should have been:

LIST( [Parent] ) + [Parent].[Ancestors]

Top Labels in this Space