📣 Attention Salesforce Certified Trailblazers! Link your Trailhead and Webassessor accounts and maintain your credentials by December 14th. Learn more.
close
trailhead

Level Up with Advanced Formulas

Learning Objectives

After completing this unit, you’ll be able to:
  • Implement advanced formulas to match complex use cases.
  • Write clean, easy-to-understand formulas.

Advanced Formulas

We’ve seen a lot of complicated formulas in this module. It’s not always easy, however, to take a business problem and make it into a formula-based solution. This unit has a few tips for taking your formulas from concept to reality.

Write It Out

When you have an idea for a formula, think about it in everyday language before you pull up the Advanced Formula Editor. Start by asking yourself what your goal is and describing it conversationally.

This example came from the Salesforce Trailblazer Community: Say that your goal is to ensure that if anyone other than a System Administrator marks an opportunity as “Closed Lost,” they have to provide an explanation.

For this example, we assume that you already have the custom picklist field, Loss Reason, populated with valid reasons for losing an opportunity. Because no one should be able to save a record without providing a reason, we use a validation rule formula to accomplish our goal.

In plain language, your formula is:

Show an error when the Stage of an opportunity is changed to “Closed Lost” by someone who isn’t a System Administrator, and Loss Reason is blank.

After you’ve written out your formula, look for keywords like “and,” “or,” and “if” that correspond to the functions used throughout this module. These functions are the building blocks of larger, more complicated formulas. Recognizing them in your use cases helps you implement such formulas.

To check that the user is not a System Administrator, insert $Profile.Name, a merge field that represents the profile making edits to the record. To check if StageName has been updated, use ISCHANGED(). ISCHANGED() returns true if the current value of a field differs from the previous value.

You can check the rest of your requirements with ISPICKVAL(). For more information on ISPICKVAL(), see Using Picklists in Formulas.

  1. In Setup, use the quick find box to find the Object Manager.
  2. Click Opportunity | Validation Rules.
  3. Click New.
  4. In Rule Name, enter Loss Reason Required.
  5. In Error Condition Formula, enter the following formula:
    AND(
      $Profile.Name <> "System Administrator",
      ISCHANGED(StageName),
      ISPICKVAL(StageName, "Closed Lost"),
      ISPICKVAL(Loss_Reason__c, "")
    )
  6. In Error Message, enter Opportunities can’t be marked Closed Lost without providing a Loss Reason.
  7. For error location, select Field and Loss Reason.
  8. Click Save.

Now, whenever a user who isn’t a System Administrator marks an opportunity as Closed Lost, an error message displays.

Use Clear Spacing and Formatting

Formulas are easier to read and understand when you use proper spacing and formatting. When we worked on Text formulas, we wrote a formula that dynamically assigns a rating to a lead based on the country, revenue, and source.

