Formula Best Practices

Tip 1: Put Every Function on a Separate Line

It’s easy to fall into the habit of keeping an entire formula on a single line, especially when the formula is small. Putting each function on its own line makes the formula easier to read and troubleshoot. These examples show the same formula, first with no line breaks, and then with each function on a separate line.

IF(AND(ISBLANK(myDate_c),active_c=true),"Missing Date","Not Applicable")

IF(
AND(
ISBLANK(myDate_c),
active_c=true
),
"Missing Date",
"Not Applicable"
)

Tip 2: Indent Sections Within Parentheses

When your formula involves multiple functions, indentation helps visually isolate each function and makes it easier to identify errors, such as misplaced characters.

In this example, with indentation, you see that the bulk of the formula sits within a single IF statement and that the AND statement contains two functions. Inside the AND statement, the function ISBLANK is enclosed in parentheses.

IF(
  AND(
    ISBLANK(myDate_c),
    active_c=true
  ),
  "Missing Date",
  "Not Applicable"
)

Indentation can also help you zero in on mistakes. With a flat layout, it’s difficult to see that an extra “)” is included after the ISBLANK statement, and there are no visual clues about how the formula is structured.

IF(
AND(
ISBLANK(myDate_c)
),
active_c=true
),
"Missing Date",
"Not Applicable"
)

The indented layout makes it easy to see the formula’s structure. You can quickly find and remove the extra character so that the AND statement is correctly formatted.

IF(
  AND(
    ISBLANK(myDate_c)
    ),
    active_c=true
  ),
  "Missing Date",
  "Not Applicable"
)

Tip 3: Write Statement and Function Names in Uppercase

All the examples here use uppercase letters for statement and function names, such as IF, AND, and ISBLANK. Using uppercase for these terms creates a clear distinction between functions and parameters and brings some visual clarity to a complex formula.

Tip 4: Handle Null and Required Input Field Values

These examples reference a field called myDate__c and use the ISBLANK check to confirm that the field is populated. It’s important to verify the contents of any field in a formula. Without this verification, a formula can fail. For example, if you add a second date to the formula and perform a greater than operation, include the ISBLANK check for the second date to ensure that the formula executes correctly.

IF(
  AND(
    ISBLANK(myDate__c),
    ISBLANK(mySecondDate__c),
    active__c=true,
    mySecondDate__c > myDate__c
  ),
  "Missing Date",
  "Not Applicable"
)

Tip 5: Use the HYPERLINK() Function to Create URLs

To create valid clickable links in Formula fields, use the HYPERLINK() function. This function is a TEXT function with syntax

HYPERLINK(URL, link_name [,target])