Introduction

Once you get the hang of it, Formulas are a delight to work with. Before proceeding any further, ensure you know how the various functions and operators work. And before you know it, you’ll be creating validation rules and formulas for your org in the blink of an eye! It’s that easy.

Advanced Formulas in Salesforce

Working with Advanced Formulas naturally leads to creating some complex expressions to get things moving in your org. Some of it may seem intimidating at first. Still, there are a few things you can keep in mind before writing formulas that can be helpful to both you and the future admins of your org.

Use Helper Formula Fields

Sometimes a formula may require you to make multiple calculations which can be confusing to express in a single field. Helper Formula Fields to the rescue! Instead of writing a Formula using a single field, we can break down the formula with the help of multiple fields. This results in a formula that’s short and easier to understand!

Example:

Let’s say you want to assign a color to Case records depending on how long they’ve been open. They can take on the colors:

RED – If the Case has been open for more than 30 days.

ORANGE – If the Case has been open for more than 20 days.

YELLOW – If the Case has been open for more than 10 days.

GREEN – In all other cases.

If we write the formula, Case Age Colour, using a single field, it will look this:

IF( (NOT(IsClosed) && (TODAY() - DATEVALUE(CreatedDate) > 30)), "RED",
 
   IF( NOT(IsClosed) && (TODAY() - DATEVALUE(CreatedDate) > 20), "ORANGE",
 
     IF(NOT(IsClosed) && (TODAY() - DATEVALUE(CreatedDate) > 10), "YELLOW","GREEN")))

We can simplify this formula by creating a separate field to calculate the Case Age.

TODAY() -  DATEVALUE( CreatedDate )

Now we can alter the formula for Case Age Colour using the custom formula field, Case Age, like so:

IF(NOT(IsClosed) &&  Case_Age__c  > 30, "RED",
 
      IF(NOT(IsClosed) &&  Case_Age__c  > 20, "ORANGE",
 
           IF(NOT(IsClosed) &&  Case_Age__c  > 10, "YELLOW","GREEN")))

Well, isn’t the second formula less cluttered and simple to understand? Additionally, suppose you need to use the formula for Case Age in another field. In that case, you can re-use the field instead of writing it all over again.

Visualize It / Write It Out

Imagine you’ve been handed over the responsibility of writing a formula that involves a series of IF functions. What would be the next step? Most likely, you’d immediately start writing the formula down. Don’t. I can’t stress this enough, the ideal way forward would be to draw a flow chart and then write the formula or expression.

Let’s take the example of the field, Case Age Colour, which we worked on just before. Instead of simply writing the formula down, drawing a flow chart helps you visualize the problem in a more organized manner and reach the solution without much difficulty.

The flow chart of Case Age Colour would look this:

The flow chart leads to practical problem analysis, and you have the solution right in front of your eyes! And no, you needn’t draw it like this, all you need is paper and a pen, and you can get started.

What if your problem does not involve a series of IF functions but simply a couple of logical formulas you need to string together? As soon as you receive a problem, say it aloud in a conversational manner and look for keywords such as AND, OR, NOT, and IF.

Example :

Your goal is to ensure that when anyone other than a System Administrator marks the Status of a Case as “Escalated,” they have to provide an explanation.

Let’s understand the question in plain language :

Display an error if anyone NOT the System Administrator changes the field, Status, of a Case to Escalated, AND the field, Reason For Escalation, is blank.

Reason For Escalation is a custom field that needs to be created if you haven’t already. Let’s get started:

• Let’s start by writing an expression that evaluates whether the User making the change is NOT the System Administrator. To do that, insert the merge field, $Profile.Name.
• Next, we’ll need to check whether the picklist field, Status, has taken on the value, Escalated. To do that, we’ll use the ISPICKVAL() function.
• Finally, we need to ensure that the field, Reason For Escalation, is blank. We’ll use the function ISBLANK() for that.

We should end up with a formula like this:

Profile.Name <> "System Administrator" && ISPICKVAL(Status, "Escalated") && ISBLANK(  Reason_For_Escalation__c )

As a ground rule when working with formulas or validation rules, always write down or visualize the formula with the help of a flow chart before proceeding.

Use Clear Spacing and Formatting

I cannot stress this enough, always ensure that the formulas you write have precise spacing and the appropriate formatting to avoid confusion later on. You’re great at writing formulas, but forgetting this rule means ending up with gibberish that’s hard to understand and next to impossible to re-work if necessary. And imagine reading a formula written by a former admin that makes no sense to you. I’m sure you do not want that!

For example, remember the Case Age Colour field we worked on earlier? Let’s say I wrote the code in this way:

Although it will work fine, with absolutely no errors, can you understand what’s going on here without wasting time?

Let’s say you’re reading a formula that’s far more complex than this. It’ll be impossible to understand what is going on in that case.

IF( (NOT(IsClosed) && (TODAY() - DATEVALUE(CreatedDate) > 30)), "RED", IF( NOT(IsClosed) && (TODAY() - DATEVALUE(CreatedDate) > 20), "ORANGE", IF(NOT(IsClosed) && (TODAY() - DATEVALUE(CreatedDate) > 10), "YELLOW","GREEN")))

Let’s add the appropriate indentations and returns:

