Create Formula Fields

Accessibility

This unit requires some additional instructions for screen reader users. To access a detailed screen reader version of this unit, click the link below:

Open Trailhead screen reader instructions.

The Rundown

VP of Sales Allison Wheeler has requested your help in collecting additional information on opportunities. First, she’d like sales and executive users to see their commission calculated for won opportunities. She’d also like the regions and zones of accounts displayed on opportunities for all to see. And, in an effort to enhance pipeline accuracy, she’d like to display the discounted amount whenever a discount has been applied to an opportunity

Handle these requests by creating custom formula fields, which allow you to define calculations that reference other fields to display new numeric, text, date, or checkbox values specific to business requirements. Let’s get started.

Calculate the Commission

Create a custom formula field on the Opportunity object that calculates 10 percent commission whenever an opportunity is closed won.

1. From Setup, click Object Manager and select Opportunity.
   
2. Select Fields & Relationships then click New.
   
3. Select Formula as the Data Type, then click Next.
   
4. Enter Commission as both the Field Label and the Field Name.
   
5. Select Currency as the Formula Return Type.
   
6. Click Next.
   
7. Select Amount from the Insert Merge Field picklist.
   
8. Click Insert Operator and select *Multiply.
   
9. In the Commission (Currency)= box, enter 0.1.
   
10. Ensure the final formula looks like this: Amount * 0.1
11. Click Check Syntax, to ensure there are no errors.
    
12. For Description, enter: Calculates sales rep commission of 10 percent when opportunity is won.
13. For Help Text, enter: Sales rep commission when opportunity is won.
14. Click Next.
    
15. In the Visible column header, select and deselect the checkbox to clear the checkboxes.
    
16. In the Visible column, select Sales User and System Administrator.
    
17. Click Next and Save.
    

Next, modify the formula so that the commission is only calculated when an opportunity is closed won.

1. While still in Fields & Relationships for the Opportunity object, click the arrow next to Commission, and select Edit.
   
2. In the Formula Options section, click the Advanced Formula tab, and then modify the formula.
   
3. In the Commission (Currency)= box, click at the beginning of the formula to place the cursor just before Amount.
   
4. From the Functions list, select IF, and then click Insert Selected Function.
   
5. From the Functions list, select ISPICKVAL.
   
6. In the formula, highlight logical_test, then click Insert Selected Function.
   
7. In the formula, highlight picklist_field.
   
8. Click Insert Field, select Stage from the second list on the Insert Field overlay that appears, and then click Insert.
   
9. In the formula, highlight text_literal and enter "Closed Won" (including the quotation marks).
   
10. In the formula, highlight Amount * 0.1 and cut it (Ctrl/Command+X).
    
11. In the formula, highlight value_if_true and paste (Ctrl/Command+V).
    
12. In the formula, highlight value_if_false and enter 0.
    
13. Ensure your formula looks like this:
    

    IF( ISPICKVAL( StageName , "Closed Won") , Amount * 0.1, 0)

    
    
14. Click Check Syntax.
    
15. Click Save.
    

Customize Formula Fields

Create a custom formula field on the Opportunity object that displays the Region and Zone information from the account record.

1. While still in Fields & Relationships for the Opportunity object, click New.
   
2. Select Formula as the Data Type, then click Next.
   
3. Enter Region/Zone as the Field Label and Region_Zone as the Field Name.
   
4. Select Text as Formula Return Type.
   
5. Click Next.
   
6. Click the Advanced Formula tab if it is not already selected.
   
7. From the Functions list, select TEXT, and then click Insert Selected Function.
   
8. In the formula, highlight value.
   
9. Click Insert Field and select Account> from the second list on the Insert Field overlay that appears.
   
10. From the new list that appears, select Region, then click Insert.
    
11. Click at the end of the formula to position the cursor, and then click Insert Operator and select &Concatenate.
    
12. At the end of the formula, enter "/" (including the quotation marks).
    
13. Click Insert Operator, and then select &Concatenate.
    
14. From the Functions list, select TEXT, and then click Insert Selected Function.
    
15. In the formula, highlight value.
    
16. Click Insert Field and select Account> from the second list on the Insert Field overlay that appears.
    
17. From the new list that appears, select Zone, and then click Insert.
    
18. Ensure your formula looks like this: 
    

    TEXT( Account.Region__c ) & "/" & TEXT( Account.Zone__c )

19. Click Check Syntax.
    
20. For Description, enter: Displays the Region and Zone values from the account record.
21. For Help Text, enter: Account region and zone.
22. Click Next, Next and Save.
    

Next you create a formula field called Amount after Discount to calculate the amount after the discount has been applied. But first, create a custom percent field called Discount Percentage.

1. While still in Fields & Relationships for the Opportunity object, click New.
   
2. Select Percent as the Data Type and click Next.
   
3. Enter Discount Percentage as the Field Label.
   
4. Enter 3 for Length.
   
5. Click Next.
   
6. Select then deselect the Visible header to clear out the checkboxes.
   
7. Select Sales User and System Administrator.
   
8. Click Next and Save.
   

Now create the formula.

1. While still in Fields & Relationships for the Opportunity object, click New.
   
2. Select Formula as the Data Type and click Next.
   
3. Enter Amount After Discount as the Field Label and Amount_After_Discount as the Field Name.
   
4. Select Currency as Formula Return Type.
   
5. Click Next.
   
6. Click the Simple Formula tab.
   
7. From the Insert Field picklist, select Amount.
   
8. Click Insert Operator and select *Multiply.
   
9. Click Insert Operator and select ( Open Parenthesis.
   
10. After the open parenthesis, enter 1.
    
11. Click Insert Operator and select -Subtract.
    
12. From the Insert Field picklist, select Discount Percentage.
    
13. Click Insert Operator and select ) Close Parenthesis.
    
14. Ensure your formula looks like this: 
    

    Amount * ( 1 - Discount_Percentage__c )

15. Click Check Syntax.
    
16. For Description, enter: Calculates the opportunity amount after any discount has been applied.
17. For Help Text, enter: Opportunity amount after discount has been applied.
18. Click Next, Next, and Save.
    

You’ve helped make necessary information available to Allison’s sales team with formulas. Move on to the next step, where you create record types.