Automating Risk Alert (Ancestor Row)

jordan_manor

Screenshot 2024-04-17 at 12.33.26 PM.png

Hi All - I'm trying to configure my "risk alert" column so that the if any of the tasks or subtasks are flagged as being at risk that the very first cell on the top (next to project name) is automatically updated. I've attached a screenshot to give a visual. The formula that I'm using for the risk cell for the ancestor row is: =IF(AND([End Date]1 < TODAY(), NOT(Status1 = "Complete")), 1, 0)

If anyone has any solutions I'd like to hear from you. Thank you for your help in advance.

James Keuning

Create a column called Top Parent and put this in it:

=IF(COUNT(ANCESTORS(Tasks@row)) = 0, Tasks@row, INDEX(ANCESTORS(Tasks@row), 1))

This will return your project name for every subtask

Then create a column called Count Risks and put this in it:

=COUNT(COLLECT([Risk Alert]:[Risk Alert], [Top Parent]:[Top Parent], Tasks@row, [Risk Alert]:[Risk Alert], true))

This will count risks among subtasks. From here you just do a formula where if Count Risks>0, then TRUE (change the field to checkbox of course)

But since your Risk Alert is doing double-duty, and it's showing you if a particular record is at risk , but also rolling up to a project-wide risk check, you need to tell the formula to do different things based on whether it's the top-line project name, or one of the subtasks, you do that like this:

=IF(Tasks@row = [Top Parent]@row, IF(COUNT(COLLECT([Risk Alert]:[Risk Alert], [Top Parent]:[Top Parent], Tasks@row, [Risk Alert]:[Risk Alert], true)) > 0, true), IF(AND([end date]@row < TODAY(), NOT(status@row = "Complete")), 1, 0))

That formula will check your Project risk box if any subtask is at risk, and it will check each subtask's risk box if the subtask is at risk.