Flagging Tasks as 'Blocked' if Predecessors are past due

Question

Hi everyone!

New to Smartsheet and trying to build some features for my company. I've been doing a lot of reading and tutorials, and I think I'm close to the end of this problem. Like the title says, if a task goes past its due date, I would all of its dependents tasks to flip to 'Blocked' status. I made it so far as to get the names of successor tasks, but I have no idea how to build the last piece.

The logic would be: If End Date is in the past, change the Status column (preferable) or add a Flag to the tasks listed in the Successor column.

In test sheet, Test1 is overdue, so Test2 and Test3 should be flagged as Blocked until Test1 is marked Complete.

Any help is greatly appreciated!

RyKlo · Answer

I am making some progress, but ran into two issues: multiple predecessors and lag time.

With successors I was able to break out multiple successors with a multi-select dropdown column, but using the same logic on predecessors does not seem to work. I'm guessing this is due to SUCCESSORS being a Function while on Predecessors is not.

Also if I add a lag time to a predecessor it seems to break.

Predecessor Expected: =Predecessors@row

PredMultiDrop: =JOIN([Predecessor Expected]@row, CHAR(10))

Blocked By: =JOIN(COLLECT([Task Name]:[Task Name], Row:Row, HAS(PredMultiDrop@row , @cell )), CHAR(10))

Paul Newcome · Answer

How do you currently populate that column?

RyKlo · Answer

Thank you! Any tips on how to isolate the Predecessor names? I thought I could copy the columns for Successors and change the logic to PREDECESSORS but I'm getting unparseable with