How to collect multiple Successor values?

Jason.Darr

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

Genevieve P.

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

Genevieve P.

Hey @Jason.Darr

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

https://community.smartsheet.com/discussion/comment/399797#Comment_399797

Cheers,

Genevieve

Jason.Darr

@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.

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

Jason.Darr

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