Formulas and Functions

Formulas and Functions

Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

Include Parent Row text in JOIN formula

✭✭
edited 11/29/23 in Formulas and Functions

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


Tags:

Answers

  • Community Champion

    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

    Architecture Solutions 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!

  • Community Champion

    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

    Architecture Solutions Manager

    Smartsheet Leader & Community Champion

    Pronouns: She/Her (What’s this?)

    “Take chances, make mistakes, get messy!” – Ms. Frizzle

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!

Trending in Formulas and Functions