Use Logic in Formulas

Learning Objectives

After completing this unit, you’ll be able to:

• Use the logic functions in the formula editor.
  
• Explain the IF function.
  
• Create a CASE function in a formula.
  

Let’s Get Logical

In the previous unit you were asked for a field that shows how many days a case is open. You created a formula that subtracts the Date/Time Opened from the current date. That’s fine if the case is still open, but what if the Case is already closed? Then you would need to subtract the Date/Time Opened from the Date/Time Closed. What we need here is some logic.

In this unit you use the IF function to determine whether a Date/Time Closed exists. If it doesn’t, you use the current calculation, but if it does, you need a new calculation.

The IF function determines if an expression is true or false. You decide what value to return if it’s true and what value to return if it’s false. The syntax for the IF function is: IF(logical_test, value_if_true, value_if_false). So the IF function takes three inputs. First, logical_test is the conditional expression that needs to be evaluated. Second, value_if_true is the value you want if it’s true, and finally, value_if_false is the value you want if it’s false.

1. From Setup, open the Object Manager and select Case.
   
2. Select Fields & Relationships.
   
3. Select Days Open.
   
4. Click Edit.
   
5. Change the Description to Number of days the case was or has been open.
   

Let’s update the formula with some logic.

1. Click Advanced Formula.
   
2. In the formula editor, place the cursor at the beginning of the formula, just in front of TODAY.
   
3. From Functions, select IF and then click Insert Selected Function.
   Your formula should be:
   IF(logical_test, value_if_true, value_if_false) TODAY() - DATEVALUE( CreatedDate )

Okay, the IF function is now inserted. Let’s define the logical_test to determine whether Date/Time Closed is blank.

1. Delete logical_test.
   
2. From Functions select ISBLANK and click Insert Selected Function.
   Your formula should be:
   IF( ISBLANK(expression) , value_if_true, value_if_false) TODAY() - DATEVALUE( CreatedDate )
3. Replace expression (the ISBLANK parameter) with the Date/Time Closed field.
   Your formula should be:
   IF( ISBLANK( ClosedDate ) , value_if_true, value_if_false) TODAY() - DATEVALUE( CreatedDate )
   
4. Replace value_if_true with the original formula by cutting and pasting TODAY() - DATEVALUE( CreatedDate ).
   Your formula should be:
   IF( ISBLANK( ClosedDate ) , TODAY() - DATEVALUE( CreatedDate ), value_if_false)

Now all you need is the value for when the Date/Time Closed has been set (it’s not blank). Subtract the Date/Time Opened from Date/Time Closed. Oh, and don’t forget to format the result as a date value.

1. Replace the value_if_false parameter with the DATEVALUE function.
   
2. Replace expression (the DATEVALUE parameter) with the Date/Time Closed field.
   Your formula should be:
   IF( ISBLANK( ClosedDate ) , TODAY() - DATEVALUE( CreatedDate ), DATEVALUE( ClosedDate ) )
   
3. Place the cursor immediately before the final parenthesis and insert the - Subtract function.
   
4. After - Insert the DATEVALUE function.
   
5. Replace expression (the DATEVALUE parameter) with the Date/Time Opened field.
   Your formula should be:
   IF( ISBLANK( ClosedDate ) , TODAY() - DATEVALUE( CreatedDate ), DATEVALUE( ClosedDate ) - DATEVALUE(CreatedDate))
6. Click Check Syntax.
   
7. Click Save.
   

You did it! The service team is ecstatic about the new Days Open field. They’ve already created reports to show how they’re minimizing how long cases remain open.

When you look at opportunity records in the playground, you’ll notice that the close dates are prior to the created dates, resulting in negative numbers for Days Open. To see positive numbers, update the Closed Date to a date after the Created Date.

The Case for CASE Functions

Remember the Commission field you created on Opportunities for the sales team? They love it! But… they want to add a scale to the allotted commission. You can do that right? Of course you can. Here’s the commission table.

This is a great case for the CASE function. The CASE function checks a given expression against a series of values. If the expression is equal to a value, it returns the corresponding result. If it isn't equal to any of the values, it returns the else_result. The syntax of the CASE function is:

CASE(expression,value1, result1, value2, result2,..., else_result)

You’ll update the Commission field’s formula so that it compares the opportunity amount to a series of values representing each commission level, and then applies the corresponding commission percentage.

Start by determining which commission percentage should be applied based on the opportunity Amount. You can determine the 4% and 8% commissions with a single IF function. For the 6% commission, however, you need to identify a middle range using the AND function within the IF function. The AND function allows you to verify the greater than $50,000 and less than $150,000 amount. The AND function syntax is AND(logical1,logical2,...). It determines whether multiple logical expressions are true or false. It returns TRUE if all values are true, or FALSE if any value is false.

