Include Parent Row text in JOIN formula

Options
Andy G90
Andy G90 ✭✭
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

  • Monique_Odom_Comcast
    Monique_Odom_Comcast ✭✭✭✭✭✭
    Options

    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

    Business Process Excellence Manager

    Smartsheet Leader & Community Champion

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

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

  • swilliams
    swilliams ✭✭✭
    Options

    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_Comcast
    Monique_Odom_Comcast ✭✭✭✭✭✭
    Options

    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

    Business Process Excellence 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!