Get Most Updated Task Status From Child

Jong_Java

Hi,

Seeking what kind of formula to update parent row with the most updated child task status in my situations.

I have 5 status of the task in order; not started, waiting, in progress, completed, cancelled. If sub task 1 is completed, it will take sub task 2 status. if sub task 2 is completed, it will take sub task 3 status. But if sub task 3 status isn't complete, it will take its status in parent row.

Below is attachment that describe my situation. 

Notes, there's a discussions similar with my situation but still can't find the appropriate method to solve this. Appreciate for your kindly advise/answer.

Regards,

Paul Newcome

Try this...

 

=IF(OR(Status2 = "Not Started", Status2 = "Waiting", Status2 = "In Progress"), Status2, IF(AND(Status2 = "Complete", OR(Status3 = "Not Started", Status3 = "Waiting", Status3 = "In Progress")), Status3, IF(AND(Status3 = "Complete", OR(Status4 = "Not Started", Status4 = "Waiting", Status4 = "In Progress")), Status4)))

 

See below to see it in use. You would just have to follow the pattern to add more rows to the formula.

J. Craig Williams

Paul's solution does not scale. 

ASSUMPTION based on question phrasing: Sub Tasks are completed in order (this may not matter, but it is an assumption nonetheless). You aren't looking for the first instance of one of the statuses and then if not found the next. 

Try this:

=IFERROR(INDEX(CHILDREN(), MIN(IFERROR(MATCH("Cancelled", CHILDREN(), 0), 5001), IFERROR(MATCH("In Progress", CHILDREN(), 0), 5001), IFERROR(MATCH("Not Started", CHILDREN(), 0), 5001), IFERROR(MATCH("Waiting", CHILDREN(), 0), 5001))), "Complete")

Further notes:

5001 is returned for each unmatched type because that is beyond the row limit and won't be returned by a valid match.

If 5001 IS returned, then no valid pattern was found. This is either because:

a. All children are complete

b. Some children are mis-typed (data validation might be recommended here)

c. I don't search for blanks (but could based on recent changes to the MATCH return value)

d. 5001 is an invalid input to the INDEX() function, which throws an error. I catch the error and return "Complete" (see a and b). That may mask a different error, and to be honest, I'm not completely comfortable with this, but to fix it (I think) would require something like =IF(a value that has a lot of characters to determine it, return true result for the value that has to be recalculated using those same set of characters, return false result that may also take all those characters to determine what it is) *

* put another way, Smartsheet does not have a mean to store a temporary value except by adding another formula in a cell, which usually means adding a column to store it in. I'm not opposed to that, but avoid it if possible. Sometimes, there is an unused column on the parent row where that temp value can be stored, but finding ones that aren't shown for the casual user is sometimes difficult.

There might be a way to make this formula more compact but I have a solution so I am moving on for now.

Craig

Paul Newcome

Jong: While my solution will work, J's is DEFINITELY better.

 

J: Thanks for the improvement. I would have never thought to work it quite that way. Especially with the 5001 reference. I'll certainly be keeping this post in mind going forward...

J. Craig Williams

Paul,

Thanks.

If you don't mind, I'd like to say something else about your solution. 

=IF(OR(Status2 = "Not Started", Status2 = "Waiting", Status2 = "In Progress"), Status2, IF(AND(Status2 = "Complete", OR(Status3 = "Not Started", Status3 = "Waiting", Status3 = "In Progress")), Status3, IF(AND(Status3 = "Complete", OR(Status4 = "Not Started", Status4 = "Waiting", Status4 = "In Progress")), Status4)))

First, you missed the 5th status (Cancelled). That's minor since we won't be using it, but is worth getting out of the way.

More importantly, sometimes, you need to think backwards. The first level of the Nest IF is

IF(OR(any non-complete status for row 2),that status, keep going)

the next level (keep going) is

IF(AND(row 2 is complete),OR(any non-complete status for row 2)),that status, keep going)

and so forth.

If we flip our thinking, to this

IF(status2 = complete, keep going, that status)

