Is it possible to use both AND and OR in the same formula?

Confused User

I am using Parent/Children

For the Parent, I want to know if all the Children say "Approved", then the Parent is "Approved", If any Child is blank or says something anything other than approved, then the Parent should be blank.

The issue is that I also want to say, if one of the Children says "Approved - Conditional" but all the other Children say "Approved", then I also want the Parent to say "Approved".

This is the formula I came up with and works for this one:

but the same formula does not work here because the result should be blank (I need the other Children to all say "Approved" or "Approved - Conditional" for the Parent to say "Approved":

James Keuning

Here is one way to do it, with helper columns. Some of the formulas can be collapsed, but the is the unrefined approach:

ID: Autonumber

Parent

=PARENT(ID@row)

Join Collect

=JOIN(COLLECT([FM Approval Status]:[FM Approval Status], Parent:Parent, ID@row), ";")

Count Approved Kids

=COUNT(COLLECT([FM Approval Status]:[FM Approval Status], Parent:Parent, ID@row))

Count Kids

=COUNT(COLLECT(Parent:Parent, Parent:Parent, ID@row))

Parent Approval

=IF(ISBLANK(Parent@row), IF([Count Kids]@row = [Count Approved Kids]@row, "approved"))

To add some explanation - your setup is not going to work very well because you are not using formulas. Set up the column this way and then make all formulas column formulas. Then hide the columns that you do not need to see.

James Keuning

Here is one way to do it, with helper columns. Some of the formulas can be collapsed, but the is the unrefined approach:

ID: Autonumber

Parent

=PARENT(ID@row)

Join Collect

=JOIN(COLLECT([FM Approval Status]:[FM Approval Status], Parent:Parent, ID@row), ";")

Count Approved Kids

=COUNT(COLLECT([FM Approval Status]:[FM Approval Status], Parent:Parent, ID@row))

Count Kids

=COUNT(COLLECT(Parent:Parent, Parent:Parent, ID@row))

Parent Approval

=IF(ISBLANK(Parent@row), IF([Count Kids]@row = [Count Approved Kids]@row, "approved"))

To add some explanation - your setup is not going to work very well because you are not using formulas. Set up the column this way and then make all formulas column formulas. Then hide the columns that you do not need to see.

Confused User

THANK YOU!!! I needed some direction and didnt think to use helper columns so this helped!