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.
Operators – Operators allows you to test and evaluate data in different ways. E.g. 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. 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
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
Calculate a Lead Score
Formula – (IF( AnnualRevenue = 0, 0, 10)) + (IF(ISPICKVAL(Industry, “”) , 0, 10)) + (IF( MobilePhone = “”, 0, 10))
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.
Calculate an Accounts Region
Formula – IF(ISBLANK(BillingState), “None”, IF(CONTAINS(“AK:AZ:CA:HA:NV:NM:OR:UT:WA”, BillingState), “West”, IF(CONTAINS(“CO:ID:MT:KS:OK:TX:WY”, BillingState), “Central”, IF(CONTAINS(“CT:ME:MA:NH:NY:PA:RI:VT”, BillingState), “East”, IF(CONTAINS(“AL:AR:DC:DE:FL:GA:KY:LA:MD:MS:NC:NJ:SC:TN:VA:WV”, BillingState), “South”, IF(CONTAINS(“IL:IN:IA:MI:MN:MO:NE:ND:OH:SD:WI”, BillingState), “North”, “Other”))))))
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.
Lead Score Image
Formula – CASE( Lead_Score__c , 10 , IMAGE(“/img/samples/light_red.gif”, “light”), 20,IMAGE(“/img/samples/light_yellow.gif”, “light”) ,30 ,IMAGE(“/img/samples/light_green.gif”, “light”), “”)
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!