Monthly Archives: September 2015

3 Steps to optimize a complex formula in Salesforce

This article was also posted in LinkedIn:  3 Steps to optimize a complex formula in Salesforce.

The formula below was created straight from the client requirements and didn’t fit the maximum formula size (5k bytes) when compiled.
It was intended to display a red, yellow or green square depending on how many days the account was past due and according to the type of the account.
Here are 3 steps to optimize it to fit the limit.

IF(
ISBLANK(Type__c),
IMAGE(“/img/samples/color_red.gif”, “Red”, 10, 10),
IF(
OR(
(Type__c = “TA”),
(Type__c = “DB”),
(Type__c = “AC”)),
IMAGE(“/img/samples/color_red.gif”, “Red”, 10, 10),
IF(
(Amount__c < 1), IMAGE(“/img/samples/color_green.gif”, “Green”, 10, 10), IF( AND( (Type__c = “C2”), (Amount__c > 1),
(Days_Past_Due__c <= 7)), IMAGE(“/img/samples/color_yellow.gif”, “Yellow”, 10, 10), IF( AND( (Type__c = “C2”), (Amount__c > 1),
(Days_Past_Due__c > 7)),
IMAGE(“/img/samples/color_red.gif”, “Red”, 10, 10),
IF(
AND(
OR(
(Type__c = “PC”),
(Type__c = “CN”)),
(Amount__c > 1),
(Days_Past_Due__c >= 1)),
IMAGE(“/img/samples/color_red.gif”, “Red”, 10, 10),
IF(
AND(
(Type__c = “P2”),
(Amount__c > 1),
(Days_Past_Due__c <= 7)), IMAGE(“/img/samples/color_red.gif”, “Red”, 10, 10), IF( AND( (Type__c = “P2”), (Amount__c > 1),
(Days_Past_Due__c > 7)),
IMAGE(“/img/samples/color_yellow.gif”, “Yellow”, 10, 10),
IF(
AND(
(Type__c = “WT”),
(Amount__c > 1),
(Days_Past_Due__c <= 14)), IMAGE(“/img/samples/color_green.gif”, “Green”, 10, 10), IF( AND( (Type__c = “WT”), (Amount__c > 1),
(Days_Past_Due__c > 14),
(Days_Past_Due__c <= 21)), IMAGE(“/img/samples/color_yellow.gif”, “Yellow”, 10, 10), IF( AND( (Type__c = “WT”), (Amount__c > 1),
(Days_Past_Due__c > 21)),
IMAGE(“/img/samples/color_red.gif”, “Red”, 10, 10),
IF(
AND(
(Type__c = “WF”),
(Amount__c > 1),
(Days_Past_Due__c <= 28)), IMAGE(“/img/samples/color_green.gif”, “Green”, 10, 10), IF( AND( (Type__c = “WF”), (Amount__c > 1),
(Days_Past_Due__c > 28),
(Days_Past_Due__c <= 36)), IMAGE(“/img/samples/color_yellow.gif”, “Yellow”, 10, 10), IF( AND( (Type__c = “WF”), (Amount__c > 1),
(Days_Past_Due__c > 36)),
IMAGE(“/img/samples/color_red.gif”, “Red”, 10, 10),
IF(
AND(
(Type__c = “MT”),
(Amount__c > 1),
(Days_Past_Due__c <= 37)), IMAGE(“/img/samples/color_green.gif”, “Green”, 10, 10), IF( AND( (Type__c = “MT”), (Amount__c > 1),
(Days_Past_Due__c > 37),
(Days_Past_Due__c <= 45)), IMAGE(“/img/samples/color_yellow.gif”, “Yellow”, 10, 10), IF( AND( (Type__c = “MT”), (Amount__c > 1),
(Days_Past_Due__c > 45)),
IMAGE(“/img/samples/color_red.gif”, “Red”, 10, 10),
IF(
AND(
(Type__c = “MS”),
(Amount__c > 1),
(Days_Past_Due__c <= 67)), IMAGE(“/img/samples/color_green.gif”, “Green”, 10, 10), IF( AND( (Type__c = “MS”), (Amount__c > 1),
(Days_Past_Due__c > 67),
(Days_Past_Due__c <= 75)), IMAGE(“/img/samples/color_yellow.gif”, “Yellow”, 10, 10), IF( AND( (Type__c = “MS”), (Amount__c > 1),
(Days_Past_Due__c > 75)),
IMAGE(“/img/samples/color_red.gif”, “Red”, 10, 10),
NULL
))))))))))))))))))))

1) Reorganize/group the conditions by their outcome

Notice the repeated references to red, yellow and green image links scattered throughout the formula. That sequence of the conditions probably match what makes sense to the end user but this arrangement leads to redundancy in the formula.

Below is how it looked like when I started grouping the conditions for Green:

