Formula Fields in Salesforce

by | Dec 31, 2021 | Salesforce Administration, Starter Administration

Salesforce formula fields are a powerful tool in your Salesforce administration toolbox. Mastering formula fields and their many applications helps with considering logic, operators and functions in other areas of declarative configuration, such as Process Builder and Flow.

Formula Fields are NOT data! Formula fields make use of existing data and operations to perform a calculation and return a result. They are executed only at run time. What that means is a formula field calculates when you view a record page or run a report.

If you have a lot of formula fields that execute at the same time, this can slow down the loading of a record page or a report. 

Formula fields reflect the status quo of the calculation at the time it is run. It is possible to time bound a formula field – for example indicating that a record was open during Quarter 1 of 2021.

Users can view formula fields on record pages but can’t see the underlying calculation or edit the value.

Formula Field Data types

There are a number of formula field data types. These are the formula fields I use the most:

1. Checkbox

This calculates a boolean value. Use an if statement to determine whether the checkbox is ticked or, alternatively use a statement: “when this is true – tick me”.

Examples

  • Record owner is active
    Owner.IsActive
  • Current user the record owner
    $User.Id = Record.OwnerId
  • Current user is manager of the record owner
    $User.Id = $User.ManagerId
  • Series of nested IF statements that indicates if the Contact has been identified as either British, French, Polish or South African in a custom Nationality field:
    IF(ISPICKVAL( Nationality__c , “British”), true,
    IF(ISPICKVAL( Nationality__c , “French”), true,
    IF(ISPICKVAL( Nationality__c , “Polish”), true,
    IF(ISPICKVAL( Nationality__c , “South African”), true,
    false) ) ) )

Checkbox formula fields are handy for a visual boolean True/False value on a record or report and can serve as a filter for a report or list view.

2. Date and Date/Time

These fields always return a date, date/time or time as GMT values. In the user interface the date and date/time formula fields display in the time zone of the user viewing it. Be prepared to adjust accordingly.

  • Date:
    Stores a year, month and day
  • Date/Time:
    Stores a year, month, day and a time
     
  • Time:
    A time from a 24 hour clock. This field can’t be used to return the time gap between two dates or times. It is a clock (and stores time according to GMT).

Remember that GMT value:

  • Converting Date to Date/Time will always begin at Midnight GMT 00:00. It will always return the time in GMT not the timezone of the org.
  • Always be mindful of any daylight saving that happens in your timezone.

Examples of Date Formula

  • If formula to replace a blank date field with the Created date:
    IF ( ISNULL( Date__c) , DATEVALUE(CreatedDate) , Date__c )
  • Setting an expiry date for a product or item (where one exists) or an alert based on a picklist field called Expiry Alert (where an expiry date does not exist):
    IF( NOT( ISBLANK( Expiry_date__c ) ) ,  Expiry_date__c ,
    CASE( Expiry_Alert__c ,
    “7 days”, Date__c + 7,
    “2 weeks”, Date__c +14,
    “1 year”, Date__c +365,
    null))

Date and Time Functions are super useful to keep in mind. As demonstrated above, math operators such as + and – can be used to count or take away days. Additional functions that supercharge your date formula fields include: 

  • AddMonths:
    Adds months to a date
  • DATE:
    Returns a Date from a Year, Month and Day that you enter.
  • DATEVALUE:
    Returns the Date value of a Date/Time field
  • DATETIMEVALUE:
    Returns a Date as a Date/Time 12.00am GMT value converted to the the viewing user’s timezone
  • DAY
    Returns the Day of the month from a Date value
  • TODAY()
    Returns the current Date as a value
  • NOW()
    Returns the current Date and Time as a value.
  • WEEKDAY
    Returns the weekday as a number from one (Sunday) to 7 (Saturday).

A neat international date formula that I will forever be grateful for due to a colleague called Gail is:

TEXT( YEAR( TODAY() ) ) & “-” & LPAD( TEXT( MONTH( TODAY() ) ), 2, “0”) & “-” & LPAD( TEXT( DAY( TODAY() ) ), 2, “0”)

This returns a text formula of the date which is useful if you are generating PDF’s from the record: 2021-05-31 in the format.

3. Number, Percent and Currency

Number: A number formula field can be used to calculate a positive or negative number, either integer or decimal.

Percent: Similarly a Percent formula field is a number field but a number with a percent sign, stored as a number divided by 100.

Currency: Also a number field – but it comes with a Currency Sign. The formula field is not associated with any particular currency. NB: If multi-currency enabled, the currency formula displays the currency of the associated record.

The Power of one! A useful formula for reporting. It is a field that just returns the number 1.

A number field can only return a number. Sounds so simple! A number field can return a number from any type of calculation, such as here: 

Time spent in hours

  • IF( ISBLANK( End_date_time__c) , 0 , ( End_date_time__c – Start_date_time__c ) * 24 )

Time spent minutes

  • IF( ISBLANK( End_date_time__c) , 0 , ( End_date_time__c – Start_date_time__c ) * 24 * 60 )

To get a number from a picklist field value stored as “1 Picklist Value”:

  • IF (ISBLANK(TEXT( Picklist_Field__c )),0,
    VALUE(TRIM(LEFT(TEXT(Picklist_Field__c) ,2))))

Total invoice arrears for a currency field:

  • Owed__c + Payments__c + Written_off__c + Refunded__c

Currency, Number, Percent operators and functions include

  • Math Operators: +, -, *, /
  • Logical Operators: eg =, <, >, <=, >=
  • Maths Functions: eg CEILING, FLOOR, ROUND

4. Text

The sovereign of the formula field types. Text can be used to concatenate text values, convert numbers and dates to text, display text conditionally, search for strings in a text field and show images!

