Salesforce Useful Validation Formulas
DOWNLOAD
First things first !
To download this implementation guide, click the download button below.
If you need more information about the implementation guide, you can read the Table of Contents below.
EXAMPLES OF VALIDATION RULES
Summary
Review examples of
validation rules for various
types of apps that you can
use and modify for your own
purposes Validation rules
verify that the data a user
enters in a record meets the
standards you specify before
the user can save the record
Review examples of validation rules for various types of apps that you can use and modify for your own
purposes Validation rules verify that the data a user enters in a record meets the standards you specify
before the user can save the record
Use the following samples for validation rules in Salesforce and Salesforce AppExchange apps including
Sample Account Address Validation Rules
For more information on any of the formula functions used in these examples see Formula Operators and
Functions by Context
Canadian Billing Postal Code
Field
Value
Description
Validates that the account Billing ZipPostal Code is in the
correct format if Billing Country is Canada
Formula
AND
ORBillingCountry CAN BillingCountry CA
BillingCountry Canada
NOTREGEXBillingPostalCode
iABCEGHJKLMNPRSTVXYdAZsdAZd
Error Message
Canadian postal code must be in AA A format
Error Location
Billing ZipPostal Code
Note This release is in preview Features described in this document dont become generally available until
the latest general availability date that Salesforce announces for this release Before then and where features
are noted as beta pilot or developer preview we cant guarantee general availability within any particular
time frame or at all Make your purchase decisions only on the basis of generally available products and
features
Last updated March
Examples of Validation Rules
Sample Account Address Validation Rules
Billing Zip Code Is in Billing State
Field
Value
Description
Validates that the account Billing ZipPostal Code is valid
by looking up the first five characters of the value in a custom object
called ZipCodec that contains a record for every valid zip code in the
US If the zip code is not found in the ZipCodec object or the
Billing State does not match the corresponding StateCodec
in the ZipCodec object an error is displayed
Formula
VLOOKUP
ObjectTypeZipCodecFieldsCityc
ObjectTypeZipCodecFieldsName
LEFTBillingPostalCode BillingCity
Error Message
Billing Zip Code does not exist in specified Billing State
Error Location
Billing ZipPostal Code
US Billing Zip Code
Field
Value
Description
Validates that the account Billing ZipPostal Code is in
or format if Billing Country is USA or US
Formula
AND
ORBillingCountry USA BillingCountry
US
NOTREGEXBillingPostalCode dd
Note This example uses the REGEX function see Shipping Zip
Code if you are not familiar with regular expressions
Error Message
Zip code must be in or format
Error Location
Billing ZipPostal Code
Examples of Validation Rules
Sample Account Address Validation Rules
Shipping Zip Code
Field
Value
Description
Validates that the account Shipping ZipPostal Code is in or
format if Shipping Country is USA or blank
Formula
AND
ORShippingCountry USA
ISBLANKShippingCountry
OR
ANDLENShippingPostalCode
LENShippingPostalCode
NOTCONTAINS
LEFT ShippingPostalCode
NOTCONTAINS
MID ShippingPostalCode
NOTCONTAINS
MID ShippingPostalCode
NOTCONTAINS
MID ShippingPostalCode
NOTCONTAINS
MID ShippingPostalCode
AND
LENShippingPostalCode
OR
MID ShippingPostalCode
NOTCONTAINS
MID ShippingPostalCode
NOTCONTAINS
MID ShippingPostalCode
NOTCONTAINS
MID ShippingPostalCode
NOTCONTAINS
MID ShippingPostalCode
Note This example interprets a blank country as US To use this example
with other countries remove the clause that checks the length of the
country field Also validation rule criteria are case sensitive so this rule
is only enforced when the country is blank or USA in all capital letters
The rule is not enforced when the country is usa
Tip You can also validate zip codes using a regular expression for an
example of a formula using a regular expression see REGEX
Error Message
Zip code must be in or format
Error Location
Shipping ZipPostal Code
Examples of Validation Rules
Sample Account Address Validation Rules
Valid Billing State US
Field
Value
Description
Validates that the account Billing StateProvince is a valid
twocharacter abbreviation if Billing Country is US USA or
blank
Formula
AND
ORBillingCountry US BillingCountryUSA
ISBLANKBillingCountry
OR
LENBillingState
NOT
CONTAINSALAKAZARCACOCTDEDCFLGAHIID
ILINIAKSKYLAMEMDMAMIMNMSMOMTNENVNH
NJNMNYNCNDOHOKORPARISCSDTNTXUTVTVA
WAWVWIWY BillingState
Note This example interprets a blank country as US To use this
example with other countries remove the clause that checks the
length of the country field Also validation rule criteria are case
sensitive so this rule is only enforced when the country is blank
or USA in all capital letters The rule is not enforced when the
country is usa
Error Message
A valid twoletter state code is required
Error Location
Billing StateProvince
Examples of Validation Rules
Sample Account Address Validation Rules
Valid Billing Province Canada
Field
Value
Description
Validates that the account Billing StateProvince is a valid twocharacter
abbreviation if Billing Country is CA or CAN
Formula
AND
ORBillingCountry CA BillingCountryCAN
OR
LENBillingState
NOT
CONTAINSABBCMBNBNLNTNSNUONPCQCSKYT
BillingState
Error Message
A valid twoletter province code is required
Error Location
Billing StateProvince
Valid Shipping State
Field
Value
Description
Validates that the account Shipping StateProvince is a valid
twocharacter abbreviation if Shipping Country is US USA or blank
Formula
AND
ORShippingCountry US ShippingCountryUSA
ISBLANKShippingCountry
OR
LENShippingState
NOT
CONTAINSALAKAZARCACOCTDEDCFLGAHIID
ILINIAKSKYLAMEMDMAMIMNMSMOMTNENVNH
NJNMNYNCNDOHOKORPARISCSDTNTXUTVTVA
WAWVWIWY ShippingState
Note This example interprets a blank country as US To use this example
with other countries remove the clause that checks the length of the country
field Also validation rule criteria are case sensitive so this rule is only enforced
when the country is blank or USA in all capital letters The rule is not enforced
when the country is usa
Error Message
A valid twoletter state abbreviation is required
Error Location
Shipping StateProvince
Examples of Validation Rules
Sample Account Address Validation Rules
Valid Shipping Province Canada
Field
Value
Description
Validates that the account Shipping StateProvince is a valid
twocharacter abbreviation if Billing Country is CA or CAN
Formula
AND
ORShippingCountry CA ShippingCountryCAN
OR
LENShippingState
NOT
CONTAINSABBCMBNBNLNTNSNUONPCQCSKYT
ShippingState
Error Message
A valid twoletter province abbreviation is required
Error Location
Shipping StateProvince
Examples of Validation Rules
Sample Account Validation Rules
Valid Billing Country
Field
Value
Description
Validates that the account Billing Country is a valid ISO twoletter
code
Formula
OR
LENBillingCountry
NOT
CONTAINS
AFAXALDZASADAOAIAQAGARAM
AWAUAZBSBHBDBBBYBEBZBJBMBTBO
BABWBVBRIOBNBGBFBIKHCMCACVKY
CFTDCLCNCXCCCOKMCGCDCKCRCIHR
CUCYCZDKDJDMDOECEGSVGQEREEETFK
FOFJFIFRGFPFTFGAGMGEDEGHGIGRGL
GDGPGUGTGGGNGWGYHTHMVAHNHKHU
ISINIDIRIQIEIMILITJMJPJEJOKZKEKI
KPKRKWKGLALVLBLSLRLYLILTLUMOMK
MGMWMYMVMLMTMHMQMRMUYTMXFMMDMC
MCMNMEMSMAMZMMMANRNPNLANNCNZNI
NENGNUNFMPNOOMPKPWPSPAPGPYPEPH
PNPLPTPRQARERORURWSHKNLCPMVCWS
SMSTSASNRSSCSLSGSKSISBSOZAGSES
LKSDSRSJSZSECHSYTWTJTZTHTLTGTK
TOTTTNTRTMTCTVUGUAAEGBUSUMUYUZ
VUVEVNVGVIWFEHYEZMZW
BillingCountry
Error Message
A valid twoletter country code is required
Error Location
Billing Country
Sample Account Validation Rules
For more information on any of the formula functions used in these examples see Formula Operators and
Functions by Context
Examples of Validation Rules
Sample Account Validation Rules
Account Number Is Numeric
Field
Value
Description
Validates that the Account Number is numeric if not blank
Formula
OR
ISBLANKAccountNumber
NOTISNUMBERAccountNumber
Error Message
Account Number is not numeric
Error Location
Account Number
Account Number Length
Field
Value
Description
Validates that the Account Number is exactly seven digits if it is not
blank The number seven is simply illustrative You can change this to
any number you like
Formula
AND
ISBLANKAccountNumber
LENAccountNumber
Error Message
Account Number must be seven digits
Error Location
Account Number
Annual Revenue Range
Field
Value
Description
Validates that the account Annual Revenue is not negative and
does not exceed billion This limit is designed to catch typos
Formula
OR
AnnualRevenue
AnnualRevenue
Error Message
Annual Revenue cannot exceed billion
Error Location
Annual Revenue
Examples of Validation Rules
Sample Call Center Validation Rules
Sample Call Center Validation Rules
For more information on any of the formula functions used in these examples see Formula Operators and
Functions
Conditionally Require Description When Case Reason is Other
Field
Value
Description
Validates that a custom field called Other Reason contains a value
if a case has a Case Reason of Other
Formula
AND
ISPICKVAL Reason Other
ISBLANKOtherReasonc
Error Message
Description of Other Reason is required
Error Location
Other Reason
Prevent Open Cases from Being Reset to New
Field
Value
Description
If a case is already open prevents the Status from being changed
back to New
Formula
AND
ISCHANGED Status
NOTISPICKVALPRIORVALUE Status New
ISPICKVAL Status New
Error Message
Open case Status cannot be reset to New
Error Location
Status
Examples of Validation Rules
Sample Call Center Validation Rules
Restrict Status of ReOpened Cases
Field
Value
Description
Validates that the case Status is Reopened when a closed case is
opened again
Formula
AND
ISCHANGED Status
OR
ISPICKVALPRIORVALUE Status Closed
ISPICKVALPRIORVALUE Status
Closed in SSP
NOT ISPICKVAL Status ReOpened
Error Message
Closed case can only be changed to Reopened
Error Location
Status
Prevent Case Milestone Completion After Cases Are Closed
Field
Value
Description
Validates that a milestones Completion Date cant occur after the
cases Status is Closed
Formula
CaseIsClosed
true
Error Message
You cant complete a milestone after a case is closed
Error Location
Top of Page
Prevent Case Milestone Completion Before Case Creation Dates
Field
Value
Description
Validates that the milestones Completion Date has occurred after
the cases DateTime Opened
Formula
CompletionDate
CompletionDate
CaseCreatedDate
CaseClosedDate
Error Message
The milestone Completion Date must occur after the date the case was
created and before the case was closed
Error Location
Top of Page
Examples of Validation Rules
Sample Experience Cloud Site Validation Rules
Sample Experience Cloud Site Validation Rules
For more information on any of the formula functions used in these examples see Formula Operators and
Functions by Context
Preventing Offensive Language in Questions
Field
Value
Description
Prevents users from entering offensive language in the Title and
Description fields when asking a question
Formula
Error Message
ORCONTAINSTitle darn CONTAINSBody
darn
Question title or description contains offensive language
Preventing Offensive Language in Replies
Field
Value
Description
Prevents users from entering offensive language when replying to a
question
Formula
Error Message
ORCONTAINSBody darn CONTAINSBody
dang
Reply contains offensive language
Preventing Offensive Language in Ideas
Field
Value
Description
Prevents users from entering offensive language in the Title and
Description fields when posting an idea
Formula
Error Message
ORCONTAINSTitle darn CONTAINSBody
darn
Idea title or description contains offensive language
Examples of Validation Rules
Sample Contact Validation Rules
Preventing Offensive Language in Idea Comments
Field
Value
Description
Prevents users from entering offensive language when posting a
comment
Formula
Error Message
ORCONTAINSCommentBody darn
CONTAINSCommentBody dang
Comment contains offensive language
Sample Contact Validation Rules
For more information on any of the formula functions used in these examples see Formula Operators and
Functions
Mailing Address Fields Are Required
Field
Value
Description
Validates that the contact Mailing Street Mailing City and
Mailing Country are provided
Formula
OR
ISBLANK MailingStreet
ISBLANK MailingCity
ISBLANK MailingCountry
Error Message
Mailing Street City and Country are required
Error Location
Top of Page
Mailing Street Is Required
Field
Value
Description
Validates that the contact Mailing Street is provided
Formula
ISBLANK MailingStreet
Error Message
Mailing Street is required
Error Location
Mailing Street
Examples of Validation Rules
Sample Contact Validation Rules
Mailing Zip Code
Field
Value
Description
Validates that the contact Mailing ZipPostal Code is in or
format if Mailing Country is USA or blank
Formula
AND
ORMailingCountry USA ISBLANKMailingCountry
OR
ANDLENMailingPostalCode
LENMailingPostalCode
NOTCONTAINS
LEFT MailingPostalCode
NOTCONTAINS
MID MailingPostalCode
NOTCONTAINS
MID MailingPostalCode
NOTCONTAINS
MID MailingPostalCode
NOTCONTAINS
MID MailingPostalCode
AND
LENMailingPostalCode
OR
MID MailingPostalCode
NOTCONTAINS
MID MailingPostalCode
NOTCONTAINS
MID MailingPostalCode
NOTCONTAINS
MID MailingPostalCode
NOTCONTAINS
MID MailingPostalCode
Note This example interprets a blank country as US To use this example
with other countries remove the clause that checks the length of the
country field Also validation rule criteria are case sensitive so this rule
is only enforced when the country is blank or USA in all capital letters
The rule is not enforced when the country is usa
Tip You can also validate zip codes using a regular expression for an
example of a formula using a regular expression see REGEX
Error Message
Zip code must be in or format
Error Location
Mailing ZipPostal Code
Examples of Validation Rules
Sample Cross Object Validation Rules
Phone Number Has International Format
Field
Value
Description
Validates that the Phone number begins with a plus sign for country
code Note that this validation rule conflicts with the tendigit rule
Formula
LEFTPhone
Error Message
Phone number must begin with country code
Error Location
Phone
US Phone Number Has Ten Digits
Field
Value
Description
Validates that the Phone number is in format This works
by using the REGEX function to check that the number has ten digits in
the format
Formula
NOTREGEXPhone DdD
Error Message
US phone numbers should be in this format
Error Location
Phone
Sample Cross Object Validation Rules
For more information on any of the formula functions used in these examples see Formula Operators and
Functions by Context
Discounts Must Be Within Range
This example consists of three validation rules on opportunity products The examples below work together
to help you manage discount amounts for products and require a custom percent field on opportunity
products called Line Discount The examples below also require you to use price books and
customize the Product Family field to include the following values Software Consulting
and Training
Software Discounts
Field
Value
Description
Prevents users from saving software products with
a discount over percent
Examples of Validation Rules
Sample Cross Object Validation Rules
Field
Value
Formula
ANDLineDiscountc
ISPICKVALProductFamily
Software
Error Message
The discount must be or less for software
products
Error Location
Line Discount
Consulting Discounts
Field
Value
Description
Prevents users from saving consulting products
with a discount over percent
Formula
ANDLineDiscountc
ISPICKVALProductFamily
Consulting
Error Message
The discount must be or less for consulting
products
Error Location
Line Discount
Training Discounts
Field
Value
Description
Prevents users from saving training products with
a discount over percent
Formula
ANDLineDiscountc
ISPICKVALProductFamily
Training
Error Message
The discount must be or less for training
products
Error Location
Line Discount
Prevent Changing Opportunity Products on Closed Opportunities
This example consists of two validation rules one on opportunity products and another on opportunities