IF( 

   ( NOT(IsClosed) && (TODAY() - DATEVALUE(CreatedDate) > 30) ), "RED",

     IF( 

        ( NOT(IsClosed) && (TODAY() - DATEVALUE(CreatedDate) > 20) ), "ORANGE",
 
        IF(

           ( NOT(IsClosed) && (TODAY() - DATEVALUE(CreatedDate) > 10) ), "YELLOW","GREEN"
           
           )

         )		

      )

Ideally, your formula should look like this. However, there are no strict rules regarding the format of formulas. But here are a few points to keep in mind:

• It’s recommended that we indent (press tab) by two spaces every time we nested logical statements.
• Use logical operators over logical functions, i.e., && instead of AND(), || instead of OR().

Keep It Simple

It’s pretty easy to get sidetracked when writing complex statements. Instead of a simple formula, we can end up with a series of nested logical statements or references to other fields that are unnecessary.

Example:

Let’s create a checkbox field, On Track, for Case objects that are selected if the Case was created in the current month and is closed.

IF(IsClosed, 
 
 
   OR(    

DATEVALUE(CreatedDate) = DATE( YEAR(TODAY()), MONTH(TODAY()), 1), 
DATEVALUE(CreatedDate) = DATE( YEAR(TODAY()), MONTH(TODAY()), 2), 
DATEVALUE(CreatedDate) = DATE( YEAR(TODAY()), MONTH(TODAY()), 3), 
DATEVALUE(CreatedDate) = DATE( YEAR(TODAY()), MONTH(TODAY()), 4), 
DATEVALUE(CreatedDate) = DATE( YEAR(TODAY()), MONTH(TODAY()), 5), 
DATEVALUE(CreatedDate) = DATE( YEAR(TODAY()), MONTH(TODAY()), 6), 
DATEVALUE(CreatedDate) = DATE( YEAR(TODAY()), MONTH(TODAY()), 7), 
DATEVALUE(CreatedDate) = DATE( YEAR(TODAY()), MONTH(TODAY()), 8), 
DATEVALUE(CreatedDate) = DATE( YEAR(TODAY()), MONTH(TODAY()), 9), 
DATEVALUE(CreatedDate) = DATE( YEAR(TODAY()), MONTH(TODAY()), 10), 
DATEVALUE(CreatedDate) = DATE( YEAR(TODAY()), MONTH(TODAY()), 11), 
DATEVALUE(CreatedDate) = DATE( YEAR(TODAY()), MONTH(TODAY()), 12), 
DATEVALUE(CreatedDate) = DATE( YEAR(TODAY()), MONTH(TODAY()), 13), 
DATEVALUE(CreatedDate) = DATE( YEAR(TODAY()), MONTH(TODAY()), 14), 
DATEVALUE(CreatedDate) = DATE( YEAR(TODAY()), MONTH(TODAY()), 15), 
DATEVALUE(CreatedDate) = DATE( YEAR(TODAY()), MONTH(TODAY()), 16), 
DATEVALUE(CreatedDate) = DATE( YEAR(TODAY()), MONTH(TODAY()), 17), 
DATEVALUE(CreatedDate) = DATE( YEAR(TODAY()), MONTH(TODAY()), 18), 
DATEVALUE(CreatedDate) = DATE( YEAR(TODAY()), MONTH(TODAY()), 19), 
DATEVALUE(CreatedDate) = DATE( YEAR(TODAY()), MONTH(TODAY()), 20), 
DATEVALUE(CreatedDate) = DATE( YEAR(TODAY()), MONTH(TODAY()), 21), 
DATEVALUE(CreatedDate) = DATE( YEAR(TODAY()), MONTH(TODAY()), 22), 
DATEVALUE(CreatedDate) = DATE( YEAR(TODAY()), MONTH(TODAY()), 23), 
DATEVALUE(CreatedDate) = DATE( YEAR(TODAY()), MONTH(TODAY()), 24), 
DATEVALUE(CreatedDate) = DATE( YEAR(TODAY()), MONTH(TODAY()), 25), 
DATEVALUE(CreatedDate) = DATE( YEAR(TODAY()), MONTH(TODAY()), 26), 
DATEVALUE(CreatedDate) = DATE( YEAR(TODAY()), MONTH(TODAY()), 27), 
DATEVALUE(CreatedDate) = DATE( YEAR(TODAY()), MONTH(TODAY()), 28), 
DATEVALUE(CreatedDate) = DATE( YEAR(TODAY()), MONTH(TODAY()), 29), 
DATEVALUE(CreatedDate) = DATE( YEAR(TODAY()), MONTH(TODAY()), 30), 
DATEVALUE(CreatedDate) = DATE( YEAR(TODAY()), MONTH(TODAY()), 31)
      ), false)

Well, isn’t this quite a formula? Instead of creating a complex solution, let’s redo this formula to create a simple, easy-to-understand formula.

IsClosed && ( MONTH(DATEVALUE(CreatedDate)) = MONTH(TODAY()) )

All we had to do was replace the IF function with an AND and check whether the MONTH of created date equals the MONTH of TODAY(). And that took just a single line of code and a couple of minutes of our time to develop.

Take another look at your logic anytime you feel your formula is quite lengthy and complicated. Most times, that’s the Case. All we had to do was think from another perspective.