Each IF function is returning a value of true or false, but there’s a hitch. The CASE function doesn’t accept true and false as values. The IF function must return a valid value, such as a number. So we’ll use 1 for true and 0 for false. The CASE function compares each case’s value to the CASE expression, which you’ll set to 1 (for true). When an IF function returns 1, because the opportunity Amount fits in the defined range, then the CASE function returns the corresponding commission (the Amount multiplied by the Commission percentage) as the result.

After you’ve used the IF function to match the right commission value to the amount, those IF functions become values and the commissions become results in the CASE syntax: CASE(expression, value1, result1, value2, result2, ..., else_result)

We’re going to need three options for the commission rates. So you can use value1, value2, and else_result. You won’t need the extra value results indicated by the ..., between result2 and else_result so remove ..., and you have: CASE(expression,value1, result1, value2, result2, else_result)

In the CASE function, the values are the IF functions and the results are the commission calculations. The CASE function is easier to read and understand when each value and result pair is on its own line. Construct the formula following the CASE syntax like this:

Pieced together, the full formula is:

CASE(1,
  IF(Amount <= 50000, 1, 0), Amount * 0.04,
  IF(AND(Amount > 50000, Amount <= 150000), 1, 0), Amount * 0.06,
  Amount * 0.08
)

To update the opportunity’s Commission formula:

1. From Setup, open the Object Manager and select Opportunity.
   
2. Select Fields & Relationships.
   
3. Select Commission.
   
4. Click Edit.
   
5. Change the Description to Varied commission based on Amount of opportunity.
   

Let’s add the CASE function to the formula.

1. From Advanced Formula, clear the formula editor so you can start with a blank formula.
   
2. From Functions, insert the CASE function.
   Your formula should be:
   CASE(expression, value1, result1, value2, result2,...,else_result)
   
3. For the CASE parameter expression, enter 1.
   
4. For the value1 parameter, insert an IF function.
   Your formula should be:
   CASE(1, IF(logical_test, value_if_true, value_if_false), result1, value2, result2,...,else_result)
   Let’s separate the cases to their own lines to make it easier to read.
   

   CASE(1,
     IF(logical_test, value_if_true, value_if_false), result1,
     value2, result2,
     ...,
     else_result
   )

5. Replace logical_test (the IF parameter) with the Amount field, followed by the Less Than or Equal operator, followed by 50000.
   
6. Replace value_if_true (the IF parameter) with 1 and for value_if_false enter 0.
   The formula’s first case should be:
   

   CASE(1,
     IF(Amount <= 50000, 1, 0), result1,

   
   
7. For result1 insert the Amount field and multiply it by 0.04 to calculate the 4% commission.
   The formula’s first case should be:
   

   CASE(1,
     IF(Amount <= 50000, 1, 0), Amount * 0.04,

   
   

The value2 needs to evaluate two conditions: Amount is greater than $50,000 and Amount is less than $150,000. To evaluate the two options you use the AND function.

1. For the value2 parameter, insert an IF function.
   
2. Replace logical_test with the AND function.
   The formula’s first and second cases should be:
   

   CASE(1,
     IF(Amount <= 50000, 1, 0), Amount * 0.04,
     IF(AND(logical1,logical2,...), value_if_true, value_if_false), result2,

   You only need two of the logical options in the AND function, so remove the final ,... .
   

   CASE(1,
     IF(Amount <= 50000, 1, 0), Amount * 0.04,
     IF(AND(logical1,logical2), value_if_true, value_if_false), result2,

3. Replace logical1 (the AND parameter) with the Amount field, followed by the Greater Than operator, followed by 50000.
   
4. Replace logical2 (the AND parameter) with the Amount field, followed by the Less Than or Equal operator, followed by 150000.
   
5. For value_if_true (the IF parameter) enter 1.
   
6. For value_if_false enter 0.
   
7. For result2 insert the Amount field and multiply it by 0.06 to calculate the 6% commission
   Your formula should be:
   

   CASE(1,
     IF(Amount <= 50000, 1, 0), Amount * 0.04,
     IF(AND(Amount > 50000, Amount <= 150000), 1, 0), Amount * 0.06,
     ...,
     else_result
   )

Now for the else_result. Because it’s the default when the other values don’t match the CASE expression, the else_result is just the last commission rate calculation. Delete the ..., before the else_result and then build the final CASE result.

1. Replace else_result with the Amount field, followed by the Multiply operator, followed by 0.08.
   Here’s the final formula:
   

   CASE(1,
     IF(Amount <= 50000, 1, 0), Amount * 0.04,
     IF(AND(Amount > 50000, Amount <= 150000), 1, 0), Amount * 0.06,
     Amount * 0.08
   )

2. Click Check Syntax.
   
3. Click Save.
   

You can go back and look at the Opportunities list to see the updates to the commissions. The sales team is super impressed once again. If they decide to establish more commission rates, you’re ready to add them at any time.

Now that you’ve wrangled some formulas with fields, operators, and some functions, you have the basic skills to create almost any formula you can imagine. You can generate all kinds of data insights using formulas. You’ve only scratched the surface here.

Resources

• Salesforce Help: CASE
• Salesforce Help: AND