then when we get to the next level we already know that the previous row IS complete, so the following ones become

IF(status3 = complete, keep going, that status)

While this code is smaller (and thus less of a CPU burden), it also has the added advantage that if the user decides to add 1 or more statuses, or change their names, the formula still works.

I hope this helps.

Craig

Jong_Java

Hi, Paul, Craig

You people are awesome. Itsn't even cross on my mind. Until now, I'm still trying to understand the logical of the formula. I'll adapt when I fully understand it, and I'll inform you guys the result.

A lot of thanks,

Regards,

J. Craig Williams

Here is a longer explanation:

http://ronin-global.com/2018/07/18/finding-next-task-in-smartsheet/

Craig

Paul Newcome

Craig: Makes total sense. I've only been using Smartsheet for about 3 months now, so I am definitely still learning, especially when it comes to "thinking outside of the box" with some of the more complex formulas.

 

Jong: Glad you were able to get a good solution.

h1pst3r

Awesome thread!

J. Craig, I also read your blog post on the Ronin Global site -- thanks for diving into more detail. I also posted this question there, but wanted to add it here in the hope that you can help and more people can benefit from your clever solutions; here's my situation:

I have a similar need but also share J. Craig's spidey-sense that the first non-completed child task becomes the status for the parent task.  

In my example we have the same parent task to one or more child tasks structure as described by the OP. In our case, the status options for the children are:

• Completed
• In Progress
• Not Started
• NA

Assuming there were multiple sub-tasks with varying statuses, how could you write a formula that made the parent status:

1. "Completed" if all children were set to "Completed"
2. "Not Started" if all children were set to "Not Started" 
3. "In Progress" if *any* of the children were set to "In Progress".
4. "In Progress" if, when more than child exists, one is set to "Completed" and one to "Not Started" but none are set to "In Progress"

Thanks!!

-Chris

 

Paul Newcome

Assuming there were multiple sub-tasks with varying statuses, how could you write a formula that made the parent status:

1. "Completed" if all children were set to "Completed"
2. "Not Started" if all children were set to "Not Started" 
3. "In Progress" if *any* of the children were set to "In Progress".
4. "In Progress" if, when more than child exists, one is set to "Completed" and one to "Not Started" but none are set to "In Progress"

.

This is actually a little more straight forward than the original post (thankfully haha)

.

=IF(COUNTIFS(CHILDREN(), "Completed") + COUNTIFS(CHILDREN(), "NA") = COUNT(CHILDREN()), "Completed", IF(COUNTIFS(CHILDREN(), "Not Started") + COUNTIFS(CHILDREN(), "NA") = COUNT(CHILDREN()), "Not Started", IF(COUNTIFS(CHILDREN(), "NA") = COUNT(CHILDREN()), "NA", "In Progress")))

 

What this says is that if ALL children are completed (other than NA), then "Completed". If ALL children are not started (other than NA), then "Not Started". If ALL children are NA, then "NA". The only other logical outcomes remaining would be "In Progress".

h1pst3r

Thanks, Paul!!

Let me test drive this!

-Chris

h1pst3r

Works a treat... except it appears that when all children are NA, then the parent = Completed.

J. Craig Williams

Thanks Chris.

I responded to your post on my website, referencing back to here.

It appears you and Paul nearly have the solution so I'll leave Paul to finish. You are in good hands.

Thanks for the kind words

Craig

Paul Newcome

Sorry about that. I put that portion in the wrong spot. Give this a whirl...

 

=IF(COUNTIFS(CHILDREN(), "NA") = COUNT(CHILDREN()), "NA", IF(COUNTIFS(CHILDREN(), "Completed") + COUNTIFS(CHILDREN(), "NA") = COUNT(CHILDREN()), "Completed", IF(COUNTIFS(CHILDREN(), "Not Started") + COUNTIFS(CHILDREN(), "NA") = COUNT(CHILDREN()), "Not Started", "In Progress")))

Paul Newcome

Craig,

 

Thanks! 

h1pst3r

Thanks guys, this is awesome!