[SalesForce] CASE formula evaluates all expressions when ‘otherwise’ condition is true

I have a case statement that looks like this in a formula variable:

    Case (ContractType, 
'Value 1', (VarA / VarB),

'Value 2', (VarA / VarB), 

 VarC / VarA
)

where VarA, B & C are numeric (dollar amounts).

Now the problem is that if the ContractType is not Value 1 or Value 2 then VarB is going to be blank or zero but in any case i want & expect the last statement to execute so it should not matter that varB is zero or null.

However, it seems that even though the last statement might, in a given situation, be the correct statement to execute, the formula VarA / VarB is still evaluated which results in an error and the formula does not return the correct result.

If I re-structure the statement to use 'If's instead of a 'Case' then it works fine. However i prefer to use Case as it is easier to write and more readable, especially as there are quite a few 'Value' statements involved in my real life scenario.

Is there a way to make the Case statement work correctly even if a value used in a part of the case statement that does not (or should not) get executed has a zero or null in it?
cheers.

Best Answer

Typically in a formula, one evaluates null using BLANKVALUE or ISBLANK, at least that's the way the Documentation recommends.

I would recommend you use a combination of IF-ELSE logic along with CASE. From your description, it sounds as though VarB is the only one that can evaluate to zero, null or empty that's in a denominator of your formulas. If that's not true, then you'll want to modify what's below to add the other variables that can also have the same conditions. Should that occur, you may need to nest a 2nd CASE statement inside it to cover situations where any of those variables are in the numerator instead of the denominator. That said, I'd expect that any zero or null values would be handled the same by your "default" or "bailout" CASE; making that unnecessary.

You didn't say what the use case was the formula results are applied to so I'm just providing the formula in the context of the question.

EDIT

IF(!(BLANKVALUE(VarB) || VarB == 0), 
CASE(ContractType, 
'Value 1', (VarA / VarB),
'Value 2', (VarA / VarB),     
 VarC / VarA
),
'Value3' or other formula applied when VarB = 0)

I edited the code above to fix an error in the way BLANKVALUE was applied. Second, in response to your comments, I want to explain how the code above works.

In a formula, an IF statement is structured like the following. There's boolean test condition that's evaluated followed by a comma. Then there's code that's applied if the boolean evaluates to 'true' (again followed by a comma). Many will often use a fixed value here, but it can be more code instead such as a CASE statement. If the condition evaluates to 'false', then any code following the 2nd comma is run. Again that code can simply be the assignment of a fixed value or as opposed to another expression that gets evaluated.

An If statement looks like the following: IF(boolean condition, 'true', false). What I did was substitute the CASE statement for the 'true'. So no, the CASE statement doesn't always get evaluated. It only gets evaluated when the boolean condition evaluates to 'true' (when VarB is not blank or equal to zero).

Edit2

To clarify my comments. The CASE statement is only evaluated when the IF statement evaluates to True. It is not evaluated when the IF statement evaluates to False. As such, it isn't evaluated every time the formula fires, instead only when the IF statement conditions allow it.

As for the CASE statement being evaluated, one could easily construct a formula where there are two CASE statements, one for when the IF statement is True and another for when it's False.

In order for the CASE statements to be evaluated, yes, the operations in each statement must be performed before they can be evaluated so the proper CASE can be chosen. I believe that's the point you were trying to make. I completely understand that the code in each formula must be executed in order for the evaluation to take place. It's incumbent upon you to make certain that your code can be handled safely and is populated with the necessary data for that to happen.

One can construct a series of nested IF statements testing each Var for BLANKVALUE and equality with 0. If a Var passes, the full CASE statement is used for the True, if it fails, a different CASE statement is used that removes the CASES that would cause problems with formulas that couldn't be resolved. The next variable is then tested for BLANKVALUE and equality with 0. This continues until you've worked your way through all of them. I hope this clarifies my answer and comments for you.

Related Topic