Formulas Overview

What is a formula field?

Data within a formula field is automatically updated by Salesforce based on the formula’s logic.

For instance, the sales team speaks with clients in terms of a monthly and yearly cost.  However, the monthly cost is simply the yearly cost divided by 12.  Instead of making the reps do the math, we can do it for them.  Enter a yearly cost of $120.00, the monthly cost a formula will display $10.00.  Change the yearly cost to $240.00, monthly cost is automatically updated to $20.00.

What is a cross-object formula?

A Cross Object Formula is one that references data in a related record.  For instance, I could create a formula field on opportunity to display the account’s phone number.

When the account’s phone number gets updated, the field on opportunity automatically reflects the change.

What’s important to know about formulas?

  • Formula fields are read-only (the same is true for roll-up summary fields) for all users.
  • For this reason, formula fields will not be displayed when editing records.  The value of the formula is only calculated after the record is saved.  This applies to cross-object formulas as well.  For instance, you can easily display the account’s phone number on the opportunity, but you cannot edit the phone number without returning to edit the account record.

Limitations of Formula Fields

There are considerations to be aware of when building formula fields.  Notably:

  • Formula fields cannot reference several field types, including (but not limited to): Text Area (Long), Text Area (Rich), Picklist (Multi-Select).
  • Some formula fields cannot be referenced by roll-up summary fields.
  • There are several restrictions on formula size (character, save, compile).  You can hit these on long/complex formulas with many arguments.

How do you create a formula field?

Simply create a new field, and select field type “Formula”.  Then enter the return type and syntax.

Simple & Advanced Formula Editors

When editing formula syntax, there are two types of editors:

The simple editor does not provide access to cross-object formulas or functions:

I recommend using the Advanced Formula editor, which exposes cross-object variables and functions:

 Sample Formulas

Examples of Advanced Formulas
[Must / Long / Salesforce.com]

100 Sample Formulas to Get you Started
[Should / Long / Salesforce.com]

