Quick and Easy Salesforce Validation Rules

Validation rules: the gatekeepers to data integrity. We use this Salesforce feature to ensure that our data meets business requirements or common sense. The best and simplest of which that I have encountered is ensuring that a field that tracks the temperature of a room exists between the coldest and warmest temperatures experienced on Earth. This is quite simple and looks like the following.

IF( OR( Temperature__c < -130, Temperature__c > 130), true, false)

Now, this is a simple example that will trigger an error only when the temperature is colder than Antarctica or hotter than a Libyan desert. Validation rules can handle more interesting problems and we will explore two of those below, both of which are applicable across verticals.

Other, but other what?

Whenever we take surveys, sometimes there is a question that says “Where did you hear about us?” and there is a picklist of options. Sometimes these match one of our choices, and other times they don’t. Good database design captures this “Other” selection and there is often a textbox for someone to elaborate on that choice. But as data integrity lovers, we need to have our data be consistent. If a user picks Other, then they must type something, and if they select anything aside from Other, then we don’t want to collect their free-form text.

For example say we are the system administrators for a banana distributor. Our manager tells us that we need to track which pesticide our banana farms use, and to capture the pesticides that don’t appear on our standard list. We currently have two fields: Pesticide_Picklist__c is a picklist field, and Other_Pesticide__c is a text field. Let’s take a look at what this validation rule will look like.

OR( AND( TEXT( Pesticide_Picklist__c ) == "Other", Other_Pesticide__c == "" ), AND( TEXT( Pesticide_Picklist__c ) <> "Other", Other_Pesticide__c <> "" ))

The rule triggers when either of the OR() conditions evaluates to true. The first of these states that our picklist value, converted to text using TEXT(), is “Other” and our text field is blank. This ensures that we collect data based on an other pesticide used. The second condition states that our picklist value is not “Other” and our text field is not blank. This is an important clause to use to avoid collecting data by accident, and will keep our data standardized.

15 minutes at a time

Our manager loves the validation rule that we wrote for tracking the pesticide, and has now given us another task. Every farm sprays their bananas with pesticide for some number of hours every week, and these occur in 15 minute time spans. We must track only 15 minute values in this 2 decimal number field called Time__c. Let’s take a look at how to make this work with a validation rule.

IF( AND( 
CONTAINS( TEXT(Time__c), "."), 
NOT( 
OR( RIGHT( TEXT(Time__c), 2) == ".0", 
RIGHT( TEXT(Time__c), 2) == ".5", 
RIGHT( TEXT(Time__c), 3) == ".00", 
RIGHT( TEXT(Time__c), 3) == ".25", 
RIGHT( TEXT(Time__c), 3) == ".50", 
RIGHT( TEXT(Time__c), 3) == ".75" 
))), true, false)

This might look a bit confusing at first, so let’s build the logic with words before looking at its translation into this formula. Logically, we only want our data as a whole number (i.e. no decimal) or a decimal that is a multiple of 0.25. Looking first at the innermost function, we see the following: RIGHT( TEXT(Time__c), 3) == “.75”. This again uses the TEXT() function to convert a value to text, the RIGHT() function to isolate the rightmost 3 characters, and then uses the double equals to compare it to a string “.75”. For the first two RIGHT() entries, we are only concerned with the rightmost 2 characters, because these are possibilities that are multiples of 0.25 without two decimals.

Now, expanding our view to the entire formula, we see that the IF() statement is true if and only if two conditions are met: 1) the value must contain a decimal point, and 2) the decimal number is not any possible combination of multiples of 0.25, or 15 minutes. If both conditions are met, the formula is true, and the validation rule will present the user with an error. Conversely, if both conditions are not met, then our rule is false and the data is clean.

Back to reality

We made a great team and our banana distributor’s org now has stops in place to collect good data. Both of these rules can be re-tailored to fit various industries, whether we look at the first for retail via a form on a Force.com Site, or the second for lawyers billing time to their big class action case.

If you want to gather around the virtual water cooler and talk more about validation rules, or share some great ones that you have written, send me a tweet @RogerMitchell, or leave us a comment below via Disqus or on our Facebook page at http://www.facebook.com/ArkusInc.