SUCCESSORS Testing

Debbie Sawyer

@Paul Newcome

As requested

https://community.smartsheet.com/discussion/75335/project-sheet-dependencies-report/

Brian_Richardson

@Paul Newcome this is fantastic, just ran across it and it is EXACTLY what I was looking for. Thank you for being such a champion on the Community. Hopefully I can find time to swing by and say hi at Engage 2023.

Paul Newcome

@Brian_Richardson Glad my thread could help although there were some other contributors on this one.

As for meeting up, I will be at the Champion/Overachiever get together Monday evening if you are unable to attend. Otherwise I will be spending the majority of my time at my Partner Booth which will be just diagonal from the Community Booth.

Brian_Richardson

See you Monday :-)

TracyL

Hi @Paul Newcome -

Thank you so much for all of this. I'm stuck however as I keep getting an unparseable error.

The formula I now have in the "ready to start column" is: =IF(AND(Predecessors@row <> "", Status@row <> "Complete", COUNTIFS([Next Tasks]:[Next Tasks], HAS(@cell, Row@row + ""), Status:Status, "Complete") = COUNTIFS([Next Tasks]:[Next Tasks], HAS(@cell, Row@row + ""))), 1)

I'm using the status column and "complete" to have it move on. It seems like it works fine for the latest predecessor... but if another down the chain is complete then the check mark disappears... so for example if I have 12>13>14 for linkages and I have 12 complete then 13 will check. But the minute I mark 13 complete the 13 check disappears and only 14 is checked.

I'm brand new to smartsheet so I'm completely lost.

Thanks!

Jason.Darr

@Paul Newcome - This thread and its comments are outstanding, thank you! Like @Vivien Chong, I am using this principle to identify my Next Assignee ("Assigned to" field). Since we all appear to be blocked when it comes to referencing data where more than one Successor exists, 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 say "Multiple", I just don't know where to start. Here's my formula...

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

Jason.Darr

UPDATE: @Genevieve P. helped me solve this use case:

https://community.smartsheet.com/discussion/comment/399796#Comment_399796

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

Jake Gustafson

Jumping in well in advance after you guys have done a ton of work on this topic. Is what is laid out in this thread still the 'best' option with the current functionality or have there been any new developments that have made this more 'out of the box'? I've got a colleague requesting the automated notification to the 'next' task owner and am liking what @Jason.Darr has shared above to at least have an error for the multi-successor option that can be handled uniquely to make sure someone takes action. @Paul Newcome @Vivien Chong

See Paul Newcome's post from 10/21/21 for the following three bullets:

1. Add the "Auto" field // auto-numbering the rows
2. Add the "Row" field // =MATCH(Auto@row, Auto:Auto, 0) // gives me the row number matching with the sheet
3. Add the "Next Tasks" field // =JOIN(SUCCESSORS([Task Name]@row), CHAR(10))
   1. The other version from Vivien Chong wasn't displaying each as unique entries. Not sure if I am/was missing something with that

Then I added a couple more columns (the first of which I'm not sure is needed or how it's used) from Paul Newcome's 1/12/22 post:

1. Added the "Successor" field // =SUBSTITUTE([Next Tasks]@row, ", ", CHAR(10))
2. Added the "Successor Task Name" field // =JOIN(COLLECT([Task Name]:[Task Name], Row:Row, HAS(Predecessor@row, @cell)), CHAR(10))

Then I needed to populate the Assigned To for the Successor Task to create an automated workflow to notify them when their task is ready for them.

1. Added the "Successor Assigned To" field // =IFERROR(INDEX([Assigned To]:[Assigned To], MATCH([Successor Task Name]@row, [Task Name]:[Task Name], 0)), "Notify PM")

Running into issues with the multiple Predecessors, but the IFERROR function in the above formula to have a value of "Notify PM" in the field with a corresponding Automation Workflow to alert the PM who can manually alert the person/s on deck for the next tasks. This can be tailored to the needs of the project; the task owner could be notified as well to alert the next in line. I'll see what the person running this sheet desires.

This post is largely for me to lay out what I had to do to make this work for my internal stakeholder, but also to seek out any additional guidance here.

Lindsay AR

@Vivien Chong @Paul Newcome Not sure if either of you posted this, I did a quick glance, but if not, I used the following formula to FF, SS, etc.

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(Predecessors@row, ", ", CHAR(10)), "FF", CHAR(10)), "SS", CHAR(10)), "SF", CHAR(10))

Brian_Richardson

@Jake Gustafson contact data is going to be difficult to pull together. You can index/match or even formula derive a single contact name to populate the Successor Assigned To, but you cannot combine contacts for a multiple-contact result. So, if you have multiple successors/predecessors in play, there's no way using a formula to get a result that has multiple contacts back.

At least, not with core Smartsheet. You can use Data Mesh, Data Shuttle, or Bridge to piece that together, but you do have to have the full emails available for your contacts.

See my post here with the various methods:

https://community.smartsheet.com/discussion/123433/combine-emails-into-a-multiple-contact-column-multi-contact-column