Using Formula Fields in Salesforce

Know How
Written By Abhinav Gupta

Introduction

Formula fields are custom fields that automatically provide results based on records and related records. They are a valuable and powerful tool provided by Salesforce to the Admins as they are updated automatically in real-time whenever a record is accessed.

They are read-only fields and are calculated based on fields and expressions specified in the formula and the values present in those fields. It calculates the latest data when it is viewed or when any of the source fields changes. More interestingly, they can also be used in Reports or SOQL queries.

A formula field expression is composed of -

  • Fields of Records

  • Fields of Related Records

  • Formula Operators

  • Formula Functions

There are a few points to consider when we are creating formula fields like -

  • A formula field expression can contain only 3900 characters, including Spaces, return Characters & Comments.

  • You can’t delete a field that is being referenced by the formula field expression.

  • Long Text Area, Rich Text Area, Multi-Select Picklist and Encrypted type fields cannot be referenced in a formula field expression.

  • A field value cannot depend on another formula field that references it.

When creating a formula field, you have to specify the return type of the field, which is the type of value that a specific formula field will hold or, in other words, the type of the value that will be returned by the expression specified in the formula field. Following return, types are allowed for a formula field.

  • Checkbox

  • Currency

  • Date

  • Time

  • Datetime

  • Number

  • Percent

Creating a Formula Field

Now, let’s see how to create a formula field for an object. To explain the steps, let’s take a scenario to create a formula field on the Account object, named Annual Tax, to calculate Annual tax as 10% of the Annual Revenue.

To create a formula field for a specific object- 

  1. Go to the “Fields & Relationships” section of that specific object and click on the “New” button.

2. Choose “Formula” as Data Type and click on “Next”.

3. Choose “Formula Return Type”, provide a “Field Label” and Click on the “Next” button.

(Like for current scenario we have chosen “Currency” as Return Type and “Annual Tax” as the field label)

4. Click on the “Simple Formula” tab, and make sure the “Select Field Type” combobox should point to the “Account” Object and click on the “Annual Revenue” field from the “Insert Field” combobox. [As required for current scenario]

5. Write the given formula to calculate the Annual Tax by using ‘(’, ‘*’, and ‘/’ operators from the Insert Operator combobox.

( AnnualRevenue * 10 ) / 100

6. Click on the “Check Syntax” button to verify the expression and click on the “Next” button.

7. Choose the profile for which you want to make this field visible, and click on the “Next” button and click on the “Save” button.

Now when you see records of the Account Object, you can see the “Account Tax” is calculated as 10% of the Annual Revenue.

Simple Formula vs Advanced Formula

You might have noticed the “Simple Formula” and “Advanced Formula” tab while creating the formula. Our current requirement is fulfilled by the “Simple Formula” itself, but you can use “Advanced Formula” as well. 

Now, let's see what’s the difference between “Simple Formula” and “Advanced Formula”.

  • In Simple Formula, you can only use fields from the Current Object or Universal Objects like “User” and “Organization”. In contrast, in Advance Formula, you can use fields from Current Object and Related Objects (Parent) along with many Universal Objects.

  • Formula Functions are not available in Simple Formula, but they are available in Advanced Formula.

  • Logical Operators are not available in Simple Formula, but they are available in Advanced Formula.

Now, let us see the various Formula Operators and Formula Functions that we can use in Formula Fields.

Formula Operators in Formula Fields

Following types of Formula Operators can be used in Formula Fields, and as discussed earlier, Simple Formula can only use Math Operators.

  • Math Operators - These operators are used to perform mathematical operations.

Operators Description Example Result
Add (+) To perform summation 25 + 5 30
Subtract (-) To perform difference 25 – 5 20
Multiply (*) To perform product 25 * 5 125
Divide (/) To perform division for the quotient 25 / 2 12.5
Exponential (^) To perform the exponential operation 25 ^ 2 635
Parenthesis ‘(’ or ‘)’ To override the order of operation (3+2) / 2 2.5

  • Logical Operators - These operators are used to perform comparison operations. All these operations either result in ‘True’ or ‘False’.

Operators Description Example Result
Equal (= and ==) It returns true when two values are equal. 25 = = 25 TRUE
Not Equal (!= and <>) It returns true when two values are not equal. 25 <> 25 FALSE
Less Than (<) It returns true when the value on the left side is less than the value on the right side. 25 < 25 FALSE
Greater Than (>) It returns true when the value on the left side is greater than the value on the right side. 25 > 25 FALSE
Less Than or Equal(<=) It returns true when the value on the left side is less than or equal to the value on the right side. 25 <= 25 TRUE
Greater Than or Equal(>=) It returns true when the value on the left side is greater than or equal to the value on the right side. 25  >= 25 TRUE
AND (&&) It compares to Boolean Values (True or False) or expressions returning Boolean Values and returns true when both of the values are true otherwise false. T && T
T && F
F && T
F && F		 True
False
False
False
OR (||) It compares to Boolean Values (True or False) or expressions returning Boolean Values and returns false when both of the values are false otherwise true. T || T
T || F
F || T
F || F		 True
True
True
False

  • Text Operator – This operator is used to concatenate texts.

    Concatenate [&] – For example ‘Hello’ & ’ World’ will result into ‘Hello World

    Formula Functions in Formula Fields

Date & Time Text Math Logical Advanced
ADD MONTHS BEGINS ABS AND CURRENCYRATE
DATE BR CEILING BLANK VALUE —
DATE TIME VALUE CASE SAFE ID DISTANCE CASE —
DATE VALUE CONTAINS EXP IF —
DAY FIND FLOOR IS BLANK —
HOUR GET SESSION ID LN IS NULL —
MILLI SECOND HYPERLINK LOG IS NUMBER —
MINUTE IMAGE MAX NOT —
MONTH INCLUDES MCEILING NULL VALUE —
NOW ISPICKVAL MFLOOR OR —
SECOND LEFT MIN — —
TIME NOW LEN MOD — —
TIME VALUE LOWER ROUND — —
TODAY LPAD SQRT — —
WEEKDAY MID — — —
YEAR RIGHT — — —
— RPAD — — —
— SUBSTITUTE — — —
— TEXT — — —
— TRIM — — —
— UPPER — — —
— VALUE — — —

We hope you enjoyed this blog! In the next one, we’ll be talking about the formula functions and their use cases and scenarios. until then you can take a dive into our other Salesforce blogs, See ya soon!! 🙂

Resources

  • https://help.salesforce.com/s/articleView?id=sf.choosing_a_formula_data_type.htm&type=5

  • https://help.salesforce.com/s/articleView?id=sf.elements_of_a_formula.htm&type=5

  • https://help.salesforce.com/s/articleView?id=sf.tips_on_building_formulas.htm&type=5

  • https://help.salesforce.com/s/articleView?id=sf.formula_field_limits.htm&type=5

  • https://help.salesforce.com/s/articleView?id=sf.customize_functions.htm&type=5

Suggested Read

Abhinav Gupta

First Indian Salesforce MVP, rewarded Eight times in a row, has been blogging about Salesforce, Cloud, AI, & Web3 since 2011. Founded 1st Salesforce Dreamin event in India, called “Jaipur Dev Fest”. A seasoned speaker at Dreamforce, Dreamin events, & local meets. Author of many popular GitHub repos featured in official Salesforce blogs, newsletters, and books.

https://abhinav.fyi
Previous

Don't Miss Out The Most Popular 2021 Salesforce Automation Sessions
Next

Google Analytics and Salesforce Marketing Cloud Integration