Bypass Circular Reference

Nik Fuentes
Nik Fuentes ✭✭✭✭✭
edited 06/29/22 in Formulas and Functions

I've gotten myself into a bit of a pickle here and I see no way out,

I have three columns: Primary, Level, and ParentID. The Level is the number of ancestors that a row has. The ParentID is an identifier of which Level 1 row this row is descended from.

ParentID has a formula which is:

IF(Level@row = 0, "", IF(Level@row = 1, Primary@row, PARENT()))

Primary for Level 1 rows in a unique name input, but on Level 3 rows it has a formula that searches for all values that share the ParentID of the row. The intention of this is to restrict the column searches to just one block of related rows. It looks like this:

COLLECT([Data column]:[Data column], [ParentID]:[ParentID], =[ParentID]@row, etc)

The problem is that I get a circular reference error on this row because the formula for ParentID references Primary@row and the formula for Primary references ParentID@row. Classic circular reference, right? Wrong.

It isn't circular as the =Primary@row only applies to Level 1 rows, which do not have the collect formula in the primary column, it is exclusively plain text. the Collect() function that references ParentID is only ever on Level 3 rows. What's more, it isn't ever outputting a value from ParentID, it's just using it for an evaluation.

ParentID MUST be hidden and therefore a column formula. We have a lot of users using this document with only editor permissions who will need to create new parent blocks. Additionally, the Level 3 Primary row MUST be in the Primary column for printing reasons.

So here I am, stuck with a formula that is only circular in a situation that will never occur and that smartsheet isn't smart enough to see through. I've been at this for over an hour and I can't find a workaround that doesn't involve making the ParentID for Level 1 rows a manual entry in some way, shape, or form. Does anybody know of a bypass for this kind of error or a clever workaround?

I tried just making the =[ParentID]@row part just reference the cell in question directly, but there's no Grandparent() function and parent(parent()) throws a different kind of error. Doing that would require another manual entry in a complicated formula. Also, note that each Level 1 row has two or three Level 3 rows and there are potentially scores of Level 1 rows. Level 2 rows are doing their own thing entirely and cannot serve as a Parent().

Any thoughts?


Best Answer

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hi @Nik Fuentes

    I can't think of a way to have that formula, referencing the Level column in your Primary column, and also have a formula in the Level column that references your Primary column. Even though you're filtering rows in the formula, since you're referencing the whole column you'll get a circular reference error.

    I would set the COLLECT formula up in a secondary column. Then you'll need to copy/paste the data from that column into your Primary level 3 rows (instead of using a formula).




Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!