Include Parent Row text in JOIN formula

Andy G90

Hello, first time posting, long time smartsheet users.

I am trying to include the parent row text in my formula but not sure how. My Formula will only pull the text of the CHILDREN but not the parent. I need column "X" to be "BI, Engineering"

=JOIN(DISTINCT(DESCENDANTS([Data Group]@row)), ", ")

Monique Odom-Stearn

Hello @Andy G90,

This might not be the most elegant solution, but this might work for you:

=IF(CONTAINS([Data Group]@row, JOIN(DISTINCT(CHILDREN([Data Group]@row)), ", ")), JOIN(DISTINCT(CHILDREN([Data Group]@row)), ", "), [Data Group]@row + ", " + JOIN(DISTINCT(CHILDREN([Data Group]@row)), ", "))

swilliams

Hi Andy! The parent row is not a descendant of itself, so you would need to add a call out to [Data Group]@row within your JOIN(DISCTINCT()) formula, like so JOIN(DISTINCT([Data Group]@row, DESCENDANTS([Data Group]@row)), ", ").

If you want to convert to a column formula and have it populate column X only on the rows with descendants, then here is what I came up with.

=IF(COUNT(DESCENDANTS([Data Group]@row)) <> 0, JOIN(DISTINCT([Data Group]@row, DESCENDANTS([Data Group]@row)), ", "), "")

Although DISTINCT() expects a range, it seems that discrete range callouts separated by commas are accepted, not just a single continuous range separated by a colon. Likely, this is due to only one argument being specified in the DISTINCT function, as this type of comma-separated range call out does not work for some other functions expecting ranges.

Hope this helps!

Monique Odom-Stearn

Ooooh, good to know about the DISTINCT function! Thank you very much, @swilliams!

Although DISTINCT() expects a range, it seems that discrete range callouts separated by commas are accepted, not just a single continuous range separated by a colon. Likely, this is due to only one argument being specified in the DISTINCT function, as this type of comma-separated range call out does not work for some other functions expecting ranges.