Include Parent Row text in JOIN formula
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)), ", ")
Answers
-
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)), ", "))
If my comment helped you, please help others by marking it as an accepted answer and consider helping me by clicking the 💡Insightful or ❤️Awesome buttons below!
Monique Odom-Stearn
Portfolio Operations Tools Manager
Smartsheet Leader & Community Champion
Pronouns: She/Her (What’s this?)
“Take chances, make mistakes, get messy!” – Ms. Frizzle
-
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!
-
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.
If my comment helped you, please help others by marking it as an accepted answer and consider helping me by clicking the 💡Insightful or ❤️Awesome buttons below!
Monique Odom-Stearn
Portfolio Operations Tools Manager
Smartsheet Leader & Community Champion
Pronouns: She/Her (What’s this?)
“Take chances, make mistakes, get messy!” – Ms. Frizzle
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 140 Industry Talk
- 472 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!