IF((Amount__c < 1), IMAGE(“/img/samples/color_green.gif”, “Green”, 10, 10),
IF( AND( (Type__c = “WT”), (Amount__c > 1), (Days_Past_Due__c <= 14)), IMAGE(“/img/samples/color_green.gif”, “Green”, 10, 10),
IF( AND( (Type__c = “WF”), (Amount__c > 1), (Days_Past_Due__c <= 28)), IMAGE(“/img/samples/color_green.gif”, “Green”, 10, 10),
IF( AND( (Type__c = “MT”), (Amount__c > 1), (Days_Past_Due__c <= 37)), IMAGE(“/img/samples/color_green.gif”, “Green”, 10, 10),
IF( AND( (Type__c = “MS”), (Amount__c > 1), (Days_Past_Due__c <= 67)),
IMAGE(“/img/samples/color_green.gif”, “Green”, 10, 10),

The purpose is to reorganize the conditions in preparation for the next step, which is:

2) Remove nested IFs – use ORs and line breaks

This is easier to show than to explain, but it means to replace the several nested IFs with a single IF with a long sequence of conditions, each condition separated by || which is the shorthand for OR.

It follows the pattern IF expression1 || expression2 || expression3 … where each OR expression is in a separate line for legibility (use Ctrl or Shift + Enter).
Each expression can (usually) contain nested ANDs.

Screen Shot 2015-09-25 at 9.17.49 PM

Here is how it looks like for the Green conditions reorganized in the previous step.
Notice that now there is only a single reference to the green image link.
The compiled formula is now smaller because the redundant nested IFs and IMAGE references were replaced.

IF((Amount__c < 1)
|| AND( (Type__c = “WT”), (Amount__c > 1), (Days_Past_Due__c <= 14))
|| AND( (Type__c = “WF”), (Amount__c > 1), (Days_Past_Due__c <= 28))
|| AND( (Type__c = “MT”), (Amount__c > 1), (Days_Past_Due__c <= 37))
|| AND( (Type__c = “MS”), (Amount__c > 1), (Days_Past_Due__c <= 67)),
IMAGE(“/img/samples/color_green.gif”, “Green”, 10, 10),

Additionally, you can use && (shorthand for AND) and remove unneeded parenthesis:

Screen Shot 2015-09-25 at 9.25.49 PM

This way you can further identify repeating patterns that you can optimize and eliminate redundancy.

At this point, the formula was pretty close to fitting the limit, but if the formula was lengthier, there are other optimizations to use.

3) Identify repeated conditions and create separate formulas/expressions for them… using a few tricks!

IF( Amount__c < 1
|| ( Type__c = “WT” && Amount__c > 1 && Days_Past_Due__c <= 14 )
|| ( Type__c = “WF” && Amount__c > 1 && Days_Past_Due__c <= 28 )
|| ( Type__c = “MT” && Amount__c > 1 && Days_Past_Due__c <= 37 )
|| ( Type__c = “MS” && Amount__c > 1 && Days_Past_Due__c <= 67 )
, IMAGE( “/img/samples/color_green.gif”, “Green”, 10, 10 ),

Notice in the above formula that for type WT the past due limit is 14, while type WF has limit of 28 and so forth for MT and MS? Notice how the amount > 1 condition is the same for all of the 4 cases.

You can condense all the “type-past due” conditions into a single CASE() function like below:

Screen Shot 2015-09-25 at 10.01.23 PM

IF( Amount__c < 1
|| ( Amount__c > 1 && Days_Past_Due__c <= CASE( Type__c, “WT”, 14, “WF”, 28, “MT”, 37, “MS”, 67, -9999 ) )
, IMAGE( “/img/samples/color_green.gif”, “Green”, 10, 10 ),

That way Days_Past_Due__c will be compared to a different value depending on the Type__c. The -9999 is there just to fail the condition when the type is not one of the 4 (WT, WF, MT or MS).
The CASE expression could also be placed in a separate formula named “Past_Due_Limit_Per_Type__c” to help with legibility (it doesn’t reduce the compiled size of the formula though).

Another way of condensing multiple OR conditions like the ones found below is to use CONTAINS().

IF(
OR(
(Type__c = “TA”),
(Type__c = “DB”),
(Type__c = “AC”)),
IMAGE(“/img/samples/color_red.gif”, “Red”, 10, 10),

See below:  you can use CONTAINS and group all the types to check in a single string with vertical pipes as delimiter. When the type matches TA, DB or AC, it will find the type inside the string “|TA|DB|AC|”.

IF( CONTAINS( “|TA|DB|AC|”, Type__c )
, IMAGE(“/img/samples/color_red.gif”, “Red”, 10, 10),

The higher the number of values to check, the more you benefit from this trick.

Beware of when the value does not have a fixed length:  if Type__c is “A” it will match “TA” in the above expression, so be careful! To make the formula safer, you can use “|” + Type__c + “|” instead of Type__c.

In summary…

The 3 steps are:

  1. Reorganize the complex formula by outcome/result

  2. Replace nested IFs with the pattern OR ( exprA1 AND exprA2 AND … ) OR ( exprB1 AND exprB2 AND … )

  3. Identify repeated conditions and create formula/expressions for them

Advertisements