After many iterations, I landed on a solution combining ideas and tips from @Chr1sG, @RonMakesSystems, @Polina_Zenevich and @Kseniya_Piotuh. Apologies in advance for the length of this post: I wanted to include information on what didn’t work and why. Hopefully others find it useful.
Basic requirements:
- One database called “Tasks” and another called “Sub-Task”. “Task” has a one:many relationship with “Sub-Task”.
- If a Task has no Sub-Tasks, the Task’s State and Dates needs to be manually managed by a user.
- If a Task has one or more Sub-Tasks, the Task’s State and Dates are inherited from its Sub-Tasks.
It’s possible to build this app with just a single Task database. In my case, there are additional levels above Task and I wanted a clear, obvious app structure for other maintainers. My approach might be blunt, but I believe it’s easier for less advanced admins (like me and my colleagues) to understand and debug.
Sub-Tasks dbase setup:
- “Dates” field with Start & End Dates = on
- “State” field using Workflow (To Do, In Progress, Done) with Default = To Do; a Single Select field with a similar configuration (inc. Always require = on with Value = To Do) could also be used
Tasks dbase setup:
- “Dates” field with Start & End Dates = on
- “Sub-Task Dates” formula field:
DateRange([Sub-Tasks].Min(Dates.Start()), [Sub-Tasks].Max(Dates.End()))
- “Synch Task Dates w/ Sub-Task Dates” rule:
WHEN Task Updated → Sub-Task Dates WHERE Rule Filter - Sub-Tasks Count > 0
THEN Update → Dates → formula:
[Step 1 Task].[Sub-Task Dates]
With the Date synch sorted, I moved along to State synch.
- “State” field using Workflow (To Do, In Progress, Done) with Default = To Do; a Single Select field with a similar configuration (inc. Always require = on with Value = To Do) could also be used
- “Rule Trigger - State Effective” formula field:
If(
[Sub-Tasks].Count() = 0,
State.Name,
If(
[Sub-Tasks].Filter(State.Name != "To Do").Count() = 0,
"To Do",
If(
[Sub-Tasks].Filter(State.Name != "Done").Count() = 0,
"Done",
"In Progress"
)
)
)
- “Rule Filter - Sub-Tasks Count” formula field:
[Sub-Tasks].Count()
TL;DR. What comes next is the complicated part that ultimately failed. If you don’t care about what happened, skip down to “Here’s what I finally implemented”. That said, the method described next could be useful for other situations.
- “Synch Task State w/ Sub-Task State” rule:
WHEN Task Updated → Rule Trigger - State Effective WHERE Rule Filter - Sub-Tasks Count > 0
THEN Update → State → formula:
If(
[Step 1 Task].[Rule Trigger - State Effective] = "To Do",
[Step 1 Task].[Sub-Tasks]
.Filter(State.Name = "To Do")
.Sort([Creation Date])
.First().State,
If(
[Step 1 Task].[Rule Trigger - State Effective] = "In Progress",
[Step 1 Task].[Sub-Tasks]
.Filter(State.Name = "In Progress")
.Sort([Creation Date])
.First().State,
[Step 1 Task].[Sub-Tasks]
.Filter(State.Name = "Done")
.Sort([Creation Date])
.First().State
)
)
The nested IF statements above deliver the following logic:
- IF all a Task’s Sub-Tasks are To Do, THEN the Task is To Do
- ELSE IF all a Task’s Sub-Tasks are Done, THEN the Task is Done
- ELSE the Task is In Progress
For those unaware (like myself), under the hood State is a database rather than a text field. To manipulate a State value, you need to access actual State from an Entity which can be done by directly referencing a State option in a formula.
Since the parent Task’s State field is a Workflow field configured exactly like the child Sub-Task’s State field, the State options can gathered from a child Sub-Task. Some parts of the formula are easier to interpret than others. For example:
- [Step 1 Task].[Sub-Tasks].Filter(State.Name != “To Do”).Count() = 0:
– Filter the Task’s Sub-Tasks where State.Name does not equal To Do and Count them; IF the answser is 0, THEN…
- [Step 1 Task].[Sub-Tasks].Filter(State.Name = “To Do”).Sort([Creation Date]).First().State:
– Filter the Task’s Sub-Tasks where State.Name equals To Do, and…
– Sort the Sub-Task list by Creation Date (I just used the default ascending… the order doesn’t really matter as it’s really about what comes next) and…
– Finally grab the First State value which is then passed to the Task’s State field as its new value. You could also use Last().State.
Tricky, no? I never would have figured this out w/o the clues from @Chr1sG and @Polina_Zenevich. I may have missed it, but I scoured the User Guide pages on Formulas, Automations and the Workflow field, but didn’t find anything that would have led me to this method.
Unfortunately, it didn’t work. A problem arises when I need to pass a particular Workflow State to a Task when there isn’t a Sub-Task with the State. For example, I have a Task with one Sub-Task in To Do and one in Done. State Effective calculates that combination as In Progress, which I think is commonsense logic, but there isn’t a Sub-Task I can scrape to get the In Progress value to then pass to the Task, thus the Rule fails. Drat.
Here’s what I finally implemented for the Tasks dbase State automation:
-
Keep the original “State” field, “Rule Trigger - State Effective” formula field and “Rule Filter - Sub-Tasks Count” formula field.
-
Create “Synch Task State w/ Sub-Task State - To Do” rule:
WHEN Task Updated → Rule Trigger - State Effective WHERE Rule Filter - Sub-Tasks Count > 0 AND State Effective = To Do
THEN Update → State → To Do
- Create “Synch Task State w/ Sub-Task State - Done” rule:
WHEN Task Updated → Rule Trigger - State Effective WHERE Rule Filter - Sub-Tasks Count > 0 AND State Effective = Done
THEN Update → State → Done
- Create “Synch Task State w/ Sub-Task State - In Progress” rule:
WHEN Task Updated → Rule Trigger - State Effective WHERE Rule Filter - Sub-Tasks Count > 0 AND State Effective = In Progress
THEN Update → State → In Progress
This is three Rules instead of one, but simple enough to create and maintain. Bummed I couldn’t get the original scheme to work, but it is what it is.
Notes on this overall approach to Dates and States synch:
- If a Task has one or more Sub-Tasks, its State and Dates will be dictated by its children.
- You can set State and Dates on a Task with no children and that will work fine, but if you add Sub-Tasks they will take over.
- If you manually change State or Dates on a Task with children, the next time a child’s State or Dates are updated, the Task’s State or Dates will be overwritten by the child.
- I think “Rule Filter - Sub-Tasks Count” is probably not needed as the “Rule Trigger - State Effective” formula only works (thus only changes) if a Task has Sub-Tasks.
