Introduction

A formula field is a read-only field that displays a value that is calculated with reference to the value of another standard or custom field. The icing on the cake? → You can display a great deal of new, untapped information from the existing data in your org!

Examples: You need to know how many days are left until an Opportunity’s close date. You can take two dates and calculate the number of days between the ages from the date of birth.

How to Find the Formula Editor?

Let’s locate the formula field and get to know how it works!

1. You need to open the Object Manager from Setup and thereafter click Account.
2. From the sidebar, open Fields & Relationships, then click New.
3. For the Field Type, choose Formula. Click Next.
4. For Field Label, enter Formula Test, and for Formula Return Type, select Number.
5. Click Next.

You should be on the Formula Editor page now.

How to Use the Formula Editor?

Before getting hands-on, let’s get to know the various parts of the Formula Editor.

1. Simple or Advanced Formula – The formula editor comes in two forms. Advanced doesn’t mean complicated or hard to create, and it simply has more tools for you to create compelling formulas. It’s recommended to choose the Advanced version over the Simple version while creating formulas.

2. Insert Field – This is where you choose the field you’re going to use in your formula. Click Insert Field to launch a menu that includes all the fields available to use in the formula.

3. Insert Operator – This opens up a menu of all available mathematical (+,-), logical (AND, OR) and text (& Concatenate) operators.

4. Functions – Includes all the functions that you can apply to the formula. These functions are pre-implemented by Salesforce and can seem intimidating, but once you’re familiar with how they work, they’re quite simple to use. Some of these functions require parameters to function.

They include Date and Time, Logical, Math, Text, Summary, and Advanced functions.

Example: LEN(text) returns the length of the text you input. LEN(“Good”) returns the value 4.

Some of the frequently used functions include:

• ABS (number) – Returns the absolute value of the number.
  Example: ABS(-10) = 10

• LEN(text) – Returns the length of the characters in a text string.
  Example: LEN(“hello”)=5

• TODAY() – Returns the current date.
• MIN(number, number,….) – Returns the least of all the parameters.
  Example: MIN(10,12,13)=10

• CEILING(number) – Rounds the number to the nearest integer.
  Example: CEILING(3.5) = 3 , CEILING(-3.5)= -4

• ISNULL(expression) – Determines if the expression is null (blank) and returns a TRUE if it is.

5. Text Area – This is where you enter your formula.

Keep these points in mind when creating a formula:

• The formulas are case-sensitive, ensure that the field and object names included in the formula are correct.
• White space is ignored. The whitespaces and line breaks do not affect the execution of the formula.
• Order of operations rule (BODMAS) applies when working with numbers.

6. Check Syntax – Once you’re done with creating the formula, click on this button to ensure that the formula follows the correct syntax. In case an error is present, the syntax checker alerts you.

Well, let’s try implementing a couple of examples to get acquainted with the Formula Editor. You can click Cancel and close the Formula Editor, as we won’t be needing this.

How to Display an Account Field on the Contact Detail Page?

Let’s start with something straightforward. We want to display the Account ID on the Contact detail page. This is called a cross-object formula since we’re taking a field from an Account and showing it on a Contact.

1. From Setup, open Object Manager, and click Contact.

2. From the sidebar, select Fields & Relationships.

3. Click New.

4. For Field Type, select Formula and then click Next.

5. For the Field Label, enter an Account ID. And for Formula Return Type, select Text. And click the Next button.

6. Now, in the Advanced Formula Editor, you need to click Insert Field.

7. Now you need to Select Contact → Account → Account ID. Click Insert.

8. Click Check Syntax

9. Click Next if no syntax errors are present. It should look like this.

10. Click Next and then Save.

Let’s see the changes we’ve made, open the detail page for any Contact record, and voila! You should find the formula field Account ID on the page.

How to Display the Number of Days Until an Opportunity Closes on a Report?

Let’s create a formula field for the Opportunity details page that is going to display the number of days left for an Opportunity to close.

1. From Setup, open Object Manager, and click Opportunity.

2. From the sidebar, select Fields & Relationships.

3. Click New.

4. For Field Type, select Formula and then click Next.

5. For Field Label, enter Days Before Close. And for Formula Return Type, select Number.