31 Responses to “Formulas Overview”

  1. fogcitynative May 30, 2017 at 10:10 pm #

    Hi John:

    I am stuck on Display an Image with CASE() in Trailhead. Posted on Salesforce Community but no correct answer.

    I am stuck on the Challenge to Create a formula field that returns an image to indicate data quality. It’s in the Text Formulas module of Advanced Formulas badge.

    No matter what I try, I can’t pass the Syntax checker. I am required to display a certain number of stars based on Data Quality calculated in another formula. And include alternate text. (By the way, is there any way to trace formulas, so I could see the number being returned by the data quality formula?)

    Herer’s my formula . . .

    IMAGE
    (CASE(Lead_Quality_Helper__c ,
    5, (“/img/samples/stars_500.gif”, “5 Stars”,)
    4, (“/img/samples/stars_400.gif”, “4 Stars”,)
    3, (“/img/samples/stars_300.gif”, “3 Stars”,)
    2, (“/img/samples/stars_200.gif”, “2 Stars”,)
    1, (“/img/samples/stars_100.gif”, “1 Stars”,)
    0, (“/img/samples/stars_000.gif”, “0 Stars”,)
    “Unknown”))

    IMAGE can take two parameters. The URL and the ALTERNATE TEXT. So I put both parameters surrounded by (…..). The Syntax checker says I am missing a parenthesis between the URL and the Alternate Text. But inserting one there results in an error that CASE is expecting a number, not text.

    I am sure it works without the alternate text, as I got an answer from a user that passed doing it that way.

    But the challenge was to display both the graphic and the alternate text. I tried it with a comma after the alternate text and without. Neither works.

    What am I doing wrong?

    • JohnCoppedge May 31, 2017 at 11:46 am #

      I believe unknown needs to return 2 variables

      IMAGE
      (CASE(Lead_Quality_Helper__c ,
      5, (“/img/samples/stars_500.gif”, “5 Stars”,)
      4, (“/img/samples/stars_400.gif”, “4 Stars”,)
      3, (“/img/samples/stars_300.gif”, “3 Stars”,)
      2, (“/img/samples/stars_200.gif”, “2 Stars”,)
      1, (“/img/samples/stars_100.gif”, “1 Stars”,)
      0, (“/img/samples/stars_000.gif”, “0 Stars”,)
      “/img/samples/stars_000.gif”, “Unknown”))

      • fogcitynative June 3, 2017 at 6:48 pm #

        Still, haven’t found a solution. Posted on some other forums too. I did move the comma outside the closing parenthesis so it looks like this now, but still won’t pass a syntax check.

        IMAGE
        (CASE(Lead_Quality_Helper__c ,
        5, (“/img/samples/stars_500.gif”, “5 Stars”),
        4, (“/img/samples/stars_400.gif”, “4 Stars”),
        3, (“/img/samples/stars_300.gif”, “3 Stars”),
        2, (“/img/samples/stars_200.gif”, “2 Stars”),
        1, (“/img/samples/stars_100.gif”, “1 Stars”),
        0, (“/img/samples/stars_000.gif”, “0 Stars”),
        “/img/samples/stars_000.gif”, “Unknown”)

        • JohnCoppedge June 3, 2017 at 9:03 pm #

          Wow, a lot going on here.

          First this formula is missing a ) at the end.
          Second the quotes aren’t copying over correctly – had to retype them (this could be a formatting issue caused by the web)
          Third, I don’t think you can wrap the Image statement on the outside… I believe you will need to include it on the inside.

          Here’s one that I got to compile:

          CASE(Status,
          “5”, IMAGE(“/img/samples/stars_500.gif”, “5 Stars”),
          “4”, IMAGE(“/img/samples/stars_400.gif”, “4 Stars”),
          “3”, IMAGE(“/img/samples/stars_300.gif”, “3 Stars”),
          “2”, IMAGE(“/img/samples/stars_200.gif”, “2 Stars”),
          “1”, IMAGE(“/img/samples/stars_100.gif”, “1 Stars”),
          “0”, IMAGE(“/img/samples/stars_000.gif”, “0 Stars”),
          IMAGE(“/img/samples/stars_000.gif”, “Unknown”))

          • fogcitynative June 3, 2017 at 10:34 pm #

            I am on a Mac so that may be tbe quote problem. I will check my keyboard encoding.

            Thanks for the suggestion. I can see how this works better.

            The challenge comes directed from Salesforce Trailhead in the advanced formulas module.

            They do show a CASE statement as an argument for an image statement in an earier part of the tutorial. But at tbis point I just want to pass the challenge, get the points and move on.

  2. lepagekristin April 25, 2017 at 4:56 pm #

    The first link in the Sample Formulas section at the very end isn’t a valid link:

    Examples of Advanced Formulas
    [Must / Long / Salesforce.com]

    Any other recommendations on where I can see some more examples of advanced formulas?

    Thank you!

  3. kmkaast March 19, 2017 at 6:07 am #

    IF(ISPICKVAL(StageName, “Closed Won”),
    ROUND(Amount *0.02, 2), 0)

    What the “2” stands for ?

  4. Mayank786 September 1, 2016 at 3:39 pm #

    Hi,
    How can i create a create a formula field in the opportunities object which takes the phone from the lead object as a cross object reference.

    Thanks!!

  5. afarooqui March 22, 2016 at 6:40 pm #

    What’s interesting is when I create a Account Phone field on the Opportunity object, it sees Phone as a text data type. So I select create new formula field in the Opportunity object. I give it a field label. The Formula return type options are just :
    Checkbox
    Currency
    Data
    Date/Time
    Number
    Percent
    Text
    there is no option for Phone in the formula return type. I tried selecting number and once I enter the formula to get the account phone, it gave me an error that there was a data type mismatch. So I changed it to text and it worked. So it seems like when it comes to formula, phone is treated as text.

  6. jsobrien November 8, 2015 at 3:46 pm #

    The sample formula pages are GOLD!!!

    Thanks,

  7. elachkar.omar@gmail.com October 9, 2015 at 12:08 am #

    Hi John ,

    With regards to the Cross object Formula , do we have to do add it to every Object that we have ? I will give an example to help explain myself . What if we have the phone number field added to other Objects other than ” Account ” , and only Account had this link with Opportunity so that if the Phone number changes in Account it will change in the Opportunity Field . Is there any way that the Phone number will change in all fields that are concerned with Phone number

    Thanks in advance ,

    Omar ,

    • JohnCoppedge October 10, 2015 at 2:38 pm #

      You would have to create a field on each object that you want to capture/display the information, yes.

  8. johns. September 14, 2015 at 8:23 am #

    Hello John

    Is it possible to relate with a cross formula option in the same Relation ?

    For example it i create custom adress field i just want to relate to the standard billingadress field

    best regards john s.

    • JohnCoppedge September 14, 2015 at 3:11 pm #

      Not sure I completely understand the question – you can reference address fields in formulas. If you wanted to update a standard address field you would need to use a workflow rule, which could reference a formula in the workflow.

  9. Rena Bennett-Dellwo April 26, 2015 at 4:22 pm #

    I know I’ve seen the answer to this somewhere, but now I can’t find it: What is the “!” in some formulas; where does it come from or how is it used? Thanks!

    • JohnCoppedge April 26, 2015 at 10:42 pm #

      The ! variables are those used in email templates (the syntax in a formula field will look different) – not sure why the two are different…

      • Rena Bennett-Dellwo April 26, 2015 at 11:56 pm #

        Thanks, John!

    • nk005347 October 10, 2016 at 11:31 am #

      I also have same question, when is “!” used in formula

  10. Maura McNulty April 13, 2015 at 8:24 pm #

    Is there a limit to a certain number of characters in the length of a formula? Is it 4000?

  11. Rena Bennett-Dellwo February 13, 2015 at 11:08 pm #

    Tiny typo in the 2nd sentence in the 2nd paragraph: “cost is a simply the”.

  12. Jody Mycka November 20, 2014 at 4:26 am #

    Hi John – The links within “Examples of Advanced Formulas” bring you to a Saleforce page discussing disabling SSL 3.0 encryption. I clicked on several of the links and received the same result.

  13. Berglind Heath-Smith September 10, 2014 at 2:56 pm #

    Hi John – you have indicated those two Sample Formula documents as being “Must” and “Should” – do we need to know all the formulae from all the examples?

    • JohnCoppedge September 10, 2014 at 9:54 pm #

      You should be well versed in the capabilities of formulas and have a solid understanding of the syntax – you don’t know necessarily need to memorize everything but you should definitely have a high degree of familiarity.

Leave a Reply