Parent/Children Flag Formula
Hi all,
I'm trying to create a formula that will flag a child row if a certain date is within a # of number of days, but I'd also like it to flag the parent row if any of the children row are flagged. I am currently using these two formulas: for the parent row  =IF(COUNTIF(CHILDREN(), =1) > 0, 1, 0) . . . and for the child row  =IF(AND([Credentialed End Date]@row <= TODAY(45), [Credentialed End Date]@row >= TODAY()), 1, 0)
I would really like to use one formula that can be made into a column formula so I don't have to remember to copy the formula to any added rows. Any thoughts?
Best Answer

Hi @ANTHONY MARELLI ,
Had to put the logic in the right order and convert the AND to an OR to get the right response. Try this:
=IF(Level@row = 1, IF(OR([Credentialed End Date]@row <= TODAY(), TODAY(45) >= [Credentialed End Date]@row), 1, IF(COUNTIF(CHILDREN(), =1) > 0, 1, 0)))
It also works as a column formula.
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
Answers

Hi @ANTHONY MARELLI ,
Try this formula in your flag column:
=IF(COUNT(Ancestors())=0, IF(COUNTIF(CHILDREN(), =1) > 0, 1, IF(AND([Credentialed End Date]@row <= TODAY(45), [Credentialed End Date]@row >= TODAY()), 1, 0)))
The formula uses the ancestors count to identify parent rows and then uses your parent flag formula. If the row isn't a parent it uses your child formula.
Work?
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

Hi @Mark Cronk ,
Thanks for replying. Unfortunately, I am receiving a "circular reference" error in the parent cell and "blocked" in the children when the formula is converted to a column formula (or if I extend it to more than one cell.

Sorry, I was trying to avoid a helper column and it failed. Try inserting a text/number helper column [Level] and insert the formula =COUNT(ANCESTORS()). You should get a 0 in Parent rows and a 1 in children; again, assumes 2 tier hierarchy.
Then in your checkbox column enter this formula:
=IF([Level]@row=0, IF(COUNTIF(CHILDREN(), =1) > 0, 1, IF(AND([Credentialed End Date]@row <= TODAY(45), [Credentialed End Date]@row >= TODAY()), 1, 0)))
Work this time?
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

I would suggest a helper column that identifies child vs parent rows then using a variation of @Mark Cronk's solution where the initial argument in the IF statement evaluates what is in the helper column to determine which of the two formulas to run.

Well, the errors are gone with the addition of the LEVEL column, but it isn't flagging correctly. There are rows with dates that are within 45 days that were flagged with my previous formulas and with the new formulas are not. See screenshot.

Hi @ANTHONY MARELLI ,
Had to put the logic in the right order and convert the AND to an OR to get the right response. Try this:
=IF(Level@row = 1, IF(OR([Credentialed End Date]@row <= TODAY(), TODAY(45) >= [Credentialed End Date]@row), 1, IF(COUNTIF(CHILDREN(), =1) > 0, 1, 0)))
It also works as a column formula.
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
Help Article Resources
Categories
Check out the Formula Handbook template!