Finding Parent value of first blank child

Debbie Turner

Hi,

I've been struggling with this and haven't been able to figure it out. I want the Current Phase field on the request record to be filled in with the parent task name of the first child with a blank status. So in this case, on the top line, I want the Current Phase to say Approval Process. I have a field for level that is 0 for all the lines I want the current phase filled in on and the level of the value I want will always be on level 1. I need this to happen on each request (level 0) so we can track what part of the process each request is in.

I have tried a bunch of different things and read a bunch of posts, but I can't figure this one out. I would appreciate any help I can get.

Thanks,

Debbie

Mark Cronk

Hi Debbie, This is a great challenge. Thanks for posting.

I'd add a helper column [status help] to the left of your [I want software to heard cats] column. Enter the column formula:

=IF(level@row=1, IF(countifs(children([date completed]), isblank(@cell)) >0, "Incomplete", ""),"")

Then in your [current phase] column use the formula:

=vlookup("incomplete", [status help]:[I want software to heard cats], 2, true)

Work?

Mark

Debbie Turner

Hi Mark,

I get an unparseable error when I put the formula in for the status helper field. It doesn't seem to like the field on children.

If I just try =count(children()) it gives me the correct number of children. The minute I add the field as in =count(children([Date Completed]) it tells me the syntax isn't quite right. I have even tried copying a sample directly from the smartsheet children function page and I can't get it to work either. This is what was killing all my attempts at this as well. Maybe it is time to call support.

=COUNTIF(CHILDREN([Sold Date]1), MONTH(@cell) = 2)

I tried adding your code to the actual status column and moving it over to the left of the task field and it works to fill in the Incomplete. When I use the vlookup it seems to pull the second item ("Preliminary ITS Review") for each level 0 task. The first one should be pulling "Approval Process" and the second one should pull "Initiation".

Thanks,

Debbie

Debbie Turner

Hi Mark,

I got it to work. Thanks for the help. You got me going in the right direction. In my regular status column, on level 1 tasks, I put in the formula:

=IF(COUNTIFS(CHILDREN(), ISBLANK(@cell)) > 0, "Incomplete", "")

Then in my current phase level 0 tasks, I'm using the formula:

=INDEX(CHILDREN(Task@row), MATCH("Incomplete", CHILDREN(Status@row), 0))

Thanks again for the help.

Debbie

Mark Cronk

Hi Debbie, Glad you found a solution. We'll done. Thank you for contributing to the community.

Mark