6. Click Next.

7. Select Advanced Formula.

8. To find the number of days before close, we need to subtract today’s date from the close date.

9. From Insert Field, choose Opportunity → Close Date. Click Insert.

• From Operators, select – Subtract.
• From the Functions menu on the right side, select TODAY, and then click Insert Selected Function.

10. Click Check Syntax. If no syntax errors are present. Click Next, Next, and Save.

That’s it. To see what we’ve created in action on a Report:

• Create an Opportunity Record with the close date a couple of days from today.
• Create an Opportunity Report, and add the column Days Before Close to the Report.

It should look like this:

How to Find Distinct Objects Using the Power of One?

The Power of One is a hack all Salesforce Admins need in their bag!

For example, consider a situation where you have 5 Accounts with 10 Opportunities each, and the Report you generate states that the total record count is equal to 50 Accounts. This can be quite misleading. We want the Report to state that there are 5 unique Accounts and not 50 Accounts. We can solve this issue with the Power of One.

The Power of One is a formula that’s going to change everything! It’s quite complex but easy to implement. Let’s understand this concept with the help of an example!

1. From Setup, open Object Manager, and then select Account.

2. Open Fields & Relationships form the sidebar.

3. Click New.

4. For Field Type, select Formula. Click Next.

5. For Field Label, enter Unique Accounts.

6. For Formula Return Type, select Number. And select 0 for Decimal Places. Click Next.

7. In the Formula Editor, enter the number 1.

8. Click Check Syntax.

9. Click Next, Next, and Save.

That’s it! Let’s see how this works in a Report.

• Create an Opportunity Report.
• Add the Column Unique Accounts to the Report.
• On the Unique Accounts Column, click on the dropdown menu and select Summarize and Sum.
• The number of distinct Accounts should appear at the bottom.

It should be similar to this:

What is Debug Formulas?

The Check Syntax button in the Formula Editor checks your formula for errors and if any are present, it tells you what error is encountered and where it’s located in your formula. Syntax Errors are unavoidable when working with formulas and are perfectly okay to come across, but what do these errors mean and how do we debug them? Let’s look at some common syntax errors we may face:

Missing parenthesis

This error is encountered when the number of opening parentheses is not equal to the number of closing parentheses.

Solution: ROUND(10.2344,3)

You can also encounter this error if a comma is missing between two function parameters. Check if the commas in your function are correct as well when you encounter this error.

Solution: ROUND (10.2344 , 3)

Incorrect number of parameters

This is encountered when you give a function the incorrect number of parameters. It could be too many or too few parameters than what is expected.

Solution: ROUND (10.2344 , 3)

Incorrect parameter type

You’ll see this error if you give a function a number parameter when it’s expecting a text parameter ( or any other combination of data types).

Solution: LEN(“1234”)

Field does not exist

This occurs when you enter a field in your formula that isn’t supported by your Object. Always check the spelling and capitalization of fields, and it’s best practice to use the Insert Field menu to make sure everything is correct.

Solution: LEN(Name)

This error can also occur if you forget the quotation marks around a hyperlink or a text literal.

Solution: LEN(“Hello”)

Unknown function

This error occurs when you use a function that is not supported by Salesforce. You may also get this error if you misspell the function.

Solution: LEN(Name)

The formula result is incompatible with the expected data type.

This error occurs when the return type you chose for your formula field is incompatible with the return type of the formula you wrote in the text area.

Solution: Write a Formula that has the return type as the Formula Field ( Number). Or click Previous and change the Formula Return Type to Text.

Further Examples

Let’s create a couple more formulas.

HYPERLINK( )

This formula is perfect if you want to create a hyperlink to an external website.

Example:

ROUND()

Use the following formula if you want to apply a discount.

Let’s say we’re applying a 10% discount to an Opportunity Amount, and then we want to round the resulting amount to exactly two decimal places using the round() function.

AND()

Let’s create a checkbox formula, Big Opportunity. It checks if an Opportunity has more than 10 Destinations AND the Opportunity Amount is more than INR 10,000. If both these statements are true, the field would appear as a checked box. Otherwise, it would appear as a blank box.

Image Source