Join ancestors EXCEPT the top level

Nat

Hi Smartsheeters,

I would like to group my milestone report by workstream, so I added a "join ancestors" column. I also have a "count ancestors" column that can be used.

My current report is a nightmare when sharing, as the top ancestor is the project name, and I can't control how massive (and not wrapped) the group header is.

As a result I currently have it ungrouped, with the "join ancestors" column out of sight on the dashboard so I can scroll over if I need to check what workstream the milestone belongs to.

How do I say "join ancestors except level 0"? or even better "Show only the level 1 ancestor"?

KPH

Hi Nat

I couldn't think of a way to get directly to what you want in just one column. There may be a way to use COLLECT based on count of ancestors, but if there is, it escapes me. However, you could create a helper column as a workaround.

Use an IF function to populate the helper column with a copy of the text from only the ancestors you want to include in your join, based on the "count ancestors" column.

Join the values in the helper column (rather than the original) using the JOIN ANCESTORS.

For example....

This is what I think you have (I have conditional formatting to make the row darker based on the count of ancestors, to make this more readable):

If you add a helper column and use a formula such as:

=IF([Count Ancestors]@row = 0, "", [Primary Column]@row)

the helper column will contain blanks for the rows where the count of ancestors is 0. And for all other rows, it will match the original column (in my case Primary Column).

If you use that column in your JOIN it will join a blank for the ones you don't want.

I also removed the first 2 characters as that was my colon space after the blank.

=IFERROR(RIGHT(JOIN(ANCESTORS(Helper@row), " : "), LEN(JOIN(ANCESTORS(Helper@row), " : ")) - 2), "")

Is that what you are looking for, and a reasonable workaround?