Identifying When Subtasks are Ready for Execution Based on Status of Other Subtasks of Same Parent

Brandon Nichols

Hi - I've seen similar posts for related activities (mostly updating the parent's status), but need help finding a solution to my use case:

Background:

We have different test scenarios, each with a parent and associated child tasks. The child tasks of a given parent need to be executed in the order they fall. The number of child tasks for any parent can vary scenario to scenario. We currently do not have a great way to identify when a child task is ready for execution (it could be pending other subtasks being passed).

Goal:

We're looking to identify which previous child tasks for the parent have been marked as "Passed" to indicate when the next child task for the parent is ready to be reviewed. I anticipate we'll add a filter to the sheet to determine which scenarios are ready for the testers. This result can live in it's own helper column

Conditions:

If the child task is the first child under a parent task OR if all of the "Status" column results for previous children for the parent are marked as "Passed", OR if the parent task does not have any child tasks, we'd like to display "Not Pending Other Subtasks" to indicate that the task is ready for execution

If any of the previous child tasks statuses for a given parent reflect anything other than "passed", we'd like to display "Pending" to indicate that other steps need to be completed prior to being able to execute this particular step

Bonus points, but not necessary - can weed this out with filters: If all of the child tasks for a given parent are marked as "Passed", we'd like to display "Test Completed"

Dropdown Options for the "Status" column that is being Referenced:

• Not Started
• In Progress
• Passed
• Failed
• Blocked
• Deferred
• Out of Scope
• Ready for Retest
• N/A

I've attached a screenshot of what I anticipate this to look like - thank you in advance!

Nathan Panuco

Ideas to consider (not a complete solution) - Rather than trying to create a complex formula for everything you need, have you considered using automation rules to change the status field using some simpler helper formulas in the parent?

You can have the trigger be the [Modified] field and add conditions to branch off the different statuses it would change to. You'd need some helper fields in the Parent to identify how many total tasks and how many were completed and how many were not successful.

Once [Number of subtasks] is equal to [Number of completed subtasks] it would signal the Parent is ready and you could change the status with automation.

Hope this helps or gives you some ideas!