We’ve written in the past about how as a nonprofit, your data is one of your most valuable assets. Keeping your Salesforce account safe, secure, and accurate should be a top priority of your organization. Today, we’ll explore that last part -- one way to maintain a “clean” and complete database.
Validation Rules are a powerful data management tool in Salesforce. They prevent users from editing and saving records that don’t make sense. A validation rule allows an admin to create a custom error message that prevents a record from being created or updated if certain conditions are not met.
For example, if an Opportunity type is “Refund,” the dollar amount should be a negative number. Here’s what a user might see if they try to save that Opportunity with a positive value in the Amount field:
Validation rules are comprised of three sections:
- A name
- A formula
- An error message
When creating a validation rule, it’s usually best to work backwards and start with the error message you want to provide. What are you trying to prevent, and what would you need to tell the user if they’ve made a mistake? The error message should be as descriptive and helpful as possible:
“Amount needs to be a negative number when Opportunity Type is marked as a Refund.”
Each rule can only have one error message, so it is best to keep your validation rule simple and targeted! Keep in mind the potential impact on your users -- if your validation rules are too broad, or if you have too many, your users will come up with really clever ways to work around them and enter the exact kind of bad data that you’re trying to avoid. Your job as a Salesforce admin is to make sure that the validation rules you write are a helping hand, not a wrathful wrist slap.
The next step is to write the formula for the rule -- the conditions that would need to be met for the Error to display. When building a Validation Rule formula, I find it helpful to write out two sentences. One is what a “good” record looks like, and the second is what a “bad” record looks like.
A useful structure is “When [FIELD] is [CONDITION], [do/don’t] allow this record to be saved.” For our example:
Good record: When Amount is less than or equal to $0 and when the Type picklist equals Refund, do allow this record to be saved.
Bad record: When the Amount is greater than $0 and when the Type picklist equals Refund, don’t allow this record to be saved.
The “bad” version of your sentence becomes the logic you use to write the formula! In this case:
ISPICKVAL(Type , "Refund"),
(Amount > 0)
If you are familiar with creating custom formula fields in Salesforce, then you already know the basics of writing these Validation Rule formulas; if not, you may want to start with creating some custom formula fields first for practice before diving in. The big difference is that in validation rules, the formula must return either TRUE or FALSE -- think of it like a “checkbox”-type formula field.
This true/false requirement can make writing validation rules a little challenging, more like solving a puzzle than writing a formula -- the puzzle unlocks and the error message gets presented when the formula is evaluated and returns TRUE.
Some helpful tips about formulas for validation rules:
AND() and OR() functions return TRUE or FALSE as a result, which makes them great starting places for your formula (as opposed to IF(), which returns whatever value you specify).
Validation rules often use picklist fields as criteria, which require using special functions in your formula. [Read more about ISPICKVAL() and CASE() here] [LINK: https://trailhead.salesforce.com/content/learn/modules/advanced_formulas....
Since validation rules are often used to restrict changing and updating different kinds of data, the functions ISBLANK(), ISNUMBER(), ISNEW(), ISCLONED(), and ISCHANGED() are all especially useful.
If you’re ready for something more advanced, REGEX() is the most powerful function of them all when it comes to formulas. You can read more about that here.
Now you have an error message, a name, and a formula, and you’re ready to test and roll out your validation rules. Just like any other admin change in your Salesforce account, it’s important to test thoroughly and communicate with your users! Since validation rules prevent people from saving records, too-restrictive ones can prevent your users from doing their job, so you’ll want to be sure to catch any problems early.
Salesforce Formulas Cheatsheet
Trailhead Project: Improve Data Quality for a Recruiting App
It's time to try out a validation rule on your own! If you need our assistance, please submit a support ticket.