Go-To Formula Functions from the Awesome Admin Community

Salesforce Admins have been known to generously share their knowledge with other Trailblazers. In conjunction with the Admin Best Practices: Building Useful Formulas Trailhead LIVE session, we thought it would be fun to interact with admins and find out what their go-to formula function is, why it’s their favorite, and to get an example or two of it in action.

I posed this question to the Trailblazer Community and LinkedIn: “#AwesomeAdmins, what is a common formula function you rely on time and time again… that other admins should add to their formula toolkit?”

Here are a few of the great responses.

Formula functions, what’s your function?

Formula functions is a fancy term for more complicated operations that are pre-implemented by Salesforce. Formula functions appear in the Functions menu (example below) where you can add formula references.

Salesforce MVP Steve Molis — now who DOESN’T owe him a beer for his formula help? I know I do! — responds that his go-to formula function is TEXT as it “…allows you to convert a picklist value to a text string, which then allows you to ‘Nest’ other functions like LEFT, RIGHT, MID, FIND, CONTAINS, BEGINS, VALUE, etc… just like it’s a Text field.”

Here are two example of Steve’s TEXT function in action:

AND(
Text( StageName ) = "Closed - Lost",
ISBLANK(TEXT (Loss_Reason__c ))
)

Note: The Loss_Reason__c is a picklist field.

Amount* ( Value( Text (Discounted_Pct__c))/100)

Note: The Discount_Pct__c is a picklist value converted to a value to do math.

Dave Gordon notes his go-to is the CASE function with the three character prefix of the record ID.

Here is Dave’s formula function CASE with the three character prefix in action:

CASE( LEFT(WhatId, 3), "001", "Account","a0M", "Consultant", "a0B", "Partner","Other" )

Note: This example formula is used on the Task object. Each of the prefixes above returns the name of the linked object, and if one isn’t called out specifically, it will be displayed as “Other”.

Community member Himanshu Hunge notes that the REGEX expression is his fav: “It’s a simplified way to compare text.”

Here are a few examples of the REGEX function in action in Himanshu’s org:

SN format validation check: NOT(REGEX( SSN__c , "[0-9]{3}-[0-5]{2}-[0-5]{4}"))

