Unlock ‘All or Nothing’ Validation Rules in Salesforce

This week someone asked me to help with an interesting Salesforce validation rule that, in plain terms, meant “Either enter these 3 fields or leave all 3 fields blank”.

That is a type of XOR operation: an Exclusive OR. A logical operation between 2 or more conditions in which either all operands are false or all operands are true.

If you enter one field in this group, you have to enter all. If you leave one field blank, you have to leave all blank in the same group.

I have found a similar question in Stack Overflow to which someone had given the following correct answer:

( ISPICKVAL( Rating, “Hot” )

&& ISBLANK( ShippingCountry ) )

|| ( NOT( ISPICKVAL( Rating, “Hot” ) )

&& NOT( ISBLANK( ShippingCountry ) ) )

/* parenthesis and identation added to make the order of operations clearer */

That formula means: “Shipping Country is mandatory when Rating is ‘Hot’. Shipping Country should be blank for any other Rating. Please either change the rating or clear the value in the Shipping Country field.”

That works. However, sometimes that same condition is mixed with others and we may run into the limit on the validation expression size.

A trick to make the expression shorter is like this:

ISPICKVAL( Rating, “Hot” ) = ISBLANK( ShippingCountry )

That is (spelled out):

• if Rating is Hot and Shipping Country is blank, then both conditions will be TRUE and equal with each other so the validation will activate and display the error

AND

• if Rating is NOT Hot and Shipping Country is NOT blank, then both conditions will be FALSE but equal with each other so the validation will activate and display the error

That type of shorter XOR expression might be handy when we have to specify a similar criteria for many fields, such as a validation that says “Shipping street, city, state and zip must be either all entered or all blank”.

In such case we can enter:

ISBLANK( ShippingStreet ) != ISBLANK( ShippingCity )

|| ISBLANK( ShippingCity ) != ISBLANK( ShippingState )

|| ISBLANK( ShippingState ) != ISBLANK( ShippingPostalCode )

Notice the transitivity between each of the conditions linking street to city all the way to postal code.

In the context of a Salesforce validation rule(*), the expression basically means both conditions spelled out below:

• if street is blank, then city should be blank and if city is blank, then state should be blank, and if state is blank, then postal code should be blank, otherwise display error

AND

• if street is NOT blank, then city should NOT be blank and if city is NOT blank, then state should NOT be blank, and if state is NOT blank, then postal code should NOT be blank, otherwise display error

If we didn’t use that shorthand, the expression would be much longer:

( ISBLANK( ShippingStreet )

&& ISBLANK( ShippingCity )

&& ISBLANK( ShippingState )

&& ISBLANK( ShippingPostalCode ) )

|| ( NOT( ISBLANK( ShippingStreet ) )

&& NOT( ISBLANK( ShippingCity ) )

&& NOT( ISBLANK( ShippingState ) )

&& NOT( ISBLANK( ShippingPostalCode ) ) )

Now imagine an instance where the conditions are not merely ISBLANK( someField ) but some other much more longer expressions and complicated – one can see how that shorthand might help.

In case further reduction of validation expression size is needed, there is another trick:

CONTAINS( “1|2|3” ,

TEXT( IF( ISBLANK( ShippingStreet ), 1, 0 )

+ IF( ISBLANK( ShippingCity ), 1, 0 )

    + IF( ISBLANK( ShippingState ), 1, 0 )

+ IF( ISBLANK( ShippingPostalCode ), 1, 0 ) ) )

OR

CASE( IF( ISBLANK( ShippingStreet ), 1, 0 )

+ IF( ISBLANK( ShippingCity ), 1, 0 )

    + IF( ISBLANK( ShippingState ), 1, 0 )

+ IF( ISBLANK( ShippingPostalCode ), 1, 0 )

   , 1, “T”, 2, “T”, 3, “T”, “” ) = “T”

The expressions above counts how many of the 4 fields are blank and makes the validation display the error message when 1, 2 or 3 fields are blank, but lets the validation pass when there are 0 or 4 fields blank. This might come in handy when you have to implement “all or nothing” validation for a greater number of fields.

__________________

(*) Salesforce validation rules display the error message when the condition is TRUE.