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
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66K Get Help
- 429 Global Discussions
- 149 Industry Talk
- 488 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 152 Just for fun
- 74 Community Job Board
- 499 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!