The Power of the Formula

SalesforceConfigurationAdminAdministration

Jan 23

Introduction

Advancing your career as a developer brings you into contact with various technologies. Some are similar while others differ significantly in purpose and capabilities. Every time a .NET/Java developer moves to Salesforce they experience a sense of familiarity, followed by a raised eyebrow and a sense of wondering.

One of the first things that most developers do is to orient themselves with the “Data Types”. As Salesforce likes to mix up terminologies, it does so in this case as well and the developer would inspect the “Field Types”. As a Salesforce newbie, you will encounter several unfamiliar Field Types, but one that is particularly interesting is the Formula Field Type. Very often underestimated or taken for granted, the possibilities it enables should be appreciated by every developer. Very often Salesforce developers, who get used to them, once they return shortly to their development stack, say the formula field is one of the things they miss the most.

So, it would probably be for the best to just jump straight into them.

The formula field is a type of field that is always a read only field, meaning that it will always only present some information and cannot be changed by user input directly. Inside of it you can define a set of logical or operational formulas with field values, similar to Excel’s Formulas. The list of available formulas is quite extensive and there is a wide variety of their purpose. The formula field can represent one of the following base types which determine the type of value presented as the result of the formula:

Checkbox
Currency
Date
Date/Time
Number
Percent
Text
Time

Depending on your requirements, you will carefully choose which one you will need. You can imagine it as a subtype. By choosing one of them, you will find different capabilities that can be achieved with them, while also keeping the code behind much cleaner.

1. Status Images

They say that a picture is worth a 1000 words. One particular formula field feature is to display icons and images instead of textual information. Images can be hosted on Salesforce or some external repository (e.g. a CDN).

We utilize them quite often in order to improve the usability in the record detail view, list views or related lists. It is particularly useful when there is a need for an easily noticeable indicator of a problem with a record or to show an image of a product on the Product detail page. Another very common use case is to display a type or a status picklist. Using icons we can very easily display the selected values graphically.

To get this kind of behavior we can use the following formula:

IMAGE(image_url, alternate_text [, height, width])

You need to specify the url, alternative text and, optionally, the display size of the image.

IMAGE(
    "/resource/StatusIcons/" &
    CASE(Status__c,
        "Cancel",  "Icon_Cancel",
        "OK",      "Icon_Ok",
        "Pending", "Icon_Pending",
        "New",     "Icon_New",
        "Icon_Error"
    ) &
    ".png",
    TEXT(Status__c),
    32,
    32
)

In this example we defined a collection of status images into the “StatusIcons” zip file and uploaded them into the StaticResources. Depending on the value of the “Status__c” picklist field, we will show a different icon in the size of 32x32 pixels.

Using the Formula in this way, we can create all sorts of useful usability effects.

2. SOQL Helper

Developers using SQL to retrieve data from a relational database tend to put many operations into a query, since it saves resources and improves performances. SQL queries like this for example.

SELECT Id, (Lastname & ", " & Firstname) AS Fullname
  FROM User
 WHERE Firstname = Lastname

This query will return a list of users with their Identifier attribute and the full name, while concatenating the Firstname and Lastname as the Fullname, while filtering out those where the Firstname and Lastname are not equal.

Translating this query into the Salesforce world and SOQL would not be possible because of 2 reasons:

SOQL does not allow fields to be calculated or, in this case, concatenated.
SOQL requires WHERE clause conditions to always contain a field and a value, so 2 fields cannot be compared directly

Nonetheless, there is still a way to execute this with the help of formulas.

Fullname__c : Lastname & ", " & Firstname
FirstEqualLast_frm__c : Firstname = Lastname

SELECT Id, Fullname__c
  FROM User
 WHERE FirstEqualLast_frm__c = true

Equally, you can utilize this approach to data filtering for much more complicated cases taking advantage of all formula field functions, but also drill down into parent objects.

Another great example of filtering records with the help of formulas would be in cases of bulkified input criteria. Let’s imagine we have the following records saved:

  
    ID
    Firstname
    ComesFrom__c
    LivesIn__c
  

    1
    Andrew
    UK
    UK
  

    2
    John
    USA
    Australia
  

    3
    Andrew
    UK
    USA
  

    4
    Slater
    Australia
    Australia
  

ID	Firstname	ComesFrom__c	LivesIn__c
1	Andrew	UK	UK
2	John	USA	Australia
3	Andrew	UK	USA
4	Slater	Australia	Australia

Imagine having a List of inputCriteria and we need to return the country where the person is living, but we are interested only in certain people and only in people who no longer live in the country where they were born.

