Is it possible to use both AND and OR in the same formula?
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":
Best Answer

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.
Answers

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.

THANK YOU!!! I needed some direction and didnt think to use helper columns so this helped!
Help Article Resources
Categories
Check out the Formula Handbook template!