Bypass Circular Reference
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().
Help Article Resources
Check out the Formula Handbook template!