Parent/Children Flag Formula

ANTHONY MARELLI

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?

Mark Cronk

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

Mark Cronk

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

ANTHONY MARELLI

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.

Mark Cronk

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

Paul Newcome

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.

ANTHONY MARELLI

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.

Mark Cronk

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