Valid If - NOT - OR

[Domearian]

I am trying to check if some items are chosen from an EnumList and make the choices valid if.....

So invalid if it contains either of these 2 selections:

NOT(AND(CONTAINS([Gate Check Results], "*Central Padlock* Good Working Order"),CONTAINS([Gate Check Results], "*Central Padlock* Poor Working Order")))

OR invalid if it contained these 2 selections:

NOT(AND(CONTAINS([Gate Check Results], "*Drop-Bolt Padlock* Good Working Order"),CONTAINS([Gate Check Results], "*Drop-Bolt Padlock* Poor Working Order")))

I was hoping to add the following to bring them together as an OR. but that hasn't seemed to work:

OR((NOT(AND(CONTAINS([Gate Check Results], "*Central Padlock* Good Working Order"),CONTAINS([Gate Check Results], "*Central Padlock* Poor Working Order")))),(NOT(AND(CONTAINS([Gate Check Results], "*Drop-Bolt Padlock* Good Working Order"),CONTAINS([Gate Check Results], "*Drop-Bolt Padlock* Poor Working Order")))))

Where have I gone wrong because it doesn't work.

[Suvrutt_Gurjar]

Not tested but you may want to try

AND ((NOT(AND(CONTAINS([Gate Check Results], "*Central Padlock* Good Working Order"),CONTAINS([Gate Check Results], "*Central Padlock* Poor Working Order")))),(NOT(AND(CONTAINS([Gate Check Results], "*Drop-Bolt Padlock* Good Working Order"),CONTAINS([Gate Check Results], "*Drop-Bolt Padlock* Poor Working Order")))))

[Domearian]

Unfortunately that does not work. It doesn't give any invalid warning as you can see from the images.

[Suvrutt_Gurjar]

Okay, sorry. I am not at my desk for next two days and replied from mobile in transit.

Not able to test suggestions. 

You may want to try below

IFS(

AND(CONTAINS([Gate Check Results], "*Central Padlock* Good Working Order"),CONTAINS([Gate Check Results], "*Central Padlock* Poor Working Order")), FALSE,

AND(CONTAINS([Gate Check Results], "*Drop-Bolt Padlock* Good Working Order"),CONTAINS([Gate Check Results], "*Drop-Bolt Padlock* Poor Working Order")), FALSE, 

TRUE, TRUE

)

, 

[Domearian]

Suv, I appreciate your suggestions so any time you can answer I really appreciate.

Your last suggestion does not provide an invalid input either sadly. This is a difficult one!

[Domearian]

So this works fine for *Central Padlock* items:

NOT(AND(CONTAINS([Gate Check Results], "*Central Padlock* Good Working Order"),CONTAINS([Gate Check Results], "*Central Padlock* Poor Working Order")))

and I then also want to add for Drop-bolt:

NOT(AND(CONTAINS([Gate Check Results], "*Drop-Bolt Padlock* Good Working Order"),CONTAINS([Gate Check Results], "*Drop-Bolt Padlock* Poor Working Order")))

The OR simply isn't working.

[Suvrutt_Gurjar · Accepted solution]

Sorry for a late reply as I was in travel and not at my desk to test.

My testing shows that the first suggestion of using AND()in place of OR() works.

Please take a look at the GIF below. The valid if uses AND() in place of OR() for combining the two sub- expressions.

May I request you to retest with AND(). I used the following sub expression in valid_if to test

AND(
NOT(AND(CONTAINS([Enumlist_Test], "*Central Padlock* Good Working Order"),CONTAINS([Enumlist_Test], "*Central Padlock* Poor Working Order"))) ,

NOT(AND(CONTAINS([Enumlist_Test], "*Drop-Bolt Padlock* Good Working Order"),CONTAINS([Enumlist_Test], "*Drop-Bolt Padlock* Poor Working Order")))
)

My testing showed that even the second suggestion works in valid_if. I mean the suggestion to use IFS() based expression.

[Domearian]

Hi Suv, Apologies for the late reply, I have been away. Yes that works perfectly. I made the mistake in not checking the spaces between the text. It does work perfectly so thank you.

[Suvrutt_Gurjar]

You are welcome. Thank you for the update. Good to know that it works as intended.