Use Formulas to Solve Problems in Salesforce.com

Formulas are incredibly useful in overcoming the challenges you face as an Admin, Report Writer, Customizer or Developer of a Salesforce system. Learning how to use them can seem daunting; however, through practice and by leveraging the work of others, you can progress quickly. Below is a quick overview overview of formulas, suggestions for useful resources, and a recent example from the trenches of report writing.

In its Help documentation, Salesforce defines:

A formula is an algorithm that derives its value from other fields, expressions, or values. Formulas can help you automatically calculate the value of a field based on other fields.

Formulas can be used in many places in the system, such as Reports, Custom Fields, Workflow Rules, Approval Processes, Validation Rules, Custom Buttons/Links and so on. Since ’08 we’ve been able to write Cross Object Formulas and can now leverage Lookups to reach across up to 10 related objects to pull in a data value to use in a formula. Similar to in Excel, our ability to understand and use “functions” and “operators” is central to advancing as a formula writer.

My advice on getting started with formulas is to look at the Examples of Advanced Formula fields within Salesforce Help.  Also, there is considerable help built into the formula editor in Salesforce, such as examples of the use of functions and operators, and a built in Check Syntax feature. It is critical to test all conditions of your formula on real data- we often use Views or Reports to see formulas in action across many records.

Here is an example of a recent formula we used to solve a specific problem:

Goal: Create a report showing data by month for a rolling Current and Previous 2 Quarters.

Challenge: Report filters don’t give us an easy way to dynamically pull in records by quarter beyond “Current and Previous FQ”. Using a report filter such as LAST 270 Days cuts into the middle of the month and isn’t precise- we need something that flexes depending on whether we are in the first, second or third month of the current quarter (meaning that the formula needs to include the current and past 6, 7 or 8 months respectively). Our date should always compare greater than equal to the first day of the month those many months ago.

Solution: Create a hidden custom formula field on the object which compares Today’s Date and the value in a Date field on the record, such as the Create Date of the records. The following formula returns “True” is the Opp was created in the Current or Prior 2 Quarters, and False if Created Date is prior to that. We can now create a report which filters on a TRUE value in this formula to determine which records fall into our rolling Current and Previous 2 Quarters. Notes: a) This formula assumes use of calendar quarters. b) The else_result in this formula will always return FALSE, but, given system constraints on the CreatedDate field and our predictable 12 months in a year, should never arise.

Formula:

IF(
   DATEVALUE(CreatedDate) >=
   CASE(
      MONTH(TODAY()),
      1, DATE(YEAR(TODAY())-1,7,1),
      2, DATE(YEAR(TODAY())-1,7,1),
      3, DATE(YEAR(TODAY())-1,7,1),
      4, DATE(YEAR(TODAY())-1,10,1),
      5, DATE(YEAR(TODAY())-1,10,1),
      6, DATE(YEAR(TODAY())-1,10,1),
      7, DATE(YEAR(TODAY()),1,1),
      8, DATE(YEAR(TODAY()),1,1),
      9, DATE(YEAR(TODAY()),1,1),
      10, DATE(YEAR(TODAY()),4,1),
      11, DATE(YEAR(TODAY()),4,1),
      12, DATE(YEAR(TODAY()),4,1),
      DATE(YEAR(TODAY())+1,1,1)
   ),
   "TRUE",
   "FALSE"
)

Three other formula related posts on our site you might be interested in include:

• Get Time Duration in “Days: Hours: Minutes” format! 
• SFDC Formula Field – Today’s date plus one year
• Salesforce Cross-Object Formulas: This is Not the $User You Are Looking For

Photo credit: wburris