How to collect multiple Successor values?

Here's a link to my initial post which helped get me this far.

RECAP: I use a combination of a Successor formula & Join/Collect in an attempt to report for my team the next tasks and assignee(s) in our waterfall workflow. I am able to successfully return the next assignee only when there is a single successor. I need to identify instances where a task has multiple successor assignees, this can be simple text field (doesn't need to be a contact field).

Here are the 3 formulas that nearly fulfill my use case:

=JOIN(COLLECT([Assigned To]:[Assigned To], Row:Row, HAS(Suc@row, @cell)), CHAR(15) + " + ")

=SUBSTITUTE(Successor@row, ", ", CHAR(10))

=JOIN(SUCCESSORS([Task Name]@row), ",")

Best Answer

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    Hey @Jason.Darr

    Yes, I believe you're correct - if there are multiple values in the cell then the formula won't be able to search for the rows individually to return the successors.

    We can add that information to your formula, try this:

    =IF(FIND(",", Successor@row) > 0, "Multiple", IF(Successor@row = "", "None", JOIN(COLLECT([Assigned To]:[Assigned To], Row:Row, HAS(Successor@row, @cell)), CHAR(10))))


    Cheers,

    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

Answers

  • Hey @Jason.Darr

    This looks related to our other discussion - did your comment here solve your question?

    Cheers,

    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • Jason.Darr
    Jason.Darr ✭✭✭
    edited 10/13/23

    @Genevieve P. This & @Paul Newcome 's advice in got me really close, but hasn't solved my team's biggest pain point, how to identify all of the "next assignee(s)" especially when there are multiple successors. This appears to be a Smartsheets limitation. My current formula successfully returns a value, but only when there is a single Successor.

    As a work around, is there a way to call out this risk via error text? I envision an amendment to my existing formula that identifies when a comma exists in the Successor field to add the text:"Multiple" and if it is blank "None", I just don't know where to start. Here's my current formula...

    Successor formula:

    =JOIN(SUCCESSORS([Task Name]@row), ", ")

    Next Assignee formula:

    =IF(Successor@row <> "", JOIN(COLLECT([Assigned To]:[Assigned To], Row:Row, HAS(Successor@row, @cell)), CHAR(10)))

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    Hey @Jason.Darr

    Yes, I believe you're correct - if there are multiple values in the cell then the formula won't be able to search for the rows individually to return the successors.

    We can add that information to your formula, try this:

    =IF(FIND(",", Successor@row) > 0, "Multiple", IF(Successor@row = "", "None", JOIN(COLLECT([Assigned To]:[Assigned To], Row:Row, HAS(Successor@row, @cell)), CHAR(10))))


    Cheers,

    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • Jason.Darr
    Jason.Darr ✭✭✭

    Marvelous, @Genevieve P. , thank you!!! This formula now addresses my use case. 😁

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!