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
-
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 -
@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)))
-
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 -
Marvelous, @Genevieve P. , thank you!!! This formula now addresses my use case. 😁
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 379 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 304 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!