In this episode of ShellBlack Whiteboard, Shell walks you through how Validation Rules in Salesforce can help you enforce data quality to ensure users cannot enter conflicting information, save a record that is missing key fields, or enter data that is not in a standardized format (to help downstream with reporting). Shell takes you through multiple examples to understand the formula syntax you’ll need to learn when writing a Validation Rule.
View this video on YouTube: http://youtu.be/165Qq2jLbRk
Additional examples of Validation Rules (including the 2-digit state example mentioned in the video).
Missing Data – Lead Source field must be populated when converting a Lead:
AND(IsConverted, ISBLANK(TEXT(LeadSource)))
Missing Data – Field must be populated before you can Save:
AND (ISPICKVAL( Status , “Not Qualified”), ISBLANK ( Why_Not_Qualified__c ))
Missing Data – Enforce Opportunity creation on Lead conversion:
AND(IsConverted=true, ConvertedOpportunityId=””)
Conflicting Data:
CloseDate < Demo_Performed__c
Data Standard Example – Stock Ticker:
NOT(
OR(
LEN (Ticker__c ) = 0,
REGEX(Ticker__c , “[A-Z]{3}”),
REGEX(Ticker__c , “[A-Z]{4}”)
)
)
Either / Or (One or the other field needs to be populated):
OR(
AND(
ISBLANK(FieldOne__c), ISBLANK(FieldTwo__c)),
AND
(NOT(ISBLANK(FieldOne__c )), NOT(ISBLANK(FieldTwo__c))))
Transcription of the video:
Hello everyone and welcome to another edition of ShellBlack Whiteboard, where we explain a topic about Salesforce and provide some tips and tricks on the way to make sure you get the most out of Salesforce platform. So, I am Shell Black, President and founder of ShellBlack.com and Salesforce MVP, and the topic for this episode is Validation Rules. And Validation Rules are important because they help you enforce data quality insides Salesforce, makes sure you are not getting a bunch of trash in the system.
So enforcing data quality. So what do I mean by that? So we are looking at missing data (making sure someone cannot save a record if there is something missing), conflicting data (maybe you have two date fields, and one should be less than the other date). You don’t want to save the record with a conflict in there. And making sure that you have consistent data standards and formats, and probably the most popular example of that is the state field on addresses. So, if you want to make sure users are using are putting the two digit state. Texas “TX” rather having someone spell out “T.E.X.A.S” and saving the record. You want put a Validation Rule in place to make sure they can’t save a record unless it conforms to the standards you have in place. So when do Validation Rules run? So, anytime you create and save a record, or edit and save a record. So, basically it runs on save. So, if you have a bunch of rules in place, you save the record it is going to look and see what you have in place, and see if it needs to alert anybody. And when it does send an alert, what it is going to do is you have a choice. You can put a message at the top of the page saying “hey we have a problem here,” or you could put the message to display on the field that has got the problem. You can have multiple rules per object. So let’s say, run on the Lead record and you want to make sure something is in place on the Lead Status field and maybe you want to make sure that Lead Source is filled out. You can have three, four, or five, however many Validation Rules that you need in place, all running at the same time, so you are not limited to just one.
You can also put Validation Rules pretty much in any place in Salesforce. That includes standard objects, as well as your custom objects. So, standard objects would be things like Leads, Accounts, Contacts, Opportunities, Cases and so forth.
Something that kind of catches people out with Validation Rules is the syntax of with how you write them. I got a lot of examples coming up on how you write Validation Rules… You actually have to write them in a formula syntax. So if you are not good at writing custom formula fields in Salesforce, don’t get too alarmed. If you go into Salesforce and search “help” you there are actually some tip sheets that have lot’s of different examples of how to write these formulas and now we are going to go to the other side of the board, we are going to step through quite a few examples to make sure you are just kind of comfortable on how these are actually composed and written.
So, with that… I know this is a little bit of an eye chart… It is a little bit small. Don’t worry I will transcribe these formulas and I will put them in the notes…The show notes for this episode at the bottom. So, you don’t have to take notes from what we show on the screen, just copy the formula fields that I have on the transcription of this post.
So let’s look at a couple of Validation Rules to look at or try to identify that there is missing data on a record. So let me step through this formula, if you are not comfortable with formulas, I think once we get through this set of exercises things it will start to click. So, what we have right now is a status field, which is a pick list, so basically this formula is saying if the pick list status is in the value of “not qualified”… And the “why not qualified” field which is lets say is the free form text field is blank, then throw an error. So what it is trying to say if you have the status of “not qualified” you need to put something else in this field called “why not qualified” before you can save the record.
Users get in a hurry, they forget to do things. So this is going to stop them and throw them an error. Here is kind of an interesting one. This is one on Lead Conversion. So, the use case in this is we don’t want the user to convert the record without filling in the Lead Source. So, how this formula translates is… If the Lead Source, and we are converting that to text, if that is blank and it is converted, which is a formula called “isconverted” So “and” meaning both these conditions are true, this condition and this condition throw an error. So, if we convert the Lead and the Lead source is blank we are going to throw an error and stop the user and make them fill that out.
Let’s take another example… I like the conversion thing, so we got another lead conversion example here. So, what we are trying to do here is, and this is kind of a cool trick. We want to enforce creating Opportunity on Lead Conversion. So, basically this says the converted Opportunity ID equals blank, and the Lead is converted, so here is our “and” statement, this must be true, and this must be true, if both of those are true, throw an error. This is kind of cool because the error displays on the conversion page, not on the Lead page, but on the conversion page. So, we want to make sure we are enforcing the creation of the Opportunity record on conversion. So, start noticing that for the alert to happen, for it to block the save of the record, it has to be true. So that is something to keep in mind.
So, conflicting data… Real easy use case in conflicting data. If the Close Date is less than this other date field, and this date field I labeled “demo perform,” so the thought is we need to do the demo before we close the sale. So, if the Close Date is less than or earlier, then the “demo perform,” stop the user, because we probably need to go back and update some of this information. We have conflicting data. We want to stop the save of the record.
Ok… I have got two examples here of formats. I originally talked about missing data, conflicting data, or enforcing data quality, and formats, making sure your data is standardized. So, there are a couple of ways to do this. There is a really good formula out there for making sure that you have a two digit state field, but it is a very long formula in Help. I didn’t want to put it here. But I give you two other examples, of making sure people are entering in data consistently. So, the use case in this is someone had a stock ticker. So “CRM” is the stock ticker I think, for Salesforce. So, the use case is this we want to make sure people put in the stock ticker in all caps, and it had to be either three digit ticker, or three character ticker, or four digit ticker, or four character ticker. So, there is an interesting little formula in Salesforce called “regex, REGEX” that will look and compare the syntax based on how you define it, to make sure you are saving it right. So, basically the short answer on how this is done. Here is our ticker field, so the ticker could either be zero length or blank. Or the ticker must be a capital “A-Z” and three characters, or the ticker can be “A-Z” and four characters, and if that is not true, so we put a “not” statement to make the inverse of that, if it not true, it is going to block the save of the record.
Let’s take another quick syntax format example, social security number… comes up a lot, you can do this with phone numbers as well. So, the length of the social security custom field could be zero, or it needs to be in the following syntax so “0 to 9”, three characters, “0-9 numeric” dash two characters, “0-9” four characters, which is the syntax of a social security number. So, if that is true, you can save the record, but we put a “not statement” in front of it, so if it is not true it is going to stop the save.
So two examples, stock ticker and social security, on how you can force data quality, to make sure people are putting that in there. And then I threw in there one other example that comes up every once and a while, But I thought it was a neat example of how you can use the Validation Rule…and so the use case is you want to have either this field, field one populated, or field two populated, but not both. So, you cannot have both blank, you can’t have both populated, it is either the first field or the second. So, what this expression is saying is, either the first field one is blank, or field two is blank, and if that is true, or that both are blank we are going to block the save. If both fields are populated we are going to put in a “not statement” in front of the is blank, so basically field one is populated, field two is populated, if that is the case, we are also going to stop the save. So, that is an interesting use case, neither field can be blank, and neither field can be populated, we want “either, or”
So that kind of wraps up this one… So, again Validation Rules is all about data quality, and if you have good data quality, you are going to get good reports, you are going to have better performance through your Workflow Rules, and Assignment Rules, and some other things. Users get in a hurry, they don’t stop and remember thing things, you can’t, train all your users, so use Validation Rules to enforce data quality, to be the kind of nanny/babysitter making sure that your data is tight and looking good.
So I hope you enjoyed this episode. If you have feedback for us, you can contact us in a couple of ways… You can email me at WhiteBoard@ShellBlack.com, I am also out on Twitter, at Shell_Black on Twitter, we would love to hear your feedback and we will talk to you soon