Sudipta Deb

Sudipta Deb

Founder of Technical Potpourri, Co-Founder of Shrey Tech, Enterprise Cloud Architect

Salesforce Formulas are a powerful tool within the Salesforce platform, designed to enhance the functionality and interactivity of your Salesforce applications. These formulas allow users to perform calculations, manipulate text, and automate processes, thereby transforming data into actionable insights. They are akin to the formulas used in spreadsheet applications but tailored for Salesforce’s robust CRM environment. By utilizing Salesforce Formulas, administrators and developers can create dynamic fields, validation rules, and workflows that respond intelligently to user input and data changes. This capability not only streamlines operations but also ensures data integrity and enhances the user experience. Whether you are calculating sales commissions, formatting dates, or validating data entries, Salesforce Formulas offer a versatile and indispensable resource for maximizing the efficiency and effectiveness of your Salesforce instance.

In this blog post, I will share some of the most commonly used formulas. I will try to keep this blog post up to date by posting new formulas as I experience it in future.

Text Operators (& or +)

This is used to concatenate several strings together to make it a single string. Below is the example where I am concatenating Contact FirstName and LastName to make it Full Name.

Logical – CASE

Case() can be used to replace multiple IF ELSE statements. It has three parts – first one is the logic that needs to be evaluated, followed by two arguments for each scenario (true and false). The final one is the catch-all argument for other scenarios that are defualt case

CASE(MONTH(Birthdate), 1, 'January', 2, 'February', 3, 'March', 4, 'April', 5, 'May', 
 6, 'June', 7, 'July', 8, 'August', 9, 'September', 10, 'October', 
 11, 'November', 12, 'December', 'Wrong Month')

As you can see in the above formula, I am extracting the month from the Contact’s Date of Birth and then using the Case function to determine the numeric value of month and then printing the name of the month.

Logical – IF

IF is the most commonly used logical operator. It has three parts – first one is the logic that needs to be evaluated, second one is the value if the evaluation comes as true and third one is the value if the evaluation comes as false.

IF(Languages__c == 'EN', 'ENGLISH', 'NON-ENGLISH')

As you can see in the above formula, I am checking the language value from Contact and if the language equals to EN, then I am saving ENGLISH otherwise it will be NON-ENGLISH‘.

Logical – ISBLANK() & ISNULL()

ISBLANK(expression) checks whether an expression is blank and returns TRUE or FALSE, whereas ISNULL(expression) checks whether an expression is null and returns TRUE or FALSE. It is always recommened to use to ISBLANK() instead of ISNULL()

ISBLANK(HomePhone)
ISNULL(HomePhone)

Why ISBLANK() instead of ISNULL()?

Use ISBLANK() instead of ISNULL() in new formulas. ISBLANK() has the same functionality as ISNULL(), but also supports text fields. 

When dealing with text and lookup fields, a blank value in the field yields true for ISBLANK() but false for ISNULL()—even if you set a text field to null in apex it is still treated as blank (i.e. not null) in the formula.

Essentially the system works the same way as you’d expect in a programming language: an empty string is not the same as a null value, it is still an instance of a string.

Logical – BLANKVALUE

It checks whether expression is blank and returns substitute_expression if it is blank. If expression is not blank, returns the original expression value.

BLANKVALUE(MailingCountry ,'No Mailing Country Set')

The above formula checks whether the MailingCountry is set in the Contact record. If it is set, then it will return the same, otherwise it will return ‘No Mailing Country Set’.

Text Functions – TEXT() / ISPICKVAL()

TEXT() converts a value to text using standard display format. ISPICKVAL() checks whether the value of a picklist field is equal to a string literal

ISPICKVAL(LeadSource, 'Web')

The above formula checks whether the picklist value of LeadSource is Web or not. If it is Web, this formula will return True, otherwise will return False.

Text Functions – BR()

This will insert an HTML break tag in string formulas. While dealing with Flow text resources, it becomes very helpful to add an extra line between texts to add some sort of formatting.

Text Functions – BEGINS() / CONTAINS() / INCLUDES()

BEGINS(text, compare_text) Checks if text begins with specified characters and returns TRUE if it does. Otherwise returns FALSE.

