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.
Let’s say you wanted to take two numeric fields on a record and divide them to create a percentage. Or perhaps you want to turn a field into a clickable hyperlink for easy access to important information from a record’s page layout. Maybe you want to take two dates and calculate the number of days between them. All these things and more are possible using formula fields.
Let’s look at a specific example. What if you wanted to calculate how many days are left until an opportunity’s close date. You can create a simple formula field that automatically calculates that value. By adding the value to the Opportunity page layout, your users can quickly access this key information. You can also add this field to reports and list views for instant access.
Before we dive into writing formulas, let’s locate the formula editor and get to know its features.
You can create custom formula fields on any standard or custom object. To start, we’ll create a formula on the Opportunity object. Follow these steps to navigate to the formula editor:
- From Setup, open the Object Manager and click Opportunity.
- In the left sidebar, click Fields & Relationships.
- Click New.
- Select Formula and click Next.
- In Field Label, type My Formula Field. Notice that Field Name populates automatically.
- 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.
- Click Next. You’ve arrived at the formula editor! Time for our tour.
- 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.
- 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.
- The Insert Operator button opens a drop-down list of the available mathematical and logical operators.
- 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.
- 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.
- 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.
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:
- From Setup, open the Object Manager and click Contact.
- In the left sidebar click Fields & Relationships.
- Click New.
- For the field type, select Formula and click Next.
- Call your field Account Number and select Text for the formula return type. Click Next.
- Click Insert Field on the Advanced Formula Editor. Select 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. 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:
- From Setup, open the Object Manager and click Opportunity.
- In the left sidebar click Fields & Relationships.
- Click New.
- Select the Formula and then click Next.
- In the Field Label text area, type Days to Close.
- Select the Number radio button.
- 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.
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.
Example 3: Finding Distinct Objects Using the Power of One
Organizations often want to count the number of unique objects in a report with hundreds of records. Say, for example, you have a hundred opportunities listed in a report, but only a handful of users own all these opportunities. How do you find the number of distinct users? This task sounds difficult, but it’s one of the easiest formulas you can write. It’s called the Power of One.
To write this formula, create a custom formula field on the User object. Name it Unique Users, give it a Number return type, and select 0 from the Decimal Places drop-down list. Click Next to open the formula editor. For this formula, you don’t need to insert any fields, operators, or functions. Instead, enter the number 1.
Yes, that’s really it! Save your formula as usual, and then click the Reports tab to see it in action. Create an Opportunities report and drag your Unique Users field onto the report from the left panel. You should see something similar to the following:
Here, we have five opportunities between two distinct users. Where does our formula come in? On the Unique Userscolumn, click the drop-down menu and select Summarize this Field.
In the popup dialog, select Sum and click Apply. The number of distinct users appears at the bottom of the column. As you can see, even simple formulas provide powerful insights into your organization.
Note: You can use the Power of One on any object. For example, if you had a report with 10 accounts, each with three opportunities, your Opportunities report returns 30 records. Adding the Power of One formula field to Account allows you to see the number of distinct accounts represented in the records. Some pros say they add a Power of One field to every object in their org!
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:
- 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.
You’ll also see this error if you forget a comma between two function parameters. This error is confusing because the actual problem doesn’t match up with the syntax checker. If you’re certain your parentheses are correct, double check that the commas in your function are correct as well.
- 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.
- 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.
- 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.
- 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.
Another reason you see this error is if you forget to put quotation marks around a text literal or a hyperlink.
- Unknown function: In this case, check that Salesforce supports the functions you’re using. You’ll also get this error for misspelled functions.
Further Examples
Let’s look at a few more examples. You can create these formulas yourself or simply read through.
- This formula creates a hyperlink to an external website using the HYPERLINK() function. Adding hyperlinks to page layouts helps your users access important information quickly from the detail pages.
- If you want to apply a discount to an opportunity amount, you can use the following formula. In this case, we’re applying a 12% discount and then rounding the result to two decimal places using the ROUND() function.
- This formula is a checkbox formula that determines whether a particular opportunity is a “big” opportunity. It checks whether the number of employees at the opportunity account’s associated company is greater than 1,000 AND whether the opportunity amount is greater than $10,000. If both statements are true, the field appears as a checked box on the Opportunity page layout. Otherwise, it appears as a blank box.
Resources
- Formulas Help & Training
- Formulas Quick Reference
- Formula Help on the Developer Forums
- Formula Ninjas Dreamforce Presentation
Roll-up Summary
While formula fields calculate values using fields within a single record, roll-up summary fields calculate values from a set of related records, such as those in a related list. You can create roll-up summary fields that automatically display a value on a master record based on the values of records in a detail record. These detail records must be directly related to the master through a master-detail relationship.
You can perform different types of calculations with roll-up summary fields. You can count the number of detail records related to a master record, or calculate the sum, minimum value, or maximum value of a field in the detail records. For example, you might want:
- A custom account field that calculates the total of all related pending opportunities.
- A custom order field that sums the unit prices of products that contain a description you specify.
Master-detail relationships closely link objects together so that the master record controls specific behaviors of the detail and subdetail record.
You define a roll-up summary field on the object that is on the master side of a master-detail relationship. For example, you can create a roll-up summary field on the Account object, summarizing related opportunities:
There are a few different types of summaries you can use.
Type Description
COUNT Totals the number of related records.
SUM Totals the values in the field you select in the Field to Aggregate option. Only number, currency, and percent fields are available.
MIN Displays the lowest value of the field you select in the Field to Aggregate option for all directly related records. Only number, currency, percent, date, and date/time fields are available.
MAX Displays the highest value of the field you select in the Field to Aggregate option for all directly related records. Only number, currency, percent, date, and date/time fields are available.
To create a roll-up summary field,
- From Setup, open Object Manager and click Account.
- On the left sidebar, click Fields & Relationships.
- Click New.
- Choose the Roll-Up Summary field type, and click Next.
- For Field Label, enter Sum of Opportunities and click Next.
- The Summarized Object is the detail object that you want to summarize. Choose Opportunities.
- Choose the SUM summary type and choose Amount as the Field to Aggregate.
- Click Next, Next, and Save.
Here are more examples of detail data rolling-up to master records.
Date Opportunity First Created
A roll-up field was created on the Accounts object. Created Date is summarized on the Opportunities object to find the earliest date an opportunity was created related to an account.
Total Price of All Products Related to An Opportunity
A roll-up field was created on the Opportunities object. Total Price is summarized on the Opportunity Product object to find the grand total of all products related to an opportunity.
Minimum List Price of An Opportunity
A roll-up field was created on the Opportunities object. List Price is summarized on the Opportunity Product object to find the product with the lowest price related to an opportunity.
Validation Rules
Validation rules verify that data entered by users in records meet the standards you specify before they can save it. A validation rule can contain a formula or expression that evaluates the data in one or more fields and returns a value of “True” or “False.” Validation rules can also include error messages to display to users when they enter invalid values based on specified criteria. Using these rules effectively contributes to quality data. For example, you can ensure that all phone number fields contain a specified format or that discounts applied to certain products never exceed a defined percentage.
You can create validation rules for objects, fields, campaign members, or case milestones. In these steps, we’ll create a validation rule that fires when a user tries to save an account with an account number of incorrect length.
Creating a Validation Rule
- From Setup, go to Object Manager and click Account.
- In the left sidebar, click Validation Rules.
- Click New.
- Enter the following properties for your validation rule:
- Rule Name: Account_Number_8_Characters
- Error Condition Formula:
LEN( AccountNumber) !=8
- Error Message: Account number must be 8 characters long.
- To check your formula for errors, click Check Syntax.
- Click Save to finish.
Here are some validation rule examples that you can try out yourself:
Account Number Is Numeric
| Field | Value | ||||||||
|---|---|---|---|---|---|---|---|---|---|
| Description: | Validates that the Account Number is numeric if not blank. | ||||||||
| Formula: |
| ||||||||
| Error Message: | Account Number is not numeric. | ||||||||
| Error Location: | Account Number |
Date Must Be in the Current Year
| Field | Value |
|---|---|
| Description: | Validates that a custom date field contains a date within the current year. |
| Formula: | YEAR( My_Date__c ) <> YEAR ( TODAY() ) |
| Error Message: | Date must be in the current year. |
| Error Location: | My Date |
Number Range Validation
| Field | Value | ||
|---|---|---|---|
| Description: | Validates that the range between two custom fields, Salary Min and Salary Max, is no greater than $20,000. | ||
| Formula: |
| ||
| Error Message: | Salary range must be within $20,000. Adjust the Salary Max or Salary Min values. | ||
| Error Location: | Salary Max |
Website Extension
| Field | Value | ||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| Description: | Validates a custom field called Web Site to ensure its last four characters are in an explicit set of valid website extensions. | ||||||||||||||||
| Formula: |
| ||||||||||||||||
| Error Message: | Web Site must have an extension of .com, .org, or .net. | ||||||||||||||||
| Error Location: | Web Site |
Valid Billing Country
| Field | Value | ||||||||||||||||||||||||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| Description: | Validates that the account Billing Country is a valid ISO 3166 two-letter code. | ||||||||||||||||||||||||||||||||||||||||||||
| Formula: |
| ||||||||||||||||||||||||||||||||||||||||||||
| Error Message: | A valid two-letter country code is required. | ||||||||||||||||||||||||||||||||||||||||||||
| Error Location: | Billing Country |
Create a validation rule to check that a contact is in the zip code of its account.
To complete this challenge, add a validation rule which will block the insertion of a contact if the contact is related to an account and has a mailing postal code (which has the API Name MailingPostalCode) different from the account's shipping postal code (which has the API Name ShippingPostalCode).
- Name the validation rule 'Contact must be in Account ZIP Code'.
- A contact with a MailingPostalCode that has an account and does not match the associated Account ShippingPostalCode should return with a validation error and not be inserted.
- The validation rule should ONLY apply to contact records with an associated account. Contact records with no associated parent account can be added with any MailingPostalCode value. (Hint: you can use the ISBLANK function for this check)
Solution : Validation Rule Detail
Rule Name Contact_must_be_in_Account_ZIP_Code
Active Checked
Error Condition Formula
AND (
NOT (ISBLANK ( AccountId )),
MailingPostalCode <> Account.ShippingPostalCode
)
Error Message
Contact Mailing ZIP Code must be same as Account ZIP Code
Error Location Top of Page
This comment has been removed by the author.
ReplyDelete