How to Create a Salesforce Formula Field
Wondering how to get started with Formula Fields in Salesforce? There are many types of custom fields you can create, but formula fields have a special kind of positive impact on your org. They can help you to display and leverage a great deal of new, untapped information from data that already exists on your records. Crazy, right?
This combined guide and tutorial will help you get started. Once you have completed both sections, you should understand exactly how formula fields in Salesforce work, having walked through the process of creating your first formula. You’ll then be ready to create many more in the future to support your organization’s different use cases.
What is a Formula Field in Salesforce?
Formula Fields are an out-of-the-box Salesforce feature that enables you to manipulate (get more out of) your existing Salesforce data.
Since most of us are familiar with Excel, these fields actually allow us to create simple and complex formulas based on data from a record and from its related records.
It’s similar to creating a formula in an Excel cell based on values from other columns, spreadsheets, or documents – keep in mind the example below, as we will apply the same logic in Salesforce.
Formula fields can have a number of applications, such as:
- Perform calculations between multiple number fields (which is also possible through Salesforce Reports to a certain extent).
- Display data from related objects.
- Concatenate and manipulate fields.
- Calculate the difference in days or a certain date between two date fields.
- Display different images if a certain criteria is true (e.g., traffic light based on length of another field).
Formula fields have slightly different characteristics from a normal field – they are not editable and their values are generated behind the scenes in the Object using the formula editor. If you can handle basic functions in an Excel sheet, then you can handle formula fields.
How Do I Create a Formula Field in Salesforce?
As with any other custom Salesforce field, formula fields are created by following just a handful of steps:
- Access the Object Manager from Setup – navigate to the object you wish to create the Formula Field on.
- Click New in the Field and Relationship section of the Object you decide on.
- Select Formula as the field type.
Formula Output Data Type
Once a formula field is selected, you should choose which data type is most suitable for your use case.
Layout and Features
After you have selected your data type, you will be presented with the formula creation page (I would always advise clicking on the Advanced tab). There are various features you can take advantage of here for building both simple and complex formulas to display different sets of data on your object:
- Insert Field: Insert fields from your object and related objects here. Standard field syntax looks like the Amount field, custom fields will always have “__c” appended to the end.
- Operators: Operators allow you to test and evaluate data in different ways – for example, performing simple mathematical equations or concatenating text .
- Functions: Functions allow you to transform data, perform calculations, or test data in a more comprehensive way than you can with operators. While you will find common functions such as IF(), there are Salesforce-specific ones as well, like ISPICKVAL() – used when working with picklist fields within a formula.
How to Create a Salesforce Formula Field
Now that we have covered all the basics, it’s time to get started with creating a formula based on a real-life scenario.
Lead scoring is a typical use case for many businesses, and provides a numerical score for Leads based on various factors. However, simply displaying a numerical Lead score may not be particularly easy (at a glance) for sales and business development teams.
So, let’s translate the number within the Lead Score field in a custom Lead Rating formula field – this will display if the Lead is a “Must Call” or “Normal Priority”, which all users can easily understand and use.
This step-by-step guide will help you navigate the creation of both a simple and a more complex formula, using one of the most widely known and used functions: the IF() statement.
As the business grows and more granularity is needed, the Lead Scoring threshold will most likely change, alongside many other processes that have to be supported in Salesforce.
In this particular example, the simple IF() formula above will change to a nested IF() with three conditions to evaluate.
Salesforce Formula Field Testing
The “Check Syntax” button exemplified above is just one of the ways you can check your formula is correct. While the syntax may be spot on, mistakes like referencing the wrong field or not capturing the entire requirement can still happen.
Make sure to:
- Test, test, then test again – create enough test records in your developer sandbox to cover the different scenarios.
- Engage business stakeholders for UAT – it’s important for the impacted users to confirm the formula works as expected, especially if it is a complex one which will be widely used.
- Once in production, smoke test – this is recommended for anything and everything that is released to the production environment.
*Smoke test: verify through test or actual records that the formula you deployed works as intended in the production environment.
Frequently Asked Salesforce Formula Questions
How many formula fields can we create in Salesforce?
There is no specified limit of how many formula fields can be created in total in your org or by Object. The limit is the total number of available custom fields you have left on an Object depending on your instance.
Are formula fields searchable in Salesforce?
At this time, searching formula fields remains a limitation. Feel free to visit (and vote for open ideas in) the trailblazer community, as Salesforce might consider implementing the capability during future releases.
What happens with the formula field on historical records?
Luckily for us, once a formula field is created, it is automatically calculated for all records of the Object the formula is created on, and populated accordingly.
Considerations for Using Formula Fields
There are some important considerations to be aware of when it comes to Salesforce Formula Fields, including:
- 3900 character limit (including spaces, return characters, and comments).
- 15000 bytes compile size.
- You cannot use long text areas, encrypted, or description fields in formulas.
- You cannot delete fields if they are referenced by a formula.
These are just a few examples. Before including formula fields as part of your solution design, make sure you check all considerations in the official documentation.
Learning formulas and logic in Salesforce is a very good skill to have as it can be used in various other places in the system, including Reports, Workflows, Validation Rules, and Flows.
Make sure you check out the recommended best practices and get some hands-on experience with this Trailhead Trail.