List<Person__c> people = new List<Person__c> {
    new Person__c(Firstname__c = 'Andrew', comesFrom__c = 'UK'),
    new Person__c(Firstname__c = 'John', comesFrom__c = 'USA')
};

The Capabilities of SOQL would only allow us to perform a query based on all unique list items:

List<Person__c> people = [
    SELECT Id, Firstname__c , ComesFrom__c, LivesIn__c
      FROM Person__c
     WHERE Firstname__c IN ('Andrew', 'John') AND
           ComesFrom__c IN ('USA', 'UK')
];

This is a common scenario where developers opt to pull way too many records from the database into memory and filter them out in Apex code. This is bad in several ways: it reduces performance, increases the memory footprint and greatly increases the chance of breaching the governor execution limits.

A formula field, appropriately constructed and used in a SOQL query would mitigate and/or eliminate deficiencies of filter-it-in-Apex approach. In our example, a formula field would look something like this:

SearchKey_frm__c :

IF(
    ComesFrom__c = LivesIn__c,
    "",
    Firstname__c & "-" & ComesFrom__c
)

If we now change the query to filter by the key we will be able to filter down the records to precisely those that we need.

List<Person__c> people = [
    SELECT Id, Firstname__c , ComesFrom__c, LivesIn__c
      FROM Person__c
     WHERE SearchKey_frm__c IN ('Andrew-UK', 'John-USA')
];

3. Single Point of Entry

Let’s imagine a scenario where you need to process a collection of opportunities. Depending on the Opportunity Recordtype, you would need to send an email either to the Account’s email address, or to the Contact’s email address. Commonly the developer would do something like this:

List<Opportunity> oppList = [
    SELECT Id, RecordType.DeveloperName, Account.Email, Contact.Email
      FROM Opportunity
     WHERE RecordType.Developername IN ('Person', 'Company')
];

for (Opportunity o : oppList) {
    if (o.RecordType.DeveloperName == 'Person')
        sendMail(o.Contact.email);
    else
        sendMail(o.Account.email);
}

This example does not look too complicated, but imagine more complex scenarios and you can immediately see how the depth of the decision tree can blow out of proportion. This can greatly complicate the code involved but also the unit tests involved, where making small tweaks can immediately make this code more readable only by switching this decision over into a formula field.

Email_frm__c :

CASE(
    RecordType.Developername,
    "Person",  Contact.email,
    "Company", Account.email,
    ""
)

List<Opportunity> oppList = [
    SELECT Id, Email_frm__c
      FROM Opportunity
     WHERE Email_frm__c != null
];

for (Opportunity o : oppList) {
    sendMail(o.Email_frm__c);
}

As you can see, the small piece of code became even simpler in every way, but the added benefit is that you can now also show this value on the Layout to the user, without them having to know about that rule or any drilling into parent records.

4. Hyperlinks

In the previous scenario we have shown how to utilize a formula as a single point of entry, for multiple fields from different objects. A neat way to make it easier to the user would be to create, in the same way, a Formula field that would generate a hyperlink to the parent record in question:

Client_frm__c :

CASE(
    RecordType.Developername,
    "Person", HYPERLINK("/" & CASESAFEID(ContactId), Contact.Name, "_self"),
    "Company", HYPERLINK("/" & CASESAFEID(ContactId), Contact.Name, "_self"),
    ""
)

Using this approach you can define a Hyperlink that will always bring you to the appropriate client record without having to look around the user interface and explain the logic in question to the user.

5. Unique Key Constraints

Very often we have to define a unique key or an external Identifier field which is concatenated from several fields. You would store it as a value into a Text field while actually performing the concatenation in either a Process Builder Flow or in Apex Code. While Apex code would imply the need to write a unit test for this case, Process Builder Flow implies the dreaded and hated cloning of processes in the case that the Key structure needs a simple tweak.

Here we can make use of the Formula field again by having the Key structure concatenated inside of it.

UniqueKey_frm__c :

Market__c & "-" & Year__c & "-" & Type__c

The step of populating the text field value would still remain a necessity of the Process or the code since Formula Fields don’t support uniqueness, but in the case of a key structure change, it is no longer required to clone the process or change any code. You would simply need to change and deploy the formula.

All of these examples of a formula field usage solve different problems or help simplify the development process. In a traditional database we are not required to do that because we can perform calculations directly in queries, but then again we will probably have, in that same database, a dozen of queries that contain the same concatenation, while in Salesforce we can always reduce it to the same formula and use it as any other attribute.

If you have other great uses of Formula fields, please feel free to let us know.

SalesforceFormulaSalesforce administratorconfigurationCRM

Ivan Kljaic