Looking for a formula to check mark children, UNLESS that child has it's own (grandchildren)

Chris Shields

So I'm working on a formula to check checkboxes in a column if the primary column (named "Task Name") is a child UNLESS that child has it's own children.

Basically what I have a is a job tracker, and some jobs are simple. Some have are more complex broken into parts. For example the completion of job "Alpha" is a sum of 3 parts; "run 1", "run 2" and "run 3". So "Alpha" being the parent is not checked, but it's administrative so it has it's own tasks, then it has grandchildren tasks for "run 1", "run 2, etc.

Might look something like this, in the column "Task Name":

Job: Alpha

1. Quote
2. Procurement
3. Engineering
4. Scheduling
5. "Run 1"
   1. CNC
   2. assemble
   3. pack
6. "Run 2"
   1. CNC
   2. assemble
   3. pack

So tasks 1-4 would be checked, then a,b, c of each Run. But NOT the job name "Alpha", nor 5, 6, 7. I hope that makes sense.

My formula so far is:

=IF(COUNT(ANCESTORS()) > 0, 1, 0)

Which leaves the Parents (such as "Alpha") unchecked, but checks all of the "Runs"

I'm ultimately going to do is hide that column, but use it to run a daily report of job tasks that are running close to deadlines, flags delays, etc. (there's a lot of other information on my tracker that I wont bore you with). This allows me to address roadblocks, triage where necessary and share out with my team a status check of where we're at. Especially since we often times have different jobs running at once and it gets a little chaotic.

So if "Run 2" is due out soon, but "Run 1" is behind schedule. It'll flag that as a need to be addressed; why are we behind? Is it a material issue? Equipment? Etc.

I'm also open to suggestions to a better way to do this.

Thank you

Andrée Starå

Hi Chris,

Try something like this to get the Level and then you can use it to decide what should me selected or not.

=IF(COUNT(CHILDREN(Task@row)) > 0, COUNT(ANCESTORS()) + 1)

Would that work?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Andrée Starå

Hi Chris,

Try something like this to get the Level and then you can use it to decide what should me selected or not.

=IF(COUNT(CHILDREN(Task@row)) > 0, COUNT(ANCESTORS()) + 1)

Would that work?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Chris Shields

Thanks for the quick response Andrée. I plugged that in, and it works to check all of the parents (which works too, for my needs) but under "Run 1", "Run 2", "Run 3" it's returning #BOOLEAN EXPECTED.

Andrée Starå

@Chris Shields

Happy to help!

You'd use my formula to get the different levels in a so-called helper column and then you'd use that to check the boxes.

You're getting the error because the checkbox column is expecting a 0 or 1.

Make sense?

Would that work?

Chris Shields

Gotcha, gotcha.

K, I plugged it into a helper column and see what you mean. I think that I can work with this. Let me play with this a little bit.... THANKS!

Chris Shields

Andree! Thank you! This will work perfect.

To consolidate/simplify for others. I now have a column called "CheckedParent" and "CheckedParentHelper", which are both hidden. In the "CheckedParentHelper" I have Andree's formula (with "Task Name" being my primary column name):

=IF(COUNT(CHILDREN([Task Name]1)) > 0, COUNT(ANCESTORS()) + 1)

This yields a "1" for all Parents and "2" for all Children that have their own Children.

Then in my "CheckedParent" column (which is a checkbox column), I have the following formula:

=IF(COUNT(CheckedParentHelperColumn1) > 0, 1, 0)

For those who might be a little lost, "1" = checked, "0" = unchecked. And you can just double click on the checkbox cell to insert your formula. Then drag that little blue square down your entire column (new rows will inherit the formulas) for both "CheckedParent" and "CheckedParentHelper"

Now I can run a report that looks for all of the checked rows with check boxes and compares them against their anticipated finish dates that are 3 days out (or behind)

Andrée Starå

@Chris Shields

Excellent!

You're more than welcome!

Mehran Bagherian

Hi Chris,

this formula,

=IF(COUNT(CHILDREN(Task@row)) > 0, COUNT(ANCESTORS()) + 1)

provides the level, but leave the level blank for the last level. I mean it does not give the level for those task that has no children. How I get level for all?

Mehran Bagherian

Hi Chris,

I have used this easier formula, which gives the level

=COUNT(ANCESTORS()) + 1

Chris Shields

Right on