Formula fields

We have already explained what formula fields are in Chapter 1Salesforce Fundamentals, but let's recap what we know about formula fields first. We'll learn more about their use in this section:

  • A formula is similar to a calculation that is executed or evaluated at the time we access/read the record, through the UI, a Visualforce page, the API, or Apex.
  • A formula field is read-only; you cannot edit it! You can edit the formula itself as an administrator from setup, but end users will never be able to edit the value/result from the UI.
  • A formula can make use of various data of the record itself or related data and even fixed variables to perform its calculations. Think of them as formulas you would use in an Excel spreadsheet. These formulas can make use of data and operations to calculate a new value for a data type.
  • Formula fields can return a value of type, checkbox, date, date/time, currency, number, percent, and text.
  • An example of a percent formula would be calculating a margin based on fields containing the cost and the sales price.
  • When referencing a parent record field in a formula, we call it a cross-object formula.
  • Cross-object formulas are all about getting information from a parent object into the child. Retrieving information pertaining to child records cannot be effected with a formula, but is effected through a roll-up summary field (which we'll explain more about in the next section)!

Let's start off by creating a formula field on our Movie__c object called Director's Birthdate, which renders the value of the Birthdate field of the related director record through our lookup called Director__c. This is a typical example of a cross-object formula, fetching data from a related record to show its value on the child record. The greatest benefit of this type of use is that if you modify the related record or reparent the record, the Director's Birthdate will always show the correct value of the related director automatically:

  1. Go to Setup Object Manager, select our Movie object, and click the Fields and Relationships tab. Click New to create a new custom field. Choose the Formula and give it the Director's Birthdate name and choose Date as the data type. The API name should get automatically populated with Director_s_Birthdate (if it doesn't, be sure to correct it so that we don't encounter any issues in future exercises):
  1. Hit Next to go to the formula editor. Click the Advanced Formula tab, which is, I find, the easiest to build your formulas with.
  2. Now, we could just write our formula like Director__r.Birthdate__c, but that's more advanced, and you'll get the hang of this when you have completed the section on Salesforce Object Query Language (SOQL) from Chapter 4, Apex Basics.
  3. For now, let's do it the point-and-click way and click on the Insert Field button within the formula editor. This will open a popup that will let you select fields from the object level you are creating the formula on; in our case, movie. So, if Movie (first column) is our base object, the second column will display all useable fields from that object for you to select.
When a field is a relationship field, such as an owner, or, in our example, director, the field will be displayed twice.
  1. In the second column, there would be a field name with just the word Director, and one more with a > after it. The > means you want to traverse the relationship to get to the fields of the related object for use. This is what we will use to get to the Birthdate field that is located on the Person object. If you select the field name without the >, you would actually want to use the value of that field itself from the base object, which would give you the record ID of the related Person record (such as a021t000005d7hJAAQ, for example):
  1. So, click the Director > field from the menu and a third column will appear containing the fields from the relationship object; in our case, the Person object. In here, select the Birthdate field and click Insert.
  2. You'll see that it has now put the correct API name of that field in your formula editor, just as I mentioned before: Director__r.Birthdate__c.
It does not say Director__c.Birthdate__c though. When you want to traverse the relationship through a custom lookup field, the __c field becomes __r (for relation). If you refer to the Director field itself on the Movie object, then it would have been the  Director__c custom field.
  1. Hence, it's best practice to click the Check Syntax button underneath the formula editor when you think your formula is complete, so that it is checked for any errors, as follows:
If your formula contains any errors, the error message would give you a number of clues as to what is exactly wrong with it.  It's always good to fill in a description   (and optionally some help text). The description is for your own reference, explaining why this field exists, or if any new administrator comes into your org, they can easily ascertain the purpose of this field. The help text is a short message you want to expose to end users in the UI when they hover over the small ? next to the field itself on the page layout. This is useful for explaining what the value in this field represents to them.
  1. At the bottom of the formula editor, there is a section called Blank Field Handling. Depending on the data type, your formula returns, in our case, a date. The output of the calculation would be zero or blank, which you would want to show to the user:
  1. Hit Next and leave all the defaults for the field level security and page layouts.
  1. If we go back to our The Godfather movie record in our BIM DB app, we'll notice that our new field, Director's Birthdate, is put at the bottom of the page layout and contains a value:

And that's exactly what we wanted as a result.

We have already seen how to modify the page layout, through the Lightning App Builder, so let's go ahead and place this new field a bit higher on the page layout (maybe right beneath the Director field), where it would make more sense to view it.

You can find some common example formulas on the Salesforce help and training portal here: https://help.salesforce.com/articleView?id=useful_advanced_formulas.htm&type=5

Formulas and their logic can be used in other places, such as the following:

  • Workflow rules
  • Approval processes
  • Approval steps
  • Assignment rules
  • Escalation rules
  • Auto-response rules

Some formula rules and limits to be aware of are the following:

  • Formulas can reference standard, custom, or other formula fields
  • Formulas can reference the object itself, or fields from parent objects, but never from child records!
  • Formulas cannot reference themselves
  • Fields referenced in a formula field cannot be deleted without an administrator first removing the reference from the formula
  • Formula fields can contain up to 3,900 characters, including spaces and line breaks
  • Cross-object formulas can reference fields from objects that are up to 10 relationships away