Salesforce formulas and validation rules can be challenging and a headache at times. To offer a helping hand, here are 5 useful ones I have created, and used this year.

Want to know more about creating formulas and validation rules then visit Trailhead to get started.

Useful Salesforce formulas and validation rules

Opportunity Lost and Loss, Turned Down By Reason required,

When an Opportunity has been closed lost it is always useful to obtain the reason why. This validation rule gives us this exactly. For this, we are using multiple picklists fields in the rule.

Error Condition Formula

[php]AND (
OR (
ISPICKVAL( Event_Stage__c , "Closed Lost")),
(ISBLANK(TEXT( Turndown_By__c )) ||
(ISBLANK(TEXT( Loss_Reason__c )))
))
[/php]

Error Message – Please provide a value for Turndown By and Loss Reason when the Opportunity has been lost.

Formula to set the Region Field based on the Country

One use case was to display the region where the client was based without using automation. We used the mailing address country of the contact to create an automated region field.

[php]IF(ISBLANK(MailingCountry),NULL,
IF(CONTAINS("Australia:Fiji:Kiribati:Marshall Islands:Micronesia:Nauru:New Zealand:Palau:Papua New Guinea:Samoa:Soloman Islands:Tonga:Tuvalu:Vanuata", MailingCountry), "Oceania",
IF(CONTAINS("Argentina:Bolivia:Brazil:Chile:Colombia:Ecuador:Guyana:Paraguay:Peru:Suriname:Uruguay:Venezuela", MailingCountry), "South America",
IF(CONTAINS("Antigua and Barbuda:Bahamas:Barbados:Belize:Canada:Costa Rica:Cuba:Dominica:Dominican Republic:El Salvador:Grenada:Guatemala:Haiti:Honduras:Jamaica:Mexico:Nicaragua:Panama:Saint Kitts and Nevis:Saint Lucia:Saint Vincent and the Grenadines:Trinidad and Tobago:United States:USA", MailingCountry), "North America",
IF(CONTAINS("Albania:Andorra:Armenia:Austria:Azerbaijan:Belarus:Belgium:Bosnia and Herzegovina:Bulgaria:Croatia:Cyprus:Czech Republic:Denmark:Estonia:Finland:France:Georgia:Germany:Greece:Hungary:Iceland:Ireland:Italy:Latvia:Liechtenstein:Lithuania:Luxembourg:Macedonia:Malta:Moldova:Monaco:Montenegro:Netherlands:Norway:Poland:Portugal:Romania:San Marino:Serbia:Slovakia:Slovenia:Spain:Sweden:Switzerland:Ukraine:United Kingdom:Vatican City", MailingCountry), "Europe",
IF(CONTAINS("United Arab Emirates:Bahrain:Iran:Iraq:Israel:Jordan:Kuwait:Lebanon:Oman:Palestine:Qatar:Saudi Arabia:Syria:Yemen", MailingCountry), "Middle East",
IF(CONTAINS("Afghanistan:Bangladesh:Bhutan:Brunei:Burma (Myanmar):Cambodia:China:East Timor:India:Indonesia:Japan:Kazakhstan:North Korea:South Korea:Kyrgyzstan:Laos:Malaysia:Maldives:Mongolia:Nepal:Pakistan:Philippines:Russia:Russian Federation:Singapore:Sri Lanka:Tajikistan:Thailand:Turkey:Turkmenistan:United Arab Emirates:Uzbekistan:Vietnam", MailingCountry), "Asia",
IF(CONTAINS("Algeria:Angola:Benin:Botswana:Burkina:Burundi:Cameroon:Cape Verde:Central African Republic:Chad:Comoros:Congo:Congo, Democratic Republic of:Djibouti:Egypt:Equatorial Guinea:Eritrea:Ethiopia:Gabon:Gambia:Ghana:Guinea:Guinea-Bissau:Ivory Coast:Kenya:Lesotho:Liberia:Libya:Madagascar:Malawi:Mali:Mauritania:Mauritius:Morocco:Mozambique:Namibia:Niger:Nigeria:Rwanda:Sao Tome and Principe:Senegal:Seychelles:Sierra Leone:Somalia:South Africa:South Sudan:Sudan:Swaziland:Tanzania:Togo:Tunisia:Uganda:Zambia:Zimbabwe", MailingCountry), "Africa", NULL
)
)
)
)
)
)
)
)

