Bypass Circular Reference

Nik Fuentes

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?

Genevieve P.

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

Cheers,

Genevieve

Andrée Starå

Hi @Nik Fuentes

I hope you're well and safe!

I'd be happy to take a quick look.

Can you maybe share the sheet(s)/copies of the sheet(s)? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@workbold.com)

I hope that helps!

Be safe and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Nik Fuentes

@Andrée Starå can't friend. It's a sensitive document, even stripped to the studs. This is about the most I can share.

Paul Newcome

Are you able to make copies and replace sensitive data with dummy data?

Nik Fuentes

@Paul Newcome Unfortunately not. I ran this by my supervisor and was not allowed to share this sheet.

Genevieve P.

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

Cheers,

Genevieve

Paul Newcome

Not even a screenshot of the basic structure and all data replaced with generic data?

You do have the circular reference going, but without seeing the structure with at least generic data in it it is hard to suggest a workaround other than @Genevieve P.'s suggestion.