Skip to main content

Create a Formula Field

Learning Objectives

After completing this unit, you’ll be able to:

  • Create a custom formula field.
  • Use the Advanced formula editor.
  • Outline at least one use case for formula fields.
  • Create basic formulas.

Get Ready to Get Hands-on with Formulas

Launch your Trailhead Playground now to follow along and try out the steps in this module. To open your Trailhead Playground, scroll down to the hands-on challenge and click Launch. You also use the playground when it’s time to complete the hands-on challenges.

Basic Formula Fields

Let’s jump in and get hands on by building a couple of basic formulas. Your sales team wants to know at a glance what their commissions are for the opportunities they are working on. To calculate commissions, we multiply the Amount on the opportunity record by the current commission rate. Then we round that number to the nearest two decimals.

Let’s start by creating the custom field.

  1. From Setup, open the Object Manager and select Opportunity.
  2. Select Fields & Relationships.
  3. Click New.
  4. Select Formula and click Next.
  5. For Field Label, enter Commission.
  6. For Formula Return Type select Currency.
    When you select Currency a new Option appears for Decimal Places.
  7. Leave Decimal Places set to 2.
  8. Click Next.

Now let’s build the formula. You add the Amount field from the opportunity. Then add a multiply operator followed by the amount of the base commission.

  1. Select Advanced Formula.
  2. Click Insert Field.
  3. Select Amount.
    The Insert Field window displays a list of fields for the current object. Select a field and the Insert button is displayed.
  4. Click Insert.
    Amount appears in the formula editor.
    “”
  5. Click Insert Operator and select * Multiply.
    An * (asterisk) appears next to Amount in the formula editor.
  6. Click into the editor after the * and enter 0.06 after the * as the 6 percent commission.
    Your formula should be: Amount * 0.06.
  7. Click Check Syntax.
    The message next to the button says “No syntax errors in merge fields or functions. (Compiled size: 31 characters)”
  8. For Description enter Base commission for Amount of opportunity.
  9. Leave Treat blank fields as zeroes selected.
    Because the formula is using numbers, we want to be sure that any blank fields that we use are set to zero instead of blank.
  10. Click Next.

Finally, as you do any time you add a field to an object, establish field-level security and add to page layouts.

  1. Accept the defaults and click Next.
  2. Accept the default page layouts and click Save.

Congratulations! Your first formula and formula field is in the books. Let’s go look at the new data insights.

  1. From App Launcher (“”), search for Opportunities and select Opportunities.
  2. Select the All Opportunities list view.
  3. Click List View Controls (“”) and select Select Fields to Display.
  4. Move Commission over to the Visible Fields box.
  5. Click Save.

Look at all those Commission fields populated with just a simple formula field. Even simple formulas can provide valuable information.

Cross-Object Formulas

Let’s do another. This time you pull information from a parent object. A formula that spans two related objects (through a lookup relationship) and references merge fields on those objects is called a cross-object formula. For more info on how objects relate to each other, check out Salesforce Object Relationships Overview.

The sales team is truly inspired by seeing the commissions on opportunities. Now they want to see the account industry on the contact record, so they don’t have to pull up the account record when they’re talking to a contact. You got this.

  1. Return to the Object Manager and select Contact.
  2. Select Fields & Relationships.
  3. Click New.
  4. Select Formula and click Next.
  5. For Field Label, enter Account Industry.
  6. For Formula Return Type select Text.
  7. Click Next.

And the formula is…

  1. Select Advanced Formula.
  2. Click Insert Field.
  3. Select Account.
  4. Select Industry.
    The Insert Field window corresponding to the preceding steps.
  5. Click Insert.
    Your super simple cross-object formula should now look like this: Account.Industry.
  6. Click Check Syntax.

Wait, there’s an error! Error: Field Industry is a picklist field. Picklist fields are only supported in certain functions.

Ah yes. Industry is a picklist. In order to use it in the formula we have to convert it to text. Remember those Text Functions? Let’s use one of them.

  1. Back in the formula editor, place the cursor immediately before Account.Industry.
  2. In the Functions list, type t and then scroll down and select TEXT.
    “”
  3. Click Insert Selected Function.
    The formula is now TEXT(value) Account.Industry. You just need to insert Account.Industry for the value.
  4. Highlight value (the TEXT parameter) and delete it.
  5. Highlight Account.Industry and move it inside the parentheses, so it reads:
    TEXT(Account.Industry)
  6. Click Check Syntax. No errors now.
  7. For Description, enter Contact’s Account Industry.
  8. Select Treat blank fields as blanks.
    The field we’re using isn’t a number so if it’s blank, that’s okay.
  9. Click Next, Next, and Save.

Pull up a contact and view the Details. See the Account Industry field displayed for them? The sales team rejoices!

Note

The playground has the new Sales view for Contacts. To open the original contact list view, click the List View button. Then select the All Contacts list view.

Time for Dates

The service team leadership heard about the great things you’ve done for the sales team. They’re wondering if you could add a field to show how many days a case has been open. Of course you can!

  1. From Setup, open the Object Manager and select Case.
  2. Select Fields & Relationships.
  3. Click New.
  4. Select Formula and click Next.
  5. For Field Label, enter Days Open.
  6. For Formula Return Type select Number.
  7. For Decimal Places select 0.
  8. Click Next.

Let’s build the formula. But first, remember getting an error because your formula expected text from the Industry picklist? In this new formula, you want to return a number of days, but the format of the Date/Time Opened field is date/time. Let’s format that field to a date value.

  1. Select Advanced Formula.
  2. From Functions, select TODAY.
    This gives you the current day as a date value.
  3. Click Insert Selected Function.
  4. From Insert Operator, select - Subtract.
  5. From Functions select DATEVALUE.
  6. Click Insert Selected Function.
    You now have TODAY() - DATEVALUE(expression).
  7. Delete expression (the DATEVALUE parameter) inside the parentheses.
  8. Click Insert Field.
  9. Select Date/Time Opened.
  10. Click Insert.
    TODAY() - DATEVALUE( CreatedDate )
  11. Click Check Syntax.
  12. For Description, enter Number of days the case has been open.
  13. Keep Treat blank fields as zeroes selected.
  14. Click Next, Next, and Save.

Now, when you check some cases, you see the Days Open field is populated.

Note

You may notice that the dates in your playground are all the same date for certain things. Most of these dates were generated when the Playground was created. Some are just part of the practice data.

The service team is singing your praises—or are they? After looking at the new field data, you realize there’s an issue. You tackle that in the next unit using some logic functions.

Resources

Condividi il tuo feedback su Trailhead dalla Guida di Salesforce.

Conoscere la tua esperienza su Trailhead è importante per noi. Ora puoi accedere al modulo per l'invio di feedback in qualsiasi momento dal sito della Guida di Salesforce.

Scopri di più Continua a condividere il tuo feedback