Okay, let’s be honest – we can’t always trust our users to enter all of the data that we’d like to have in our database. And I don’t like to place the blame on the user – some of the most spectacular salespeople in the world just aren’t blessed with great attention to detail. This is why validation rules exist. They will trigger an error message when the user tries to save a record with incomplete or incompatible data entry, saving administrators the hassle of checking everything after the fact.

Here are the formulas for some of my favorites, based on real-life needs or complaints of SFDC admins. The formula builder can be intimidating if you are not familiar with writing these kinds of formulas, but it’s fairly easy to learn. Use the “Insert Field” button to replace the sample fields in my formulas with fields of your own.

“I want all US and Canadian states to have the standard two-character state format.”
These formulas are for billing addresses, but can easily be modified for shipping addresses, in contacts as well as accounts. You can also use them for leads, but the field name is “State/Province” instead of “Billing State.”

AND (
OR(BillingCountry = “US”, BillingCountry=”USA”, ISBLANK(BillingCountry)),
OR(
LEN(BillingState) < 2,
NOT(
CONTAINS(“AL:AK:AZ:AR:CA:CO:CT:DE:DC:FL:GA:HI:ID:” &
“IL:IN:IA:KS:KY:LA:ME:MD:MA:MI:MN:MS:MO:MT:NE:NV:NH:” &
“NJ:NM:NY:NC:ND:OH:OK:OR:PA:RI:SC:SD:TN:TX:UT:VT:VA:” &
“WA:WV:WI:WY”, BillingState)
)))

AND (
OR(BillingCountry = “CA”, BillingCountry=”CAN”),
OR(
LEN(BillingState) < 2,
NOT(
CONTAINS(“AB:BC:MB:NB:NL:NT:NS:NU:ON:PC:QC:SK:YT”, BillingState)
)))

“I want a field to have a certain number of digits.”
This formula is useful if you have invoice numbers or customer numbers that are a certain length. It specifies seven digits but is easy to modify.

AND(
ISBLANK(AccountNumber),
LEN(AccountNumber) <> 7
)

“I have a field that only the record owner should be allowed to edit.”

AND(
ISCHANGED( Custom_Field__c ),
Owner <> $User.Id
)

“I have a text field that should only be used if another field is populated.”
I like this rule because sometimes I create text fields for a specific purpose, and then my users fill them with text that belongs in the Description field or elsewhere.

AND(
NOT( ISBLANK( Custom_Text_Field__c ) ),
ISBLANK( Other_Custom_Field__c )
)

“I don’t want an Open case to be allowed to be set back to New.”
Because you never know when someone will decide to make it look like they have not yet received a support case.

AND(
ISCHANGED( Status ),
NOT(ISPICKVAL(PRIORVALUE( Status ), “New”)),
ISPICKVAL( Status, “New”)
)

“End Date cannot be earlier than Begin Date.”
This is a must if you have fields pertaining to the begin and end dates of a subscription product or a service engagement.

Begin_Date__c > End_Date__c

“I don’t want opportunities to go beyond a certain stage without containing products.”

AND(
OR ( ISPICKVAL( StageName, “Stage4″ ), ISPICKVAL( StageName,”Stage5″ ),
ISPICKVAL( StageName,”Stage6″ ), ISPICKVAL( StageName,”Closed Won” ) ),
NOT( HasOpportunityLineItem ), NOT( ISNEW () )
)

“I don’t want an opportunity closed without approval if it is above a certain amount.”
This will only work if you have an approval process in place. Another easy way to monitor opportunities of large amounts is to create a workflow rule that sends an email notification when these opportunities are created and/or edited.

AND(
OR(
ISPICKVAL(StageName,”Closed Won”),
ISPICKVAL(StageName,”Closed Lost”)),
(Amount > 500000),
NOT(ISPICKVAL(Approval_Status__c ,”Approved”)))

“I want to prevent discounts above 40 percent.”
This is only useful if your maximum discount is carved in stone. Nobody can get past an active validation rule, not even us admins! Note: it needs to be used on the opportunity product rather than the opportunity.

Discount > .40

“I have a picklist value of Other, and I want to require additional info when that value is selected.”
In some cases, you can use field dependencies instead of a validation rule for this situation. But when that is not ideal, this formula will do the trick.

AND(
ISPICKVAL( Reason, “Other” ),
ISBLANK(Other_Reason__c)
)

“If Custom Field #1 has a value of Abcxyz, then Custom Fields #2 (date field), #3 (text field), and #4 (lookup field) must be populated when the opportunity is set to Closed Won.”
This is a complicated one – I’m adding it because I had to write it myself, and it took me forever! Sometimes there is information that should only be mandatory in some opportunities – and this formula will help you if you cannot confine those opportunities to a single record type. Note: remove the 2nd line of the formula if stage doesn’t matter.

AND(
( ISPICKVAL( StageName , “Closed Won” ) ),
( ISPICKVAL( CustomField1__c , “Abcxyz” ) ),
( OR (
ISNULL( CustomField2__c ), ISBLANK( CustomField2__c ), ISBLANK( CustomField3__c )
) )
)

These are just a sampling of validation rules that you may want to consider. You can find many, many more here. I’m not gonna lie – I stole a lot of mine from the Help & Training. I hope I’ve saved you a little bit of effort. Please feel free to comment with your favorites. You may be able to help the rest of us!