Formula Fields

You’ve got a lot of data in your organization. Your users need to access and understand this data at-a-glance without doing a bunch of calculations in their heads. Enter formula fields, the powerful tool that gives you control of how your data is displayed.

A formula field in a page layout, a list view, and a report.
When you’re first learning formulas, it’s best to start with simple calculations and build up to more complex scenarios. But, as you’ll see, even simple formulas provide valuable information.

Finding and Using the Formula Editor

You can create custom formula fields on any standard or custom object. To start, we’ll create a formula on the Account object.

Creating a New Formula Field

Follow these steps to navigate to the formula editor:

1. Go to the Accounts page from setup.
2. Create a new field.
3. Select Formula and click Next.
4. In Field Label, type My Formula Field. Notice that Field Name populates automatically.
5. Select the type of data you expect your formula to return. For example, if you want to write a formula that calculates the commission a salesperson receives on a sale, you select Currency. For now, pick Text.
6. Click Next. You’ve arrived at the formula editor! Time for our tour.

Using the Formula Editor

This image highlights the most important parts of the formula editor:

1. The formula editor comes in two flavors: Simple and Advanced. It’s tempting to use the Simple editor, but we always recommend using the Advanced editor. Advanced doesn’t mean more complicated. It means more tools for you to create powerful formulas.
2. The text area is where you enter your formula. When writing formulas, keep in mind that:
• Whitespace doesn’t matter. You can insert as many spaces and line breaks as you want without affecting the formula’s execution.
• Formulas are case sensitive. Pay attention to capitalization of field and object names.
• When working with numbers, the standard order of operations applies.
3. The Insert Field button opens a menu that allows you to select fields to use in your formula. Inserting from this menu automatically generates the correct syntax for accessing fields.
   
4. The Insert Operator button opens a drop-down list of the available mathematical and logical operators.
   
5. The Functions menu is where you view and insert formula functions. Functions are more complicated operations that are pre-implemented by Salesforce. Some functions can be used as-is (for example, the TODAY() function returns the current date), while others require extra pieces of information, called parameters. The LEN(text) function, for instance, finds the length of the text you input as a parameter. The formula LEN("Hello") returns a value of 5.
   
6. Once you’ve written a formula, you can use the Check Syntax button to ensure that everything is in working order before saving. If your formula has issues, the syntax checker alerts you to specific problems.
7. The Quick Tips box links you to formula help documentation. If you’re looking for reference material, examples, or general formula tips, click the links in the box.

Examples of Formula Field

Example 1: Displaying an Account Field on the Contact Detail Page

Record detail pages contain a ton of information, but sometimes it’s not enough. Sometimes you need more! For your first formula, let’s do something simple. Let’s take a single field from an Account and show it on a Contact using what’s called a cross-object formula. Let’s take a look.

First create a Contact. If you’ve never created a Contact before, go to the Contacts tab and click New. Enter any value for Last Name and make sure that you fill in a value for the Account Name field by clicking the lookup icon. Next we’ll create a formula to display the account number on the Contact page:

1. From Setup, navigate to Customize | Contacts | Fields.
2. In Contact Custom Fields & Relationships, click New.
3. For the field type, select Formula and click Next.
4. Call your field Account Number and select Text for the formula return type. Click Next.
5. Click Insert Field on the Advanced Formula Editor. Select Contact | Account | Account Number and then click Insert.
   

Let’s see this formula in action. The next page lets you set field-level security. For now, click Next so we can add our formula field to the page layout. If you haven’t learned about page layouts yet, check out the UI Customization module. For the time being, make sure that all the checkboxes are selected. Click Next and then click Save.

Now it’s time to see what you’ve done. Open the detail page for the Contact object you just created and find your new Account Number formula field. Cool!

Example 2: Displaying the Number of Days Until an Opportunity Closes on a Report

You can also use formula fields in reports to increase the visibility of important information. Say, for example, you wanted a report column that displays the number of days until an opportunity is closed. First, create an Opportunity to test our formula.

If you’ve never created an Opportunity before, go to the Opportunities tab and click New. Fill in any value for the Name, select any Stage, and set a close date that’s at least three days in the future. Then create a custom formula field called Days to Close on the Opportunities object with a Number return type:

1. From Setup, click Customize | Opportunities | Fields.
2. Scroll to the Opportunity Custom Fields & Relationships section and click New.
3. Select the Formula radio button and then click Next.
4. In the Field Label text area, type Days to Close.
5. Select the Number radio button.
6. Click Next to open the formula editor.
   

We need to find the difference between the opportunity close date and today’s date. Let’s start by inserting the Close Date field in the editor. Since we’re finding a difference, use subtraction. Select - Subtract from the Insert Operator menu.

But how do we tell our formula that we need today’s date? Luckily, there’s a function called TODAY() that updates to match the current date. Find it in the Functions menu on the right side of the editor and click Insert Selected Function.

The Days to Close formula.
After you click through the save screens, it’s time to put your new formula field in a report. From the Reports tab, click New Report.... Then select Opportunities and click Create. Your opportunity appears in the Preview panel. Search for Days to Close in the Fields menu on the left side of the page. This field is the formula field you just created. Drag it to the last column in your report. The column populates automatically with the calculated value.

We won’t return to this report, so you can either save it or move directly to the next example.

Debugging Formulas

Syntax errors are an inevitable part of working with formulas. The Check Syntax button in the editor is an important tool for debugging your formulas. The syntax checker tells you what error it encountered and where it’s located in your formula. Here are some common syntax issues:

1. Missing parentheses: This error most often occurs when the number of opening parentheses doesn’t match the number of closing parentheses. It can be particularly difficult to avoid this error if you’re using several functions at once. Try breaking your function into multiple lines so it’s easier to tell which sets of parentheses belong together.
   
2. Incorrect parameter type: If you give a function a number parameter when it expects text (or any other combination of data types), this is the error you’ll see. Always check the help text or the documentation so you know what kind of parameters a function accepts. 
   
3. Incorrect number of parameters for function: If you input too many or too few parameters into a function, the syntax checker alerts you. Again, check the help text or documentation for guidelines on inputting parameters to specific functions.
   
4. Formula result is incompatible with formula return type: You’ll see this error if you select one data type when creating the formula field but write a formula that returns a different data type. In the example below, you can see that My Account Formula expects to return a number (shown in parentheses next to the formula name), but the TODAY() function returns a date. The error tells you what the expected data type is, but you can always reference the documentation beforehand to avoid the error.
   
5. Field does not exist: This error indicates that you’ve included a field in your formula that your object doesn’t support. In this case, check your spelling and capitalization. If you can’t find any mistakes, try inserting the field from the Insert Field menu again to make sure you’re referencing it correctly.
   
6. Unknown function: In this case, check that Salesforce supports the functions you’re using. You’ll also get this error for misspelled functions.