Table of Contents

## 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!

- You need to open the
**Object Manager f**rom**Setup**and thereafter click**Account**. - From the sidebar, open
**Fields & Relationships**, then click**New**. - For the
**Field Type,**choose**Formula**. Click**Next**. - For
**Field Label**, enter**Formula Test,**and for**Formula Return Type,**select**Number**. - 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.

## 0 Comments