Parent status based on children

mccoy_FSI

This gets asked all the time and I've looked through posts but I can't get the formulas to work for my project.

I have a project with hierarchy 1,2 and 3. I need the parents' status to update based on the children. My status options are: Not Started, In Progress, Complete and Blocked.

If one child is in progress or complete the parent should show in progress, if one child is blocked the parent should show blocked, if all children are complete the parent should show complete, if all children are not started the parent should show not started.

Samuel Mueller

You need a nested if formula

=if(contains("Blocked", children()),"Blocked", if(contains("In Progress", children()),"In Progress", if(contains("Completed", children()),"Completed", "Not Started")))

JamesB

@mccoy_FSI

This would be the formula to use in the Parent Row of the Status column. This will not be able to be a column formula though. If you want to be able to use it as a column formula, I would ask do your child rows currently use a formula, and can you share that?

=IF(ISERROR(INDEX(COLLECT(CHILDREN(Status@row), CHILDREN(Status@row), "Blocked"), 1, 1)), IF(ISERROR(INDEX(COLLECT(CHILDREN(Status@row), CHILDREN(Status@row), "In Progress"), 1, 1)), IF(ISERROR(INDEX(COLLECT(CHILDREN(Status@row), CHILDREN(Status@row), "Not Started"), 1, 1)), IF(ISERROR(INDEX(COLLECT(CHILDREN(Status@row), CHILDREN(Status@row), "Complete"), 1, 1)), "", "Complete"), "Not Started"), "In Progress"), "Blocked")

mccoy_FSI

Right, this can't be a column formula. But this formula is giving me a similar result to others I've tried. If one of the children is marked complete, the parent reverts back to not started, the only way to have the parent show in progress is if the children show in progress, when they go to complete the parent goes back to not started. Everything else works, i.e. a blocked ancestor will change the parent to blocked, an in progress ancestor changes the parent to blocked, if all the children are complete the parent goes complete. So it's just stll the problem that if one ancestor shows complete the parent does not show in progress it shows not started. See the screenshot, the parent should show in progress if at least one child is complete but it does not.

Samuel Mueller

Which formula did you use? In my testing which I tested all the options, I did not have any issues. And also what is your structure here? If you have multiple hierarchies that would change the formula. Can you share the hierarchy levels