A Formula for Success

Formula fields are a great way to derive data using information from the record or related records. You can fall out of love with them because of the dread of updating a long and complex expression and having to understand what each bit does.

In this article, we will talk about some best practices and ways to mitigate against complex formulas. The sections below will guide you through different strategies which can help the future you. This can ultimately help you better understand, maintain and produce more robust and hopefully error-free formulas.

What are Operators?

An operator is an instruction which can manipulate data or apply logic in a specific way to generate a specific result. A full list of operators available to formula fields can be found here.

For example, how do we evaluate ‘Is the country United Kingdom’?

The == operator can help with this expression:
Country__c == ‘United Kingdom’
Now, how can we evaluate ‘Is the country United Kingdom and the county is London?’:

The && operator can help with this expression:
Country__c == ‘United Kingdom’ && County__c == ‘London’
Symbol and text equivalents

Operators can come with symbol and text equivalents. Below are some examples of these:
'X = Y' is the same as 'X == Y'
'X == NULL' is the same as 'ISBLANK(X)'
'X || Y' is the same as'OR(X, Y)'
(Has_Attended__c && Is_Active__c) is the same as AND(Has_Attended__c, Is__Active__c)

Why use a common language?

As symbol and text operators are interchangeable, it can often lead to complex/difficult-to-read formulas.

Imagine the following formula field called ‘Ready to Enroll’ which contains the following expression:

Mix
IF(Is_Active__c && OR(Has_Attended__c == TRUE, Is_Exempt__c = TRUE), TRUE, FALSE) && (Is_Complete__c || Is_Not_Required__c)
A mishmash of operators can lead to difficulty reading and understanding the expression. The above can be written as:

Symbols
IF(Is_Active__c && (Has_Attended__c == TRUE || Is_Exempt__c = TRUE), TRUE, FALSE) && (Is_Complete__c || Is_Not_Required__c)
Text
AND(IF(AND(Is_Active__c, OR(Has_Attended__c == TRUE, Is_Exempt__c = TRUE)), TRUE, FALSE), OR(Is_Complete__c, Is_Not_Required__c))
Having consistent language can help with readability and avoid sudden surprises when updating formula fields.

What are Booleans?

Booleans are a great way to distinguish whether something is true or false. We can further simplify the above example by replacing equal checks on boolean fields. For example, the 2 comparisons below are the same:

• Has_Attended__c == TRUE
• Has_Attended__c

Having a descriptive field name here is key.

Taking the symbol and text examples from above, they can be re-written as:

Symbols
IF(Is_Active__c && (Has_Attended__c|| Is_Exempt__c), TRUE, FALSE) && (Is_Complete__c || Is_Not_Required__c)
Text
AND(IF(AND(Is_Active__c, OR(Has_Attended__c, Is_Exempt__c)), TRUE, FALSE), OR(Is_Complete__c, Is_Not_Required__c))
After taking that extra step to simplify those booleans, it’s now clear we don’t even need that IF statement!

IF(X, TRUE, FALSE) is always equivalent to just X, therefore, our further simplified expression will look like this:

Symbols
Is_Active__c && (Has_Attended__c || Is_Exempt__c) && (Is_Complete__c || Is_Not_Required__c)
Text
AND(AND(Is_Active__c, OR(Has_Attended__c, Is_Exempt__c), OR(Is_Complete__c, Is_Not_Required__c))

Making formulas readable

Line breaks and spaces can play a huge role in readability. All the examples above use one line making it difficult to scan the blocks of expressions it’s trying to evaluate.

Symbols

Is_Active__c && (Has_Attended__c || Is_Exempt__c)
&&
(Is_Complete__c || Is_Not_Required__c)

AND(
    AND(
    Is_Active__c,
        OR(Has_Attended__c, Is_Exempt__c)
    ),
    OR(Is_Complete__c, Is_Not_Required__c)
)

Break down those monolithic formulas

Splitting your formula field into separate fields can help alleviate complex expressions. The formula we’ve been working on called ‘Ready to Enroll’, so far, requires many fields to achieve this. If we captured fields into their area of concern, we can achieve a level of abstraction, further simplifying our solution.

We can break down(Is_Complete__c || Is_Not_Required__c)to its own field called ‘Can_Enroll__c’

The final expression would look like this:

Symbols

Is_Active__c && (Has_Attended__c || Is_Exempt__c)
&&
Can_Enroll__c

AND(
    AND(
    Is_Active__c,
        OR(Has_Attended__c, Is_Exempt__c)
    ),
    Can_Enroll__c
)

Are there any tools that can help?

Salesforce provides out-of-the-box formula editors. These come in 2 flavours, Simple and Advanced. Although these generally do the job, there are tools out there to level up that experience.

Better Salesforce formula editor is a chrome extension which adds a new tab to the formula field editor. It provides syntax and bracket pair highlighting as well as other useful shortcuts/syntax-checking features.

Not exactly a tool embedded within Salesforce, however, DrCode – Boolean Expressions Calculator takes in an expression and attempts to simplify it. It can be useful for breaking down repeated logic.

Now it’s your turn

We’ve dived into operators and spoken about having consistent language when writing expressions; simplified expressions by removing specific boolean equality checks; made our expression clearer and more readable by introducing line breaks and better spacing; taken an extra step to break down our formula by separating blocks of expressions into their own concerning field and we’ve introduced tools to help write better, more concise formulas.

We’d love to see which of these combinations you’ve managed to adopt, and how this has transformed your complex, unmanageable formulas. Please share these with us by getting in touch with our team.