Supercharge your Salesforce: Formula Fields

Share this article...

Salesforce Formula Fields can have a huge impact on your Salesforce Instance and can provide many unrealised, useful bits of information around any object you can place them on. They can have a number of applications such as –

Perform Calculations between multiple number fields

Display data on a child object from the parent object

Concatenate and manipulate fields

Calculate a certain date between two date fields

Display different images if a certain criteria is true (e.g. Traffic Light)

The bottom line is, you can get a great deal of new unrealised information from data that already exists on your records.

Formula fields have slightly different characteristics from a normal field, they are un-editable and their values are set behind the scenes in the Object using the formula editor. Formula fields are very easy to get to grips with and use logic as opposed to APEX code or any other developery language. If you can handle basic function in an excel sheet, then you can handle formula fields.

Creating a Formula Field

Formula fields are created like any other field by clicking New in the field setup of your object and selecting formula. You are however faced with what type of formula field you wish to create.

Depending on the type of formula field you choose, will depend on the kind of output you are allowed to produce. For example if you choose a number field, you will not be able to introduce date fields into this for a date output. The same as if you choose a Date field but want to use time values, this will not be allowed.

Layout & Features

After you proceed to the next step you will be faced with the formula creation page (I would always advise clicking on the Advanced Tab). There are various features you can take advantage of here to build simple and complex formulas to display different sets of data on your object. Learning formulas and logic in Salesforce is a very good skill to have as they can be used in various other places like Workflows and Validation Rules.

Insert Field – Insert fields from your object and parent objects here. Standard field syntax look like the Amount field, custom fields will always have “__c” appended to the end.

Functions –Functions allow you to transform data, perform calculations or test data in a more comprehensive way than you can with operators. For example a common Function is an IF statement, test some data e.g. “IF Amount > 1000”, display some data if True and some different data if False

Examples

Calculate basic commission on Opportunities

Formula – IF(Amount < 10000, Amount * 0.10, Amount * 0.15)

A simple IF statement that looks if the Opportunity Amount is less than 10k, if so the commission is 10%, if it is greater, the commission is 15%. This calculates and displays that commission

A basic Lead score formula that looks at whether fields are blank or not and assigns them a score if not. We can then add these together. Please note the ISPICKVAL function that is necessary if you are using a Picklist field.

Here’s a great example of a formula field assigning, West, Central, East, South, North or Other based on the state. This was taken from Salesforce Example page.

Using Images in Formula Fields

One of the best things about Formula Fields is the ability to display images as an output. So in the example above with the Lead Score, instead of outputting a number, we could output an image to display how “Hot” the lead is.

Salesforce has a bunch of images that can be used straight out of the box. You can grab a whole list of them here (You will need to remove some of the URL as instructed). There is also an AppExchange pack you can install to get more.

So carrying on the example with Lead score, I’ll show you a formula with images.

Using a CASE statement (Similar to and IF statement but can have more than three outcomes), we can look at our Lead Score field and assign an Image URL using the IMAGE function. If the score is 10, assign a red traffic light, if 20, assign a yellow and if 30, assign the green. Easy!

SUBSCRIBE TO OUR MONTHLY NEWSLETTER

Connect with us

Address

Salesforce Ben
199 Bishopsgate
London
EC2N 4AG

Interested in guest posting?

Have something to share? Want to tell your story? We are always on the hunt for writers that interesting to say about the Salesforce platform and ecosystem. Join our group of 120+ trusted guest posters – Click here to start the conversation.