Here is an Image function which returns an image with alternative text (alternative text is key for accessibility):

IMAGE( “/resource/1435223689000/nameofimage” , “Alternative Text” )

For the IMAGE function – it’s best to store the image as a static resource as I have in the example. If you are not sure what a static resource is, see this Salesforce Help Link.

I find there is always a need for traffic lights on a record in a Salesforce org for example, indicating data quality, or a graphical representation of a rating, etc. The example below is evaluating a number field:

  • IF( NumberField__c < 5, IMAGE(“/resource/435678456/RedTrafficLight”, “Red Traffic Light”),
    IF( NumberField__c < 10, IMAGE(“/resource/435678456/AmberTrafficLight”, “Amber Traffic Light”),
    IMAGE(“/resource/435678456/GreenTrafficLight”, “Green Traffic Light”))

Text formula fields also enable you to concatenate text values together, convert numbers and dates to text, display text conditionally, search for strings in a text field.

Concatenate function becomes particularly useful if you would like to have the second field in a search layout useful for the person searching records.

For example combining an Account, Billing Country, and Industry 

  • Name & ” – ” & BillingCountry & ” (” & Industry & “)”

Or another example for showing the full Created by name

  • CreatedBy.FirstName & ” ” & CreatedBy.LastName

Note:
You can’t use long text area, encrypted, or Description fields in formula fields. However – you can look in that data. Functions that help you to search or data mine in such fields are: begins, contains, and find.

The pitfalls to data mining is that the users entering data in the fields must be disciplined with terminology. It’s all very well searching for “Mental Health” as a flag for mental health issues when a user could have written “No Mental Health Issues”. The formula will capture this text. Oh and unless you convert all text to either lower text or upper text in the formula all text searches are case sensitive/ The search term “Mental Health” will not find “mental health”.

Another useful text formula field function is CASESAFEID. CASESAFEID converts a 15-character Salesforce ID to a case insensitive 18-character ID

The Hyperlink Function – creates a link to a URL specified that is linkable from text specified. This can be used in list views to return a more recognisable link to a record than an auto-number. For example, with the Case Object, the Cases Name field is an auto-number. To swap this out in list views and have a more user friendly reference to click on to open the record, create a formula field that returns a hyperlink pointing to the Record Id of that case.

5. What about the other formula field data types?

That will be the email, phone and URL. They do what they say!

You can use them with logical operators and functions.

A note about referencing Picklists and Multi-Select Picklists:

Picklists and Multi-Select Picklists have specific functions in order to use their values in formula fields.

  • ISPICKVAL = Is used to query values in picklist fields:
    ISPICKVAL(PicklistFieldName__c, “Value I am looking for”)

You need to specify the value amongst the options that you wish to reference.

  • INCLUDES = Is used to query values in multi-select picklist fields:
    INCLUDES(MultiselectFieldName__c, “Value I am looking for”)

    You need to use includes as the value you wish to reference could be by it self or one of many selected.

Useful functions!

  • TEXT = changes a date or picklist/multi-select picklist as a text value. It can open up these fields to other functions.
  • ISBLANK = Is this field empty? This can only be used with picklist and multi-select picklists if we surround the field with TEXT first.
  • NOT or ! = Essentially give me the opposite of the function that is inside the NOT or ! parentheses:
    NOT(ISBLANK(FieldName__c))

Cross Object Formula Fields

These have been dealt with in a separate post.

If and Case

In the examples above both If and Case were used. These two functions are all about logic! Both work on a if not this it’s that premis. Just remember what the return/output is to be for the type of formula field.

  • In a simple situation, a single IF function will work: IF(logical_test, value_if_true, value_if_false), for example:

    IF(ISPICKVAL(Sky__c = “Blue”) && Daytime__c = TRUE, “Go Outside”, “Stay In and read book”)

    A logical test can be one argument, or more as above.
  • If more there is more than one possible result for the logic you want to represent, use the  Case Function.
    CASE(Weather__c,
    “Clear”, “Go Outside”,
    “Overcast”, “Go Outside”,
    “Raining”, “Go Outside with a Coat on”,
    “Hurricane”, “Stay in the Shelter”,
    “Stay in and read a book”)
  • If more complicated branching logic is needed, I tend to use Nested IF functions. The order is important – once an argument is reached the formula field will return the first IF Statement that is TRUE. Be specific and make sure you cover all scenarios. The below formula would produce the wrong result:

    IF(Daytime__c = TRUE, “Go Outside”,
    IF(ISPICKVAL(Weather__c = “Hurricane”) && Daytime__c = TRUE, “Stay in the Shelter”, “Stay in and sleep”)

    Firstly, if it is daylight, the first IF statement will be true regardless of the weather, so the formula field will return a value of “Go Outside”.
    Secondly, if it is night time, and there is a hurricane, the formula field would return the value “Stay in and sleep”. This, like the first scenario, depending on where you are living could be deadly advice.

A note about Character Limits

Each formula field has a 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.

Watch out for the Save Size and Compile Size limits. 

  • For the save size limit, formulas cannot exceed 4,000 bytes when saved. The save size is different from the number of characters you have used in your formula.
  • For the 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 other formula fields 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.

Ending Notes:

  • The maximum number of displayed characters after an evaluation of a formula expression is 1,300. Sadly (or happily), formula fields can’t return an essay.
  • The value of a field can’t depend on another formula that references it. This makes sense – after all if the formula fields only represent a calculation of data referenced elsewhere, if they reference each other, they have no data to calculate a value from.
  • Fields referenced in formula fields cannot be deleted. They have to be removed from the formula field first.

I hope that this has given you some ideas and help (if needed) for formula fields and how you could use them. 

Share This