Formulas | Introduction

Formulas can perform calculations, manipulate text, evaluate logical conditions, or display the value of another field. You can use a number of functions and operators in formulas to achieve the result. You can combine multiple functions and operators depending on the complexity of the value you wish to return. You can also use values of other fields in formulas. You can use formulas in formula fields, email templates, Flows, and action events.

Best practices

Before you start writing a formula, it is best practice to ensure you have done the following:

  • Clarified the requirements

  • Planned the formula

  • Considered how to make the formula future proof

Clarify the requirements

Consider the following questions:

  • What information does the formula calculate or retrieve?
  • Which object hosts the formula field?
  • Where do you want to display the field and who should have access to it?
  • What is the field’s output data type? Consider an example of what you would expect to see.
  • What existing fields need to be referenced?

The following table provides example responses to these questions for two different formula fields: 

Formula details Example 1 Example 2
Description of what the formula is intended to calculate or retrieve

Full name of team member with Preferred Name if they have one or First Name if they don't

 Standard Working Hours from Policy unless Hours worked override field on Employment Record used
Which object hosts the formula field? Team Member Employment Record
Where do you want to display the field? HR Portal, Team Member record, top section HR Portal, Employment Record, Information Section
Formula output type Text Number, 2 decimal places
An example of the kind of value you expect to see Liz Mary Stevens 37.5
Fields the formula references

  • Preferred Name or First Name

  • Middle Name

  • Surname

  • Working Hours on Policy

  • Employment Record Hours Worked override

Plan the formula

By having a clear understanding of the requirement, you can plan which functions, operators, and fields you need to achieve it. Sometimes there are multiple ways to achieve the same result so planning can help you configure the formula in the most efficient way.

The following table provides examples for the same use uses: 

Formula details Example 1 Example 2
Plan for the formula

Check if Preferred Name is populated. If it is, return Preferred Name. If not, return First Name. Check if Middle Name is populated. If it is, concatenate it. If Middle Name is not populated, return nothing, then concatenate with Surname.

 Check if Hours Worked field on Employment Record is populated and return if so. Then check if there is a Policy Override, and if so, collect the Hours Per Week from the policy. If no Policy Override, collect the Hours Per Week from the policy on the HR Department.
Functions, operators, and fields to use in the formula Concatenate BLANKVALUE - Preferred or First Name IF and IS BLANK - Middle Name IF NOT ISBLANK Employment Record Hours Worked, Employment Record Hours Worked, IF NOT ISBLANK Employment Record > Team Member > Policy Override, Employment Record > Team Member > Policy Override > Hours Per Week, Employment Record > Team Member > HR Department > Policy > Hours Per Week

Future proof the formula

When designing your formula, consider what could change in the future that could affect the formula:

  • It is best to not refer to specific people or records wherever possible in case those records change.

  • If values change in the future, can admins maintain the formula so it is still covers all scenarios? Are the kinds of changes required documented? Is it clear who can do changes like this? 

    For example:

    • If a formula specifies department names, what happens when a new department is added in the future?

    • Does the formula contain values expected to change year on year? Such as compensation planning formulas, formulas with references to particular periods of time? 

Best practices for future proofing include: 

  • Use the formula editor, do not attempt to enter the entire formula manually.

    Using the formula editor enables you to avoid typical errors made when entering the formula manually, such as syntax errors, typing errors, or missing brackets. Not having to search for such errors can save you time later. For more information, see Formulas | Create a formula field.

  • Use formatting: line breaks can make long complex formula easier to understand.

    For example, with nested IF statements, a line break can be entered at the start of each IF to make it easier to read.

    Line breaks and spaces in a formula do not affect it functioning correctly. For more information, see Formulas | Common formula functions and operators.

  • Use comments: You can add comments within a formula itself by wrapping the comment between /* at the start and */ at the end.

    Comments do not affect the functioning of the formula. Comments can often be useful for future reference for yourself and for other people who need to understand and maintain the formula in the future.

More information

For more information on best practices for formulas, see Salesforce help article: Build a formula field.

Limits

Formula fields have the following limits:

  • Character limit: Formula fields can contain up to 3,900 characters, including spaces, return characters, and comments. If your formula needs more characters, create separate formula fields and reference them in another formula field.
  • Compile size limit: Formula fields cannot exceed 5,000 bytes when compiled. The compile size is the size of the formula (in bytes) including all of the fields, values, and formulas it references. There is no direct correlation between the compile size and the character limit. Some functions, such as TEXT, DATEVALUE, DATETIMEVALUE, and DATE significantly increase the compile size. Because the compile size includes any referenced formulas, you cannot resolve a compile size limit by splitting the formula into multiple formula fields. You can, however, create a custom field that is not a formula field to reference and create a workflow rule to update the field with a formula which will not be included in the compile size
  • Field types: You cannot use long text area, encrypted, or description fields in formulas.

Learn more

In the help center: Functions and operators reference and Date and time functions for use in Payflow only.

Quick reference: Formula operators and functions by context in Salesforce developer documentation

Salesforce help articles: Some examples of very helpful ones are:

Salesforce Trailhead: Salesforce provide e-learning modules for most Salesforce functionality which provides not only and explanation of the functionality and instructions but also exercises you can work through to practice, by spinning up a dev org specifically for Trailhead exercises where your progress can be automatically assessed. Most modules are now in Lightning so may appear differently to the Salesforce Classic View. Particularly useful modules for formulas are: