Alerts based off predecessor completion

Kimberly Aldred

Reaching out to see if you have any insight on something I am trying to do in Smartsheet

I have a cutover to production plan created and I want to automate the alerts when the predecessor task is complete, I want to an alert to go to the next task on the plan and alert the assigned to: that they can start their task. Is there a way to do this with Smartsheets without a heavy lift on setup?

Workflow Simplified Expectation:

• Task A is assigned to: John Smith
• Task B has a predecessor of Task A and is assigned to: Mary Johnson
• John Smith marks Task A as Status as Complete or Percentage 100%

Automation kicks off alert to Mary Johnson that they can start Task B

Debbie Sawyer

For this explanation, I'll assume Task A is on row 1 and Task B is on row 2

You need a helper column in the sheet set as a Checkbox data type called something like "Predecessor Complete".

On ROW 2 (Task B row) set up an IF function on the Predecessor Complete Checkbox cell saying =IF(Status1="Complete",1,0) (remember this formula is being entered on row 2 but referring to the value in row 1 as that is the row that Task A is on).

So when Task A is set to complete, the checkbox on Task B will enable (tick) and an Alert can be looking for a change in state on the checkbox and send a message to the assignee on Task B. (row 2).

I know this works, as I have used it many time in client solutions ;)

Hope this helps you out.

Kind regards

Debbie

Christina09

Hi @Debbie Sawyer

What if each row has a different dependency(predecessors)? Is there one formula for it instead of manually changing the referred row?

Let's say row 1 is complete, but row 5 and 7 depend on row 1. I have a dependency(predecessors) column with row #. Is there a formula that will auto refer back to the row that depends on and then uses the if formula; if complete, 1, 0.

Hope this makes sense.

Thank you!

Debbie Sawyer

Hi @Christina Lam Great question!

Sadly, I don't know of a way to set a row number for a cell reference using an if function! I like the logic of "set the row number of the status column to check, by the number defined in the predecessors column" but I don't think there is a way to do this.

I am sure another consultant will share their knowledge if I am wrong!

Good luck with your Smartsheet work :)

Kind regards

Debbie

Christina09

@Debbie Sawyer Thanks for getting back to me.

Have a great weekend!!

Tom Rodden

I believe I have developed a pretty solid solution to control the issuance of notifications based on predecessor completion. I have written it up in an article, attached here. Let me know if you find this useful. I can be reached at tom.rodden@seagate.com

Optimizing Task Notification Workflow in Smartsheet.docx

Debbie Sawyer

@Tom Rodden

Great article Tom.

I haven't revisited this post in a while, but since my last entry, I too have developed a way of reporting on the Predecessor column. It is a bit more involved than the article you have created though.

A client asked me to create a checkbox reportable column for "is the task on the critical path" so I needed to find a way of looking at the predecessor column and working it out. (I couldn't do a true critical path, but I could identify all tasks that had dependencies i.e., which tasks were being used as a predecessor and which tasks were the start and end points of the project)

Your article solution is great if, the project is using just 1 row number in the predecessor column. If there are multiple predecessors then you might run into issues. Also using an autonumber to count your rows, you are reliant on no-one deleting a row. As soon as a row is removed, the predecessor column will change the row number it is referring to (inline with the deletion) but your autonumber won't renumber, so your task number will be out of kilter with your predecessor value. The third part is when people are using other dependency types (i.e. SS, SF or FF) or if they are using Lag times. Then you will need to start extracting just the row number part of your predecessor text value.

There is a lot to solve!! :D

Feel free to reach out if you (or anyone else) would like some more guidance on this.

Kind regards

Debbie

debbie.sawyer@smarterbusinessprocesses.com

Tom Rodden

Thanks for your feedback, Debbie, and I agree with all of your points. I did try to address a "workaround" for the issue of multiple predecessors in the article I wrote, but it is only a workaround for that issue. And I am indeed painfully aware of the problem that row additions and deletions create when you introduce a Row or Tak ID column using autonumbering. That is solvable if you re-set the autonumbering each time you add or delete a row, but it is a pain. Another Smartsheet design weakness in my mind. But it can be handled with a bit of effort. (I have controlled access to my Smartsheet. Aside from me, no one can add or delete rows. Task owners update the plan only through automated workflows containing a link to their specific tasks and most columns are also locked.) And yes, the use of SS, SF and FF type dependencies further complicates the solution and I have no great answer for that in my article. As you said, more work to be done...

Debbie Sawyer

@Tom Rodden

Thanks for the comment, I really did think the article is good, and will be useful to others in the community.

To get around the Auto Number issues mentioned above try using the set up described below instead of using an Autonumber column.

Set up a column called One with the column formula of =1

Set up a second column called RowNum with the formula of =Sum(One$1:One@row).

This second formula won't be able to be a column formula due to having an absolute reference in it, but copy it down your column and new rows will be updated automatically.

This will give you a true row number which updates along with your predecessors when rows are added and removed.

I use this set of two helper columns a lot!! :D

Happy Smartsheeting!

Kind regards

Debbie