This tutorial covers some basic functions and logic that are useful when creating formulas in Salesforce. The examples below specifically refer to Validation Rule formulas, which can sometimes behave slightly different from other formulas in Salesforce.

• When writing a new formula or validation rule, I always like to have a tab open with a  “formula cheat sheet”, like this one.

Remember:

Validation rules prevent records from being saved when the result of the tests are TRUE.

Everything inside a single validation rule is evaluated at the same time. Any criteria that needs to be evaluated needs to be defined in your formula. Sometimes that means adding a “NOT” in front of the expression.

Scenario 1: Account Number field can be saved with blank values but when a value is entered, it must be numeric only.

There are 2 logic tests here and one field referred:

Logic Test 1: NOT (ISBLANK (AccountNumber))

Logic Test 2: NOT (ISNUMBER (AccountNumber))

AND ties both conditions together

• If a user tries to save a record but leaving the Account Number field blank. Blank is a value. The validation rule created for the Account Number field will run as follows:

1. Is it “not blank”? False

   AND

2. Is it “not a number”? False

The end result of this test is FALSE, therefore the record can be saved.

• Now, if the value for Account Number is entered as “Acme”.

1. Is it “not blank”? True

   AND

2. Is it “not a number”? True (they’re letters)

Formula expression returns as TRUE and prevents the record from being saved.

• Now, a new value is entered for Account Number and it’s  “12345”.

1. Is it “not blank”? True

   AND

2. Is it “not a number”? False

Formula expression returns as FALSE and record can be saved.

Tip: If you use this function with a picklist, use ISBLANK(TEXT(<picklist>)) to convert the picklist items into a text value.

Scenario 2: Email field must contain a valid email address format, if not blank.

1. 1. •      When there is a predictable pattern of numbers, letters and punctuation, REGEX is your friend. REGEX stands for Regular Expression and is used when you want the value to follow a specific format, such as Social Security Numbers, or Driver’s License numbers that have letters and numbers, post codes, email addresses etc.

E.G. Social Security number – an important field where not only it has to contain only numeric, but observe a specific pattern of XXX – XX –XXXX

Formula:

Note that OR is used here to prevent a record from being saved if EITHER of the conditions return TRUE.  If SSN length (LEN) is not 0 (a blank value wouldn’t cause an error message but any other random value would);

If SSN doesn’t observe the regular expression in the formula.

Note: Regular expression syntax in Salesforce is based on Java Platform SE 6 syntax. However, backslash characters (\) must be changed to double backslashes (\\) because backslash is an escape character in Salesforce. 

REGEX is also very useful when you create a validation rule that conditions one field to another. E.g., if post code is in the UK, must have [A-Z]{1,2}[0-9][0-9A-Z]?\s?[0-9][A-Z]{2} format.

Learn More:

• Tips for Writing Validation Rules
• Formula Operator Functions A-H
• Formula Operator Functions I-Z
• Video: Formulas for Dummies