The bold statement: CASE formula always calculates all values (including else) even if a condition has already been met.


Use case: Create a formula field to calculate the 1st day of next month from closedate (for simplification lets assume all months have equal number of days)

Consider the following 2 formulas:

Formula_1:

CASE(MONTH(CloseDate),
12, DATE(YEAR(CloseDate)+1,1,1),
DATE(YEAR(CloseDate),MONTH(CloseDate) + 1,1)
)

Formula_2:

IF(MONTH(CloseDate) == 12,
DATE(YEAR(CloseDate)+1,1,1),
DATE( YEAR(CloseDate), MONTH(CloseDate) + 1,1)
)

Guess what, if the Close Date is in December Formula_1 will throw an error. In Formula_1, “MONTH(CloseDate) + 1” was evaluated even though the case = 12 was met. Obviously, Salesforce knows 13 isn’t a valid month and hence throws an error.

Consider another formula (forget the use case for a while):

CASE(MONTH(CloseDate),
12, DATE(YEAR(CloseDate),MONTH(CloseDate)-1,1),
DATE(YEAR(CloseDate),MONTH(CloseDate) + 1,1)
)

Here if the closedate is in Jan the formula again throws an error. Which means that when the else statement is true, the formula still evaluates the case with value = ’12’

 

Another example:

Formula is:

case (
Number_field__c,
0, 0,
1/Number_field__c
)

If Number_field__c = 0, the formula throws an error.

What we infer from here:-

CASE formula always calculates all the condition values (including else) even if a condition has already been met.
Note: This is not true for IF statements, which doesn’t calculate the “false” value if a condition is met.

Visual illustration of above examples:

In the image a below, the 2 fields on the left are date and number fields respectively, while the 2 fields on the right are the formula fields as defined above. See the result for yourself:

screenshot-30

screenshot-31

Hope this will help write better CASE statements.
Cheers!

Advertisements