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 my previous post, I have explained some of the most important formulas. So if you haven't read that blog post, you can read it below -
The form you have selected does not exist.
Now today, I am going to share some of the advanced formulas which you might need to write during your implementation. I will always keep on updating this blog post as I encounter any such advanced formulas.
Use Case 1 - Linkable Phone Number Field
Creates a phone number field that is linkable and, when clicked, calls the provided number automatically. Here, "servername" and "call" should be changed to the name of your dialing tool and the dialing command that it uses. The identification for the contact, lead, or account record is inserted in the merging field {!Id}. The dialing tool receives its phone number from the first {!Phone} merge field, and when the user clicks on the Phone field value in the last {!Phone} merge field, it becomes clickable text that can be used to dial.
HYPERLINK("http://servername/call?id=" &
{!Id} & "&phone=" & {!Phone}, {!Phone})Use Case 2 - Round Robin Lead Assignment
Using a straightforward round-robin method, evenly distributes leads to a pool of consumers. The Lead Number field in this scenario is a custom autonumber field with a display format of {000000}. The MOD function is used to it to generate a number that alternates between 1 and the number of people in the round robin pool, which in this case is 3. You need to construct lead assignment rules based on this formula field after it has been created. You indicate the user or queue assignment for each of the formula field's values (1, 2, or 3 in this example).
MOD( VALUE({!Lead_Number__c} ), 3) + 1Use Case 3 - Lead Age (Open Lead)
Verifies whether a lead is open and, if it is, determines how many days it has been open by deducting the creation date and time from the current date and time. The number of days open, rounded to two decimal places, is the outcome. This field is empty if the lead is not open.
IF(ISPICKVAL({!Status} , "Open"),
ROUND(NOW()-{!CreatedDate}, 0), NULL)Use Case 4 - Consider Prev Value
This is mainly used in the validation rule. Consider when the opportunity is closed (won/loss), your business requirement is that changing the amount is not allowed and if anyone want to do that, you want to throw the error message. In this scenario, your validation rule will look like -
AND(IsClosed, PRIORVALUE(Amount) <> Amount)Use Case 5 - Lead Score
This formula can check the completeness of the lead i.e. whether some important lead attributes are having values or not, and then based on it it calculates the score.
But you can definitely optimize the below formula as per your business logic, like when the lead's title is CEO, then add more values whereas when the lead's title is DIRECTOR, then comparatively less value.
If(ISBLANK(Title),0,5)+
If(ISBLANK(Street),0,5)+
If(ISBLANK(Email),0,5)+
If(ISBLANK(Phone),0,5)+
If(ISBLANK(Website),0,5)Use Case 6 - Dipslay Progress Bar
I have written a blog post where Salesforce Formula and static resources can be used to display the progress bar. This is very powerful as no coding is needed and can be used in any standard or custom object. You can read the blog post below -
Use Case 7 - Case Categorization
Displays a text value in RED, YELLOW, or GREEN based on the custom field's case age. In a summary report, this formula could be used for grouping.
IF( {!Case_Age__c} > 20, "RED", IF({!Case_
Age__c} > 10, "YELLOW", "GREEN"))Use Case 8 - Contract Aging
This formula calculates the number of days since a contract with an account was activated. If the contract Status isn’t “Activated,” this field is blank.
IF(ISPICKVAL(Contract_Status__c, "Activated"),
NOW() - Contract_Activated_Date__c, null)Use Case 9 - Contact's LinkedIn Profile
This formula displays contact's LinkedIn profile.
https://www.linkedin.com/search/fpsearch?type=people&keywords
={!Contact.FirstName}+{!Contact.LastName} Use Case 10 - Unformatted Phone Number
This formula removes the parentheses and dash characters from North American phone numbers. This formula is necessary for some auto-dialer software.
IF(Country_Code__c = "1", MID( Phone ,2, 3) & MID(Phone,7,3) & MID(Phone,11,4), Phone)Use Case 11 - Contact's Preferred Phone
This formula displays the contact’s preferred contact method in a contact related list—work phone, home phone, or mobile phone—based on a selected option in a Preferred Phone custom picklist.
CASE(Preferred_Phone__c,
"Work", "w. " & Phone,
"Home", "h. " & HomePhone,
"Mobile", "m. " & MobilePhone,
"No Preferred Phone")Use Case 12 - Add Business Days
This formula finds three business days from a given date. This formula finds the day of the week of the date field value. If the date is a Wednesday, Thursday, or Friday, the formula adds five calendar days, two weekend days, three weekdays, to the date to account for the weekend. If date is a Saturday, you need four additional calendar days. For any other day of the week Sunday Tuesday, simply add three days. You can easily modify this formula to add more or fewer business days. The tip for getting the day of the week is useful to use to adjust this formula.
CASE(
MOD( date - DATE( 1900, 1, 7 ), 7 ),
3, date + 2 + 3,
4, date + 2 + 3,
5, date + 2 + 3,
6, date + 1 + 3,
date + 3
)Use Case 13 - Find Difference Between Two Date/Times
To find the difference between two Date values as a number, subtract one from the other like so: date_1 — date_2 to return the difference in days.
Finding the elapsed time between two Date/Time values is slightly more complex. This formula converts the difference between two Date/Time values, datetime_1 and datetime_2, to days, hours, and minutes.
IF(
datetime_1 - datetime_2 > 0 ,
TEXT( FLOOR( datetime_1 - datetime_2 ) ) & " days "
& TEXT( FLOOR( MOD( (datetime_1 - datetime_2 ) * 24, 24 ) ) ) & " hours "
& TEXT( ROUND( MOD( (datetime_1 - datetime_2 ) * 24 * 60, 60 ), 0 ) ) & " minutes",
""
)Use Case 14 - Find Business Hours Between Two Date/Times
The formula to find business hours between two Date/Time values expands on the formula to find elapsed business days. It works on the same principle of using a reference Date/Time. In this case 1/8/1900 at 16:00 GMT, or 9:00 AM PDT, and then finding your Dates’ respective distances from that reference. The formula rounds the value it finds to the nearest hour and assumes an 8–hour, 9:00 AM5:00 PM work day.
ROUND( 8 * (
( 5 * FLOOR( ( NOW() - DATETIMEVALUE( '1900-01-08 16:00:00') ) / 7) +
MIN(5,
FLOOR ( MOD ( NOW() - DATETIMEVALUE( '1900-01-08 16:00:00'), 7) / 1) +
MIN( 1, 24 / 8 * ( MOD( NOW () - DATETIMEVALUE( '1900-01-08 16:00:00' ), 1 ) ) )
)
)
-
( 5 * FLOOR( ( MQL_datetime_c - DATETIMEVALUE( '1900-01-08 16:00:00') ) / 7) +
MIN( 5,
FLOOR( MOD( MQL_datetime_c - DATETIMEVALUE( '1900-01-08 16:00:00'), 7) / 1) +
MIN( 1, 24 / 8 * ( MOD( MQL_datetime_c - DATETIMEVALUE( '1900-01-08 16:00:00' ), 1) ) )
)
)
),
2 )Use Case 15 - Horizontal Bar To Indicate Scoring
This formula displays a horizontal color bar (green on a white background) of a length that is proportional to a numeric score. In this example, the maximum length of the bar is 200 pixels.
IMAGE("/img/samples/color_green.gif", "green", 15, Industry_Score__c * 2) &
IMAGE("/s.gif", "white", 15,
200 - (Industry_Score__c * 2))Use Case 16 - Case Priority Flags
This formula displays a green, yellow, or red flag image to indicate case priority.
IMAGE(
CASE( Priority,
"Low", "/img/samples/flag_green.gif",
"Medium", "/img/samples/flag_yellow.gif",
"High", "/img/samples/flag_red.gif",
"/s.gif"),
"Priority Flag")Use Case 17 - Expected Product Revenue
This formula calculates total revenue from multiple products, each with a different probability of closing.
ProductA_probability__c * ProductA_revenue__c + ProductB_probability__c * ProductB_revenue__cUse Case 18 - Tiered Commission Rates
This formula calculates the 2% commission amount of an opportunity that has a probability of 100%. All other opportunities have a commission value of zero.
IF(Probability = 1,
ROUND(Amount * 0.02, 2),
0)Use Case 19 - Opportunity Discount Rounded
Use this formula to calculate the discounted amount of an opportunity rounded off to two digits. This example is a number formula field on opportunities that uses a custom percent field called Discount Percent.
ROUND(Amount-Amount* Discount_Percent__c,2)Use Case 20 - Opportunity Discount With Approval
This formula adds a “Discount Approved” checkbox to an opportunity. It uses conditional logic to check the value of the approval flag before calculating the commission.
IF(Discount_Approved__c, ROUND(Amount – Amount * DiscountPercent__c, 2), Amount)Final Thought
In this blog post, I tried to list down the most commonly used formulas in Salesforce. I will keep on adding contents as needed.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