Validation Rule for specific text values

Question

I have a requirement to create a validation rule on a text field where the values must be capitalized, and restricted to specific values. I realize that converting this field to a picklist is the best course of action, however it cannot be done for the time being, and this is a hacky way of accomplishing the requirement until that change can be made.

The first part of my formula works, which ensures that words must be capitalized, however the second part which is intended to restrict what value a user can enter is not working. I am able to enter any string or number value as long as the string is capitalized.

Here is what I have:

AND(
    Tax_1_Code__c <> UPPER(Tax_1_Code__c),
    OR(
        Tax_1_Code__c <> "HST",
        Tax_1_Code__c <> "QST",
        Tax_1_Code__c <> "PST",
        Tax_1_Code__c <> "RST",
        Tax_1_Code__c <> "GST",
        Tax_1_Code__c <> "FEDERAL",
        Tax_1_Code__c <> "STATE"
    )
)

Any help you can give is greatly appreciated.

Answer 1

When you're checking if a single field is not equal to any of a collection of possibilities, you need to use AND() instead of OR()

That's because if the value is, say, "STATE"
then it can't possibly be "HST"
so your OR() would always return true because your field can only possibly be one of those values

And actually, with your formula structured as it is now, it'll allow you to save any value (upper case or lower case) as long as it's not in your collection of 7 values. (The OR() returns false, and AND(false, <anything else>) results in false)

Since validation rules complain when the overall result of the formula is true, you need to swap the top-level AND() for an OR() (because not being all caps or not being one of the prescribed values are individually sufficient for you to complain).

OR(
    Tax_1_Code__c <> UPPER(Tax_1_Code__c),
    AND(
        Tax_1_Code__c <> "HST",
        Tax_1_Code__c <> "QST",
        Tax_1_Code__c <> "PST",
        Tax_1_Code__c <> "RST",
        Tax_1_Code__c <> "GST",
        Tax_1_Code__c <> "FEDERAL",
        Tax_1_Code__c <> "STATE"
    )
)