CONTAINS(text, compare_text) – Checks if text contains specified characters, and returns TRUE if it does. Otherwise, returns FALSE

INCLUDES(multiselect_picklist_field, text_literal) – Determines if any value selected in a multi-select picklist field equals a text literal you specify.

Text Functions – SUBSTITUTE

SUBSTITUTE(text, old_text, new_text) – Substitutes new_text for old_text in a text string. Use SUBSTITUTE when you want to replace specific text in a text string

SUBSTITUTE(Languages__c , 'EN', 'ENGLISH')

The above formula checks the vaue of Languages field and if it has ‘EN’, then it will replace ‘EN’ with ‘ENGLISH’.

Text Functions – TRIM() And LEN()

TRIM(text) – Removes all spaces from a text string except for single spaces between words

LEN(text) – Returns the number of characters in a text string

Text Functions – GETSESSIONID() And HYPERLINK()

GETSESSIONID() – Returns the current session ID. This may be useful in hyperlinks to other applications that use salesforce.com credentials for authentication.

HYPERLINK(url, friendly_name [, target]) – Creates a hyperlink

HYPERLINK("https://sudipta-deb.in"&
GETSESSIONID() & "/2024/07/commonly-used-salesforce-formulas-good-to-know.html","technical-potpourri")

The above formula creates a hyperlink by utilizing the GETSESSIONID() function and finally adding the value as part of the hyperlink url.

Text Functions – INITCAP() And UPPER()

INITCAP(text) – Return the text as lowercase with first character of each word made uppercase.

UPPER(text) – Converts all letters in the value to uppercase

Text Functions – FIND()

FIND(search_text, text [, start_num]) – Returns the position of the search_text string in text. You can also specify a start position to skip part of the target string. FIND() is not zero indexed.

Note – If your start_num isn’t greater than zero, a 0 displays in the field.

FIND('@', Email,1)

The above formula find the first occurance of ‘@’ in the email field and returns the position.

Text Functions – LEFT() , RIGHT() & MID()

LEFT(text, num_chars) – Returns the specified number of characters from the start of a text string

RIGHT(text, num_chars) – Returns the specified number of characters from the end of a text string

MID(text, start_num, num_chars) – Returns character from the middle of a text string, given a starting position and length

//FirstName = 'Shanon'
LEFT(FirstName, 3) //Retuns - Sha
RIGHT(FirstName, 4) //Returns - anon
MID(FirstName, 2,3) //Returns - han

Number Functions – VALUE()

It converts a text string that represents a number to a number

Number Functions – CURRENCYRATE()

It returns the conversion rate to the corporate currency for the given CurrencyIsoCode, or 1.0 if the currency is invalid. It does not support Dated Exchange Rates. Here is the Idea open for that. Idea

TEXT( MCEILING(CURRENCYRATE(“USD”)) * Amount) & ” $”

In the above formula, I used the MCEILING function to round it up to the nearest integer. Used CURRENCYRATE function to convert the amount into USD

Number Functions – MCEILING()

MCEILING(number) – Rounds a number up to the nearest integer, towards zero if negative

Date Functions – ADDMONTHS()

Add the num months to the date, using the last date of the month if date is the last day of the month or adding num months has fewer days.

ADDMONTHS(Birthdate,5)

It adds 5 months to the BirthDate and returns the Date.

Date Functions – YEAR() /MONTH() / DAY()

These formulas can be used to return the year, month, or day from the date and turn into a number.

Date Functions – DATETIMEVALUE() / DATEVALUE()

DATETIMEVALUE(expression) – Returns a year, month, day and GMT time value

DATEVALUE(expression) – Creates a date from its datetime or text representation

Final Thought

In this blog post, I tried to summarize the most commonly used formulas in Salesforce. I will keep on adding contents as needed. 

Very soon, I will be publishing a post which will contain all the crazy formulas you can write in Salesforce. Till then, please subscribe to my blog and youtube channel – technical potpourri.

Disclaimer

This article is not endorsed by Salesforce, Google, or any other company in any way. I shared my knowledge on this topic in this blog post. Please always refer to Official Documentation for the latest information.

0 Comments

Submit a Comment

Your email address will not be published. Required fields are marked *