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)), ", ")

Find more posts tagged with

Formulas

Join

Accepted answers

All comments

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!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.