Send alert to task's assignee when predecessor task's status changes to complete

quinn.farley85191

Hi all. New, enthusiastic user here hoping for some guidance. In setting up a repeating process, I have dependencies/predecessors clearly identified. Wonderful! Now, how does one set an alert/notification to auto-send to a task's assignee when the task's predecessor's status changes to "Complete"?

Paul Newcome

Let me start with this: Feel free to submit a product enhancement request as there is no direct way to do this. The link is here:

https://app.smartsheet.com/b/form/739aa75f30ca43a8a22eb53e4da7d409?_ga=2.199971950.1717656072.1557144026-2072984282.1556800642

 

The honest answer in regards to a workaround... The pain level of this is entirely dependent on the size of your sheet and the maximum number of predecessors any single row could have. There are a few different ways to set something up (none of which are "easy"), but the best way for you is going to depend on the details.

 

How many rows does your sheet have?

What is the highest amount of predecessors any one row can have?

Do you have some kind of Row ID established?

Can you provide a few screenshot of your sheet with sample data included?

quinn.farley85191

Screenshot now attached to my post.

I did add a column [hidden in screenshot] to number sequentially the tasks in order.

Subtracting Parent row task headers in dark blue, I've got 23 task steps.

I do not have any tasks with two predecessors.

Paul Newcome

Ok. Yours should actually be pretty straight forward then. I am assuming that the sequentially numbered column does NOT have any duplicates. Since it is hidden in your screenshot, I will refer to this column as simply "Helper".

 

What we need to do is look at the status for the row containing the number in the Helper column that is one less than the number in the Helper column on the row that the formula is on.

 

So we want to use an INDEX function to pull the actual word from the Status column.

 

=INDEX(Status:Status, 

 

Next we need to figure out the actual row number. For this we can use a MATCH function to pull the row number based on the number in our Helper column minus 1 and we want an exact match. Additionally we will not need to establish a column number for the INDEX function, as we are only looking at 1 column. Therefore we can go ahead and close the INDEX after we close the MATCH.

 

=INDEX(Status:Status, MATCH(Helper@row - 1, Helper:Helper, 0))

 

Ok. So now that we are able to see the status of the previous task, we need to build that into an IF statement so we can check the box when that INDEX/MATCH returns a result of "Complete".

 

=IF(INDEX(Status:Status, MATCH(Helper@row - 1, Helper:Helper, 0)) = "Complete", 1)

 

The formula above will only ever display one checked box at a time as we are basing it off of your sequential numbering.

 

To base it off of the Predecessors themselves so that if you have 5 tasks with the predecessor of row 7 then all 5 of those boxes would be checked at the completion of row 7, it would require a very minor change in the setup of your sheet and the formula.

 

The sheet change would be using the following formula in the Helper column (instead of a sequential order).

 

=Predecessors@row

 

The change to the formula would actually simplify it. Since the predecessor IS a row number, we can just use that in place of the MATCH function. The only catch is that right now the "number" in your Helper column is actual considered text, so we will simply have to use a VALUE function to convert it to a number.

 

=IF(INDEX(Status:Status, VALUE(Helper@row)) = "Complete", 1)

 

This will mean that if row 7 is marked as complete, ALL rows that have a predecessor of that task will be checked.

 

What will determine the best solution for you at this point will be how you prefer to send out the notifications.

 

Do you want to send it out for ALL tasks that have a predecessor of row 7 (second solution),

 

or

 

do you want to send one alert out at a time based off of the sequential completion regardless of predecessor (first solution)?

 

EDIT: Because of the simplicity of your setup, all formulas are written to be able to put them in the first row and dragfill on down.

 

NOTE: I have not tested any of this just yet. I will when I am able. If you are able to get to the testing before I am, please let me know how it goes.

Thia Davis

@Paul Newcome , do you know if there's been any new activity/conversation around this topic? Also, do you know if Smartsheet lists the product enhancements by requested votes? I want to know if the "Alert Assignee When Predecessor is Complete" request has gained any traction.

Thanks!

Paul Newcome

@Thia Davis Smartsheet does not make their roadmap public, but we have made some progress on alerting someone when the predecessor(s) is/are completed.

Take a look through this thread. You should be able to leverage the solution in the original post.

https://community.smartsheet.com/discussion/84257/successors-testing