Formulas | Common formula functions and operators

You can use functions and operators in formulas to help you achieve your result. You can combine multiple functions and operators depending on the complexity of the value to return. You can also refer to other fields.

For more examples of functions and operators used typically with specific data types, see also:

Functions

The ISBLANK and ISNULL functions

ISBLANK and ISNULL work in the same way. Both determine if the expression has a value and return true if the expression is blank and has no value, and false if it has a value.

The key difference between ISBLANK and ISNULL is support for text fields: ISNULL evaluating a text field always returns false. For this reason Salesforce recommends you use ISBLANK for all new formulas, although existing formulas with ISNULL continue to work.

For ISBLANK, a field has a value and is not empty if it contains a character, blank space, or a zero. A space character entered with the space bar is a character, so a field with a space is not blank.

For example, if you want to check if the Home Address Line 2 field has a value, the formula looks like the following example:

Copy

Example of ISBLANK function

ISBLANK(fHCM2__Home_Address_2__c)

The BLANKVALUE and NULLVALUE functions

Where ISBLANK and ISNULL check if a field has a value and return true or false, BLANKVALUE and NULLVALUE return a specified substitute value if the field is empty. If the field is not empty, the function returns its value.

The key difference between BLANKVALUE and NULLVALUE is support for text fields: for NULLVALUE text fields are never null. For this reason Salesforce recommends you use BLANKVALUE for all new formulas, although existing formulas with NULLVALUE continue to work.

For example, you can use BLANKVALUE to check if a Team Member has a preferred name and if so return that name, and otherwise return their first name, as shown in the following example:

Copy

Example of BLANKVALUE function

BLANKVALUE(fHCM2__Preferred_Name__c, fHCM2__First_Name__c)

Instead of using another field as the substitute value, you can also specify a value in the formula. For example, if no value has been selected for Ethnicity, the formula returns the specified value "Not disclosed", as shown in the following example:

Copy

Example of BLANKVALUE function with specified value

BLANKVALUE(Ethnicity__c, "Not Disclosed")

The NOT function

The NOT function enables you to invert a condition you have specified in the formula.

Copy

The NOT function

NOT(logical)

For example, to invert one of the examples for ISPICKVAL function you can create a checkbox formula field, which is checked when the selected picklist value is not one of the values specified:

Copy

Example of the NOT function with ISPICKVAL

NOT(ISPICKVAL(Reason_For_Leaving__c, "Issues with Role"))

To check if a field is populated, you can combine the NOT function with the ISBLANK function.

For example, to invert one of the examples in the ISBLANK section you can create a checkbox formula field, which is checked when the Address Line 2 field is completed:

Copy

Example of the NOT function with ISBLANK

NOT(ISBLANK(fHCM2__Home_Address_2__c))

IF and CASE

The IF function uses the principle of if… then… else… to enable you to build conditional logic in formulas:

Copy

The IF function

IF(logical_test, value_if_true, value_if_false)

If the first element, the logical test, is true, then the formula returns what is specified as value_if_true, else the formula returns what is specified as value_if_false.

For example, to build a formula to check if the Home Address Line 2 is populated and if yes, include it in the address formula with a line break, but if not populated, not include it:

Start by inserting the IF function
Start creating the logical test by inserting the NOT function to replace logical_test
Insert the ISBLANK function inside the NOT function
Insert the Home Address 2 field into the ISBLANK function
In the IF function, replace value_if_true with a line break BR(), the Concatenate operator & and the Home Address 2 field. This means if the Home Address 2 field has a value, the formula returns a line break and the value of the field.
In the IF function, replace value_if_false with "" to return nothing.

The resulting formula looks like the following example:

Copy

Example of IF with NOT and ISBLANK

IF(NOT(ISBLANK(fHCM2__Home_Address_2__c)), 
BR() & fHCM2__Home_Address_2__c, 
"")

You can nest IF functions in a formula to evaluate multiple conditions.

For example, you can build on the following formula, which calculates the hourly rate for team members whose pay is calculated per week, and returns 0.00 for team members whose pay is determined in any other way:

Copy

Example of IF statement with ISPICKVAL

IF(ISPICKVAL(fHCM2__Current_Salary__r.fHCM2__Period__c, "Week"), 
fHCM2__Current_Salary__r.fHCM2__Amount__c / fHCM2__Hours_Worked__c, 
0.00)

To add further conditions:

Replace the 0.00 in the formula with a second IF function
In this second IF statement, replace logical_test in the second IF statement with a new condition:
Copy

A condition for logical test
```
ISPICKVAL(fHCM2__Current_Salary__r.fHCM2__Period__c, "Year")
```
In the second IF statement, replace the value_if_true with the calculation for the hourly rate:
Copy