State code validation check: NOT( REGEX(StateCode_c, "[a-z]{2}-[0-9]{3})")

An example using a combination of ISBLANK and REGEX:

AND( ISBLANK(Email), NOT(REGEX(Email ,('[a-zA-Z0-9_.+-]+@[a-zA-Z0-9-]+.[a-zA-Z0-9-.]+')))

Shubham Sonar shares his favorite function, which is ISBLANK. He says it “…helps [to] check if a single [field] is not empty before moving to a different stage or [to] check if a field is empty before actually evaluating a complex formula.”

Here’s the ISBLANK function in action:

AND(
 StageName = ‘Paid’,
 OR (
  ISBLANK( Invoice_Amount__c ),
  ISBLANK( Invoice_Date__c ),
  ISBLANK( Payment_Transaction_Id__c )
 )
)

Skot Nelson provides not one but TWO go-to formula functions: ISCHANGED and PRIORVALUE. Way to go, Skot!

Here is Skot’s example formula using both functions that checks for when an opportunity leaves the “Incubation” (New) Stage:

ISCHANGED(StageName) && ISPICKVAL(PRIORVALUE(StageName), 'Incubation')

Speciality formulas

Other admins provided their favorite formulas. While these aren’t true formula functions, they are great formula examples that admins should add to their go-to collection.

Trailblazer Brenden Burkinshaw responds that his go-to is $CustomMetadata, as it prevents the “…hard coding of record type ids, profiles ids, role ids, etc.” Basically, using this avoids hard-coding all the things.

Brendan puts this to use in the example formula below:

AND(
Active_Contract__c = TRUE,
NOT(OR(
CONTAINS($CustomMetadata.ID_Validation_Exclusion__mdt.Location_Associated_with_Active_Contract.Role_ID__
c , $UserRole.Id),
CONTAINS($CustomMetadata.ID_Validation_Exclusion__mdt.Location_Associated_with_Active_Contract.User_ID__
c , $User.Id),
CONTAINS($CustomMetadata.ID_Validation_Exclusion__mdt.Location_Associated_with_Active_Contract.Profile_ID
__c , $User.ProfileId))))

Salesforce MVP Eric Smith recommends not hard-coding links to Salesforce pages and records. Instead, he offers this handy formula for “…your current instance [that] makes it easy to move your custom fields and flows between sandboxes, community, and production orgs.”

LEFT({!$Api.Partner_Server_URL_340},FIND("/services", {!$Api.Partner_Server_URL_340})) & "<myLocation>"
Make "<myLocation>" "s/" for a Community Home Page, {!Account.Id} for a record page, etc.

Admins, please avoid hard-coding references in your org.

Hamza Abib shares that his go-to formula function is what he calls Access Level. It’s a single formula on an object. He uses it to “…create a single list view that filters the records based on the logged in user’s attributes on their user record. This is particularly useful in a larger organization that has a complex role hierarchy/record access requirements and you don’t want to create multiple list views that is showing the same list of records.”

Here’s a simple version of this formula:

IF( $User.Id = OwnerId, 1, IF($User.Id = Owner.Manager.Id, 2, IF( $User.Department = "Compliance", 3, 0)))

Salesforce MVP Michelle Hansen’s go-to formula function is the power of one. She says, “It’s the first field that I create because of its amazing flexibility in reporting.” Use it in your report to add records.

This is the simplest of formulas. Just add “1” in the Formula field. That’s it!

Formulas are an admin’s superpower. You can use them across the Salesforce Platform in custom fields, validation rules, and reports as well as in your automation. It was fun getting our expert admins to share their go-to formula functions. Seeing them in action was an added bonus!

My three go-to formula functions and examples

I couldn’t leave you without go-to formulas of my own! Check them out below.

1. CASE function

SteveMo taught me this trusty formula function. I like it because it allows me to write cleaner, more efficient formulas.

Example: If the opportunity stage is Qualification or Prospecting, assign a 1 value. Otherwise, assign a 0 value. The assigned value needs to equal 1 to be true.

CASE (StageName,
‘Qualification’, 1,
‘Prospecting’,1,
0)=1

In the example above, where you reference several values of the same picklist field, you do not need to repeat the syntax for each value: ISPICKVAL (picklist_field, text _literal1) || ISPICKVAL (picklist_field, text _literal2). This makes for a simpler looking formula.

2. IF function

This allows you to perform a logical and, if true, use the true value; otherwise, use the false value. You can have nested IF statements for multiple conditions.

In this example, I’m checking for two conditions. First, is the Best Method of Contact equals Phone and the Phone Number field blank? True or false? If it’s false, then determine if the Best Method of Contact equals Email and the Email field is blank. True or False?

IF(
      TEXT(Best_Method_of_Contact__c)= "Phone" &&
      ISBLANK(Phone__c),
true,
IF(
      TEXT(Best_Method_of_Contact__c)="Email" &&
      ISBLANK(Email__c),
true, false)
)

3. ISCHANGED function

This is handy when you only want the formula to fire when the field has changed and not every time.

This example checks if the Stage has changed, if the prior stage value is Closed Won, and if the user does not have the custom permission Update Stage Closed Won.

ISCHANGED(StageName) &&
TEXT(PRIORVALUE(StageName))="Closed Won" &&
NOT($Permission.Update_Stage_Closed_Won)

Now, let’s hear from you! If you have a powerful formula or tip, be sure to share it on Twitter using #AwesomeAdmin, and start a conversation on the Admin Trailblazer Community.

Resources

• Trailhead: Formulas and Validations
• Trailhead: Advanced Formulas
• Trailhead: Improve Data Quality for a Recruiting App
• Trailblazer Community: Formulas — Help, Tips, and Tricks 
• Salesforce Help: Formula Operators and Functions
• Admin Blog: Formula Advice From the Awesome Admin Community
• Trailhead LIVE: Admin Best Practices: Building Useful Formulas