How to update a task state when all subtasks related to it have a certain state

Hi !
i have 2 databases for tasks that are related to each other one is called tasks and the other is subtasks. they both have the same state options ( open, in progress and done).
i Have been trying to create an automation where the first rule makes the state of the parent task be set to in progress if one or more subtasks are in progress state, and the second makes the task state done if all the subtasks are done.
does anyone have an idea of how this might be accomplished ?

I would suggest creating a couple of formula fields in the Task db:

Subtasks.Filter(State.Name = "In Progress").Count() > 0

Subtasks.Filter(State.Name = "Done").Count() = Subtasks.Count()

The first formula will be true if there are any subtasks in progress, the second will be true if all subtasks are done.

Then you can define a couple of automations.
One should trigger on the first field updating (and filter to the case where it is true) with an action to set the task state to In Progress.
The other should trigger on the second field updating (and filter to the case where it is true) with an action to set the task state to Done.

By the way, is there a reason why you are using two databases, when you could just use one database and a ‘self-relation’ so that each Task can link to multiple other Tasks (which would be the subtasks)?

Thank you for replying
your suggestion has worked, however the second formula is displaying true state when the task has no subtasks is there a way to fix it, because I’m new to formulas and can’t figure it out.
I’m using separate databases because in my opinion it’s easier to manage them that way.

(Subtasks.Filter(State.Name = "Done").Count() = Subtasks.Count()) AND Subtasks.Count() > 0

I guess I assumed that if a Task has no subtasks, then there are no subtasks which are not done.
This seemed to be logical behaviour to me, but you can use the above if you want the opposite.

Out of curiosity, what are the things that are easier with 2 databases (or what is harder with just one?)

Thank you for replying again.
I use 2 databases because i don’t want to have a lot of fields in the same database as it gets daunting to manage, because the tasks and subtasks require different fields such as: separate date fields for subtasks and tasks, a description for both tasks and subtasks, and so on.

Thanks for that. It’s always interesting to understand other people’s use cases :pray:

any time :+1: