What comes to your mind when you hear the word “formula”? If you are anything like my 13-year old daughter, the quadratic formula and other dreaded mathematical functions come to mind. While formulas in Salesforce can be used to do calculations, that’s just the tip of the iceberg. Below are five cool things that you can do with formulas.
Keep Data Clean
Is poor data derailing your marketing efforts and making your reporting impossible?
Hey – I’ve been there and feel your pain. Fortunately, formulas in validation rules can save the day!
Here’s a good example. Let’s say that you are in charge of marketing a product to individuals in the USA and Canada and that state or province is needed for your marketing segmentation. Your organization also does some business in other parts of the world, so state is not required when entering leads into Salesforce. Formulas to the rescue!
The simple formula below checks the country of leads upon entry and will not let the record be saved if the country is USA or Canada and the state field is blank.
Nothing too fancy, but effective. When users try to save the record with missing data, they are greeted with a friendly reminder at the top of the page.
Build Picklist Dependencies
One of my favorite Pardot form functions is dependent fields. This feature allows you to shorten forms and only show select fields based on the response to a dependent field. You can build very similar dependencies in Salesforce using formulas.
Let’s put our marketing hat back on for a moment and consider the situation below. As the marketing leader of an organization, you know the personas of your customers quite well and have determined that job function is one of the most critical factors in identifying future buyers. Based on customer analysis, you have created picklists in Salesforce to capture this information in a consistent manner when leads are entered (nice job!). You also had the foresight to include “other” as an option in your picklist to capture titles that may not be in your list or may be associated with future product releases.
After creating “Job Function” as a required field (with picklist values) and creating a non-required “Other Job Function” field, the formula below can be added as a validation rule. This rule will require that the “Other Job Function” field be completed prior to records being saved if the “Other” option is selected in in the picklist.
Here’s the error message that users will see if “Other” is selected and the “Other Job Title” field is not populated.
If your Salesforce org is any like the ones that I work in, there is a lot of data on your lead and contact page layouts. While the data is needed and serves a purpose, finding key information can often be a challenge. We’ve all heard the expression “a picture is with a thousand words” – you can leverage this concept by adding images with formulas.
The example below is used to apply visual indicators based on lead rating. If the lead is Hot, five stars are displayed. If the lead is Cold, only one star is displayed.
One of our core values at Secante is to “simplify complexity” and formulas can help us do that too. Sometimes we don’t need all the data presented on a page layout. The information needed can be conveyed through the humble checkbox.
Let’s say that your organization has a list of target accounts that are treated differently than other accounts. Asking the sales team to memorize a list of accounts that may be in a state of perpetual change, is just not scalable. However, the addition of a simple checkbox on the contact page layout to serve a visual indicator would easily address the need.
Simplify Other Formulas
We’ve started off with some pretty simple formulas, but the reality is that formulas in the real world tend to get much more complex. In this example from the Advanced Admin Trail, a text formula named Case_Category _ _c was created to color code cases based on the length of time that they have been open.
While technically accurate, this formula has a lot of redundant components. Sticking with the “simplify complexity” theme that we mentioned earlier, a helper formula can be used to make this formula a bit more efficient.
In this case, the logic for calculating the case’s age can be defined in another formula called Case_Age_In_Days_ _c. We can then insert this formula field into our original formula and eliminate the need to do the age calculations within the “IF” statements. The result is a streamlined formula that’s much easier to read and understand.
Check out these great resources from Salesforce to build and expand your formula skills.
- The Advanced Formulas Trailhead module is a great place to start.
- The Formula Operators and Functions document is an excellent reference guide. It lists out all the formula operators and provides descriptions of what they do.
- Salesforce has also compiled an extensive list of Examples of Advanced Formula Fields. This is a great resource for addressing some of the more complicated formulas that you’ll encounter.