[/php]

Formula to set the Time Zone Field based on the State

We work with a client that has a big US client base. It’s useful for the business to know the time zone for each client so they knew when to contact them and market to them. Check out the formula I used to make this possible below.

[php]IF(ISBLANK(MailingState ), NULL,
IF(CONTAINS("Guam (GU):Northern Mariana Islands(MP)", MailingState ), "UTC+10: Chamorro Standard Time (ChT)",
IF(CONTAINS("Hawaii(HI)", MailingState ), "UTC-10: Hawaii-Aleutian Standard Time (HAT)",
IF(CONTAINS("American Samoa", MailingState ), "UTC-11: Samoa Standard Time (ST)",
IF(CONTAINS("Puerto Rico (PR):United States Virgin Islands (USVI)", MailingState ), "UTC−4: Atlantic Standard Time (AST)",
IF(CONTAINS("Connecticut(CT):Delaware(DE):District of Columbia(DC):Florida(FL):Georgia(GA):Indiana(IN):Maine(ME):Maryland(MD):Massachusetts(MA):Michigan(MI):New Hampshire(NH):New Jersey(NJ):New York(NY):North Carolina(NC):Ohio(OH):Pennsylvania(PA):Rhode Island(RI):South Carolina(SC):Vermont(VT):Virginia(VA):West Virginia(WV)", MailingState ), "UTC−5: Eastern Standard Time (ET)",
IF(CONTAINS("Alabama(AL):Arkansas(AR):Illinois(IL):Iowa(IA):Kansas(KS):Kentucky(KY):Louisiana(LA):Minnesota(MN):Mississippi(MS):Missouri(MO):Nebraska(NE):North Dakota(ND):Oklahoma(OK):South Dakota(SD):Tennessee(TN):Texas(TX):Wisconsin(WI)", MailingState ), "UTC−6: Central Standard Time (CT)",
IF(CONTAINS("Arizona(AZ):Colorado(CO):Montana(MT):New Mexico(NM):Utah(UT):Wyoming(WY)", MailingState ), "UTC−7: Mountain Standard Time (MT)",
IF(CONTAINS("California(CA):Nevada(NV):Oregon(OR):Washington(WA)", MailingState ), "UTC−8: Pacific Standard Time (PT)",
IF(CONTAINS("Alaska(AK):", MailingState ), "UTC-9: Alaska Standard Time (AKT)",NULL
)
)
)
)
)
)
)
)
)
)

[/php]

Formula Field on Campaign Member to display the Last time the Contact or Lead was Last Active(Tracked in Pardot)

For this, we have a customer with multiple campaigns and they were looking for an easy way in Salesforce when looking at campaign members to display, sort by, and easily report on, when records were last active.

[php]IF (ISBLANK( DATETIMEVALUE( Lead.pi__last_activity__c )),Contact.pi__last_activity__c,
IF (ISBLANK( DATETIMEVALUE( Contact.pi__last_activity__c )),Lead.pi__last_activity__c,NULL))

[/php]

Set a Default Value in Date & Time Field

For the final formula, when creating a record the client wanted the default time and date value to be set. The below sets the date to today and start time for 7 am.

[php]DATETIMEVALUE(TEXT(TODAY())&’ 07:00:00′)

[/php]

Find out more

We’ve been in the Sales and Marketing Strategy game for longer than we care to remember, but we pride ourselves on the combined exposure our team has had to different B2B markets globally, and the strategic services we offer your business as a result.

If you are looking for Salesforce / Pardot Support with an existing Org or want to know if Salesforce or Pardot is right for you, get in touch with us for a free consultation or simply a chat. We regularly work alongside Start-ups and Enterprises alike, so we’re certain we will find you a path to growth, whatever your plans.

We are open Monday-Friday 9-6pm and have open channels for communication either on our LinkedIn, Twitter or you can simply give us a call on 020 8106 8500.