Calculation for value
```
fHCM2__Current_Salary__r.fHCM2__Amount__c / 
52 / 
fHCM2__Hours_Worked__c
```
In the second IF statement, replace value_if_false with a new IF statement

Complete the third IF statement for the next pay period you want to consider, and repeat until you have covered all the pay periods. The value_if_false in the final IF statement of the example returns the salary amount for team members whose pay period is not covered in any of the other IF statements:

Copy

Example with nested IF statements

IF(ISPICKVAL(fHCM2__Current_Salary__r.fHCM2__Period__c, "Week"), 
fHCM2__Current_Salary__r.fHCM2__Amount__c / fHCM2__Hours_Worked__c, 
IF(ISPICKVAL(fHCM2__Current_Salary__r.fHCM2__Period__c, "Year"), 
fHCM2__Current_Salary__r.fHCM2__Amount__c / 52 / fHCM2__Hours_Worked__c, 
IF(ISPICKVAL(fHCM2__Current_Salary__r.fHCM2__Period__c, "Month"), 
fHCM2__Current_Salary__r.fHCM2__Amount__c * 12 / 52 / fHCM2__Hours_Worked__c, 
IF(ISPICKVAL(fHCM2__Current_Salary__r.fHCM2__Period__c, "Day"), 
fHCM2__Current_Salary__r.fHCM2__Amount__c / (fHCM2__Hours_Worked__c / 5), 
fHCM2__Current_Salary__r.fHCM2__Amount__c))))

Tip

The formula is easier to read if each IF statement starts on its own line.

This example evaluated 4 conditions. If you need to consider more potential conditions, it is possible evaluating the formula hits Salesforce limits. To reduce the number of characters used in the formula and the complexity of the nested IF statements, the CASE function is a good alternative:

Copy

The CASE function

CASE(expression, value1, result1, value2, result2,...,else_result)

With the CASE function you can specify the field just once, and then insert the values and results for each of the conditions you want to evaluate. Finally, insert the else_result for the formula to return in cases not evaluated by the formula.

The following example shows the nested IF statements of the previous example converted into a CASE function:

Copy

Example of the CASE function

CASE(fHCM2__Current_Salary__r.fHCM2__Period__c,
"Week", fHCM2__Current_Salary__r.fHCM2__Amount__c / fHCM2__Hours_Worked__c,
"Year", fHCM2__Current_Salary__r.fHCM2__Amount__c / 52 / fHCM2__Hours_Worked__c, 
"Month", fHCM2__Current_Salary__r.fHCM2__Amount__c * 12 / 52 / fHCM2__Hours_Worked__c,
"Day", fHCM2__Current_Salary__r.fHCM2__Amount__c / (fHCM2__Hours_Worked__c / 5), 
fHCM2__Current_Salary__r.fHCM2__Amount__c).

In the example, you can see:

The field for the pay period in place of expression
The different picklist values of the pay period (Week, Year, Month, Day) in place of value1, value2, and so on.
The calculations for the hourly rate for each pay period in place of result1, result2, and so on.
The salary amount field in place of else_result, which is returned for cases other than those used as values in the formula, here for the hourly paid team members.

Operators

The AND operator &&

The AND operator && enables you to combine multiple conditions in formulas.

Not to be confused with the Concatenate operator &. AND is often used with other operators and functions in formulas. Enter the first condition, then the AND operator && and then the second condition.

For example, a checkbox formula field on the Employment Record to identify all the part time employees in the UK could look like the following example:

Copy

Example of AND operator &&

fHCM2__Team_Member__r.fHCM2__Country__c = "UK" 
&& ISPICKVAL(fHCM2__Basis__c, "Part Time")

The checkbox is checked when the formula returns the Boolean value true when evaluating the following conditions:

the value of the Country field in the Team Member record is UK
the selected value for the Basis field in the Employment Record is Part Time

The OR operator ||

The OR operator || enables you to specify a result based on one of multiple conditions being true.

OR is often used with other operators and functions in formulas. Enter the first condition, then the OR operator || and then the second condition.

For example, a checkbox formula field on the Employment Record to identify all employees expected to have a value in the Contract End Date field could look like the following example:

Copy

Example of OR operator ||

ISPICKVAL(fHCM2__Basis__c, "Fixed Term") 
|| ISPICKVAL(fHCM2__Basis__c, "Maternity Cover") 
|| ISPICKVAL(fHCM2__Basis__c, "Temporary")

The formula evaluates to true if any of the following values is selected for the Basis picklist field:

Fixed Term
Maternity Cover
Temporary

For more examples of the ISPICKVAL function, see the picklist section in Mirror fields, picklist values, images.