IF(AND(AnnualRevenue > 1000000, CONTAINS(CASE(Country,
"United States", "US", "America", "US", "USA", "US", "US", "US",
"NA"), "US")), IF(ISPICKVAL(LeadSource, "Partner Referral"),
"Hot", IF(OR(ISPICKVAL (LeadSource, "Purchased List"),
ISPICKVAL(LeadSource, "Web")), "Warm”, "Cold")), "Cold")

Although this formula is syntactically correct, it’s almost impossible to tell what it does or how the logic works. Because white space and returns don’t matter in formulas, it’s easy to make your formula more readable with spaces. Here’s the same formula, but cleaned up with indentations and returns.

IF(
  AND(AnnualRevenue > 1000000,
  CONTAINS(CASE(Country, "United States", "US", "America",
    "US", "USA", "US", "US", "US", "NA"), "US")),
  IF(
    ISPICKVAL(LeadSource, "Partner Referral"), "Hot",
    IF(
      OR(
        ISPICKVAL(LeadSource, "Purchased List"),
        ISPICKVAL(LeadSource, "Web")
      ), "Warm”, "Cold"
    )
  ), "Cold"
)

Although there are no hard and fast rules for formatting formulas, we recommend that you indent with two spaces each time you nest a logical statement. When you indent properly and consistently, it’s easier to see which function you’re working in and avoid mismatched parentheses.

Another way to make logical statements easier to read is by using logical operators instead of functions. When you use && instead of AND(), or || instead of OR(), it’s easier for someone reading your formula to follow the logical flow. Here’s the same formula one more time, with && and || instead of AND() and OR().

IF(
  AnnualRevenue > 1000000 &&
  CONTAINS(CASE(Country, "United States", "US", "America", "US", 
    "USA", "US", "US", "US", "NA"), "US"),
  IF(
    ISPICKVAL(LeadSource, "Partner Referral"), "Hot",
    IF(
      ISPICKVAL(LeadSource, "PurchasedList") ||
      ISPICKVAL(LeadSource, "Web"),
      "Warm", "Cold"
    )
  ), "Cold"
)

Keep It Simple

When writing a complicated formula, it’s easy to get bogged down in nested logical statements and references to other fields. Often, the simplest formula is the best formula.

This validation rule formula returns true if an opportunity’s Close Date is not in the current month
OR (
CloseDate < DATE( YEAR(TODAY()), MONTH(TODAY()), 1), 
IF (
AND (
MONTH (TODAY() ) =1, 
CloseDate > DATE( YEAR(TODAY() ), MONTH(TODAY() ), 31)),
true, 
IF (
AND (
MONTH (TODAY() ) =2, 
CloseDate > DATE( YEAR(TODAY() ), MONTH(TODAY() ), 28)), 
true,
IF (
AND (
MONTH (TODAY() ) =3, 
CloseDate > DATE( YEAR(TODAY() ), MONTH(TODAY() ), 31)), 
true,
IF (
AND (
MONTH (TODAY() ) =4, 
CloseDate > DATE( YEAR(TODAY() ), MONTH(TODAY() ), 30)),
true,
IF (
AND (
MONTH (TODAY() ) =5, 
CloseDate > DATE( YEAR(TODAY() ), MONTH(TODAY() ), 31)),
true, 
IF (
AND (
MONTH (TODAY() ) =6, 
CloseDate > DATE( YEAR(TODAY() ), MONTH(TODAY() ), 30)),
true,
IF (
AND (
MONTH (TODAY() ) =7, 
CloseDate > DATE( YEAR(TODAY() ), MONTH(TODAY() ), 31)),
true,
IF (
AND (
MONTH (TODAY() ) =8, 
CloseDate > DATE( YEAR(TODAY() ), MONTH(TODAY() ), 31)),
true,
IF (
AND (
MONTH (TODAY() ) =9, 
CloseDate > DATE( YEAR(TODAY() ), MONTH(TODAY() ), 30)),
true,
IF (
AND (
MONTH (TODAY() ) =10, 
CloseDate > DATE( YEAR(TODAY() ), MONTH(TODAY() ), 31)),
true,
IF (
AND (
MONTH (TODAY() ) =11, 
CloseDate > DATE( YEAR(TODAY() ), MONTH(TODAY() ), 30)),
true,
IF (
AND (
MONTH (TODAY() ) =12, 
CloseDate > DATE( YEAR(TODAY() ), MONTH(TODAY() ), 31)),
true, false
)))))))))))))
This complex check determines whether the Close Date is earlier than the first day of the current month, or later than the last day of this month. But actually, the day doesn’t matter at all: if the month and year of the Close Date are the same as the month and year of the current month, then it’s the same month. So we can rewrite the formula as:
NOT( 
  AND( 
    MONTH( CloseDate ) = MONTH( TODAY() ), 
    YEAR( CloseDate ) = YEAR( TODAY() ) 
  ) 
)
This new version is much more readable, and only compiles to 200 characters compared to more than 3,000 for the original formula. If your formula seems more complicated than it should be, it probably is—try approaching the problem from a different angle to simplify things.
retargeting