{"id":245,"date":"2015-09-26T09:23:19","date_gmt":"2015-09-26T04:23:19","guid":{"rendered":"https:\/\/learnsf.wordpress.com\/?p=245"},"modified":"2023-12-30T17:12:54","modified_gmt":"2023-12-30T23:12:54","slug":"3-steps-to-optimize-a-complex-formula","status":"publish","type":"post","link":"https:\/\/blog.felineflock.com\/index.php\/2015\/09\/26\/3-steps-to-optimize-a-complex-formula\/","title":{"rendered":"Optimize Complex Salesforce Formulas: 3 Proven Steps"},"content":{"rendered":"
This article was also posted in LinkedIn: \u00a03 Steps to optimize a complex formula in Salesforce<\/a>.<\/strong><\/h5>\n

The formula below was created straight from the client requirements and didn’t fit the maximum formula size (5k bytes) when compiled.
\nIt\u00a0was 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.
\nHere are 3 steps\u00a0to\u00a0optimize it to fit the limit.<\/p>\n

IF(
\nISBLANK(Type__c),
\nIMAGE(“\/img\/samples\/color_red.gif”, “Red”, 10, 10),
\nIF(
\nOR(
\n(Type__c = “TA”),
\n(Type__c = “DB”),
\n(Type__c = “AC”)),
\nIMAGE(“\/img\/samples\/color_red.gif”, “Red”, 10, 10),
\nIF(
\n(Amount__c < 1), IMAGE(“\/img\/samples\/color_green.gif”, “Green”, 10, 10), IF( AND( (Type__c = “C2”), (Amount__c > 1),
\n(Days_Past_Due__c <= 7)), IMAGE(“\/img\/samples\/color_yellow.gif”, “Yellow”, 10, 10), IF( AND( (Type__c = “C2”), (Amount__c > 1),
\n(Days_Past_Due__c > 7)),
\nIMAGE(“\/img\/samples\/color_red.gif”, “Red”, 10, 10),
\nIF(
\nAND(
\nOR(
\n(Type__c = “PC”),
\n(Type__c = “CN”)),
\n(Amount__c > 1),
\n(Days_Past_Due__c >= 1)),
\nIMAGE(“\/img\/samples\/color_red.gif”, “Red”, 10, 10),
\nIF(
\nAND(
\n(Type__c = “P2”),
\n(Amount__c > 1),
\n(Days_Past_Due__c <= 7)), IMAGE(“\/img\/samples\/color_red.gif”, “Red”, 10, 10), IF( AND( (Type__c = “P2”), (Amount__c > 1),
\n(Days_Past_Due__c > 7)),
\nIMAGE(“\/img\/samples\/color_yellow.gif”, “Yellow”, 10, 10),
\nIF(
\nAND(
\n(Type__c = “WT”),
\n(Amount__c > 1),
\n(Days_Past_Due__c <= 14)), IMAGE(“\/img\/samples\/color_green.gif”, “Green”, 10, 10), IF( AND( (Type__c = “WT”), (Amount__c > 1),
\n(Days_Past_Due__c > 14),
\n(Days_Past_Due__c <= 21)), IMAGE(“\/img\/samples\/color_yellow.gif”, “Yellow”, 10, 10), IF( AND( (Type__c = “WT”), (Amount__c > 1),
\n(Days_Past_Due__c > 21)),
\nIMAGE(“\/img\/samples\/color_red.gif”, “Red”, 10, 10),
\nIF(
\nAND(
\n(Type__c = “WF”),
\n(Amount__c > 1),
\n(Days_Past_Due__c <= 28)), IMAGE(“\/img\/samples\/color_green.gif”, “Green”, 10, 10), IF( AND( (Type__c = “WF”), (Amount__c > 1),
\n(Days_Past_Due__c > 28),
\n(Days_Past_Due__c <= 36)), IMAGE(“\/img\/samples\/color_yellow.gif”, “Yellow”, 10, 10), IF( AND( (Type__c = “WF”), (Amount__c > 1),
\n(Days_Past_Due__c > 36)),
\nIMAGE(“\/img\/samples\/color_red.gif”, “Red”, 10, 10),
\nIF(
\nAND(
\n(Type__c = “MT”),
\n(Amount__c > 1),
\n(Days_Past_Due__c <= 37)), IMAGE(“\/img\/samples\/color_green.gif”, “Green”, 10, 10), IF( AND( (Type__c = “MT”), (Amount__c > 1),
\n(Days_Past_Due__c > 37),
\n(Days_Past_Due__c <= 45)), IMAGE(“\/img\/samples\/color_yellow.gif”, “Yellow”, 10, 10), IF( AND( (Type__c = “MT”), (Amount__c > 1),
\n(Days_Past_Due__c > 45)),
\nIMAGE(“\/img\/samples\/color_red.gif”, “Red”, 10, 10),
\nIF(
\nAND(
\n(Type__c = “MS”),
\n(Amount__c > 1),
\n(Days_Past_Due__c <= 67)), IMAGE(“\/img\/samples\/color_green.gif”, “Green”, 10, 10), IF( AND( (Type__c = “MS”), (Amount__c > 1),
\n(Days_Past_Due__c > 67),
\n(Days_Past_Due__c <= 75)), IMAGE(“\/img\/samples\/color_yellow.gif”, “Yellow”, 10, 10), IF( AND( (Type__c = “MS”), (Amount__c > 1),
\n(Days_Past_Due__c > 75)),
\nIMAGE(“\/img\/samples\/color_red.gif”, “Red”, 10, 10),
\nNULL
\n))))))))))))))))))))<\/p><\/blockquote>\n

1) Reorganize\/group the conditions by their outcome<\/strong><\/h3>\n

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.<\/p>\n

Below is how it looked like when I started grouping the conditions for Green:<\/p>\n

IF((Amount__c < 1), IMAGE(“\/img\/samples\/color_green.gif”, “Green”, 10, 10),
\nIF( AND( (Type__c = “WT”), (Amount__c > 1),\u00a0(Days_Past_Due__c <= 14)), IMAGE(“\/img\/samples\/color_green.gif”, “Green”, 10, 10),
\nIF( AND( (Type__c = “WF”), (Amount__c > 1),\u00a0(Days_Past_Due__c <= 28)), IMAGE(“\/img\/samples\/color_green.gif”, “Green”, 10, 10),
\nIF( AND( (Type__c = “MT”), (Amount__c > 1),\u00a0(Days_Past_Due__c <= 37)), IMAGE(“\/img\/samples\/color_green.gif”, “Green”, 10, 10),
\nIF( AND( (Type__c = “MS”), (Amount__c > 1),\u00a0(Days_Past_Due__c <= 67)),
\nIMAGE(“\/img\/samples\/color_green.gif”, “Green”, 10, 10),
\n…<\/p><\/blockquote>\n

The purpose is\u00a0to reorganize the conditions in preparation for the next step, which is:<\/p>\n

2)\u00a0<\/strong>Remove nested IFs – use ORs and line breaks<\/strong><\/h3>\n

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

It follows the pattern IF expression1 ||\u00a0expression2 || expression3 … where each OR expression is in a separate line for legibility (use Ctrl or Shift + Enter).
\nEach expression can (usually) contain nested ANDs.<\/p>\n

\"Screen<\/a><\/p>\n

Here is how it looks like for the Green conditions reorganized in the previous step.
\nNotice that now there is only a single reference to the green image link.
\nThe compiled formula is now smaller because the redundant nested IFs and IMAGE references were replaced.<\/p>\n

IF((Amount__c < 1)
\n|| AND( (Type__c = “WT”), (Amount__c > 1),\u00a0(Days_Past_Due__c <= 14))
\n|| AND( (Type__c = “WF”), (Amount__c > 1),\u00a0(Days_Past_Due__c <= 28))
\n|| AND( (Type__c = “MT”), (Amount__c > 1),\u00a0(Days_Past_Due__c <= 37))
\n|| AND( (Type__c = “MS”), (Amount__c > 1),\u00a0(Days_Past_Due__c <= 67)),
\nIMAGE(“\/img\/samples\/color_green.gif”, “Green”, 10, 10),
\n…<\/p><\/blockquote>\n

Additionally, you can use && (shorthand for AND) and remove unneeded parenthesis:<\/p>\n

\"Screen<\/p>\n

This way you can further identify repeating patterns that you can optimize and eliminate redundancy.<\/p>\n

At this point, the formula was pretty close to fitting the limit, but if the formula was lengthier, there are\u00a0other\u00a0optimizations\u00a0to use.<\/p>\n

3)\u00a0<\/strong>Identify repeated conditions and create separate formulas\/expressions for\u00a0them… using a few tricks!<\/strong><\/h3>\n

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

Notice in the above formula that for\u00a0type WT the\u00a0past due limit is\u00a014, 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.<\/p>\n

You can condense all the\u00a0“type-past due”\u00a0<\/em>conditions into a single CASE() function like below:<\/p>\n

\"Screen<\/p>\n

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

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\u00a0or\u00a0MS).
\nThe CASE expression could also be placed in a separate formula named “Past_Due_Limit_Per_Type__c<\/em>” to help with legibility (it doesn’t reduce the compiled size of the formula though).<\/p>\n

Another way of condensing multiple OR conditions like the ones found below is to use CONTAINS().<\/p>\n

IF(
\nOR(
\n(Type__c = “TA”),
\n(Type__c = “DB”),
\n(Type__c = “AC”)),
\nIMAGE(“\/img\/samples\/color_red.gif”, “Red”, 10, 10),
\n…<\/p><\/blockquote>\n

See below: \u00a0you 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|”.<\/p>\n

IF( CONTAINS( “|TA|DB|AC|”, Type__c )
\n, IMAGE(“\/img\/samples\/color_red.gif”, “Red”, 10, 10),
\n…<\/p><\/blockquote>\n

The higher the number of values to check, the more you benefit from this trick.<\/p>\n

Beware of when the value does\u00a0not have a fixed length<\/strong>: \u00a0if 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 + “|”<\/strong><\/em> instead of Type__c.<\/p>\n

In summary…<\/strong><\/h2>\n

The 3 steps are:<\/p>\n

    \n
  1. \n

    Reorganize the complex formula by outcome\/result<\/h2>\n<\/li>\n
  2. \n

    Replace nested IFs with the pattern OR ( exprA1 AND exprA2 AND … ) OR ( exprB1 AND exprB2 AND … )<\/h2>\n<\/li>\n
  3. \n

    Identify repeated conditions and create formula\/expressions for them<\/h2>\n<\/li>\n<\/ol>\n

    <\/h1>\n","protected":false},"excerpt":{"rendered":"

    This article was also posted in LinkedIn: \u00a03 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\u00a0was intended to display a red, yellow or green square depending on how many days the account was […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"_uf_show_specific_survey":0,"_uf_disable_surveys":false,"footnotes":""},"categories":[11],"tags":[],"class_list":["post-245","post","type-post","status-publish","format-standard","hentry","category-formula"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/blog.felineflock.com\/index.php\/wp-json\/wp\/v2\/posts\/245","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.felineflock.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.felineflock.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.felineflock.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.felineflock.com\/index.php\/wp-json\/wp\/v2\/comments?post=245"}],"version-history":[{"count":1,"href":"https:\/\/blog.felineflock.com\/index.php\/wp-json\/wp\/v2\/posts\/245\/revisions"}],"predecessor-version":[{"id":556,"href":"https:\/\/blog.felineflock.com\/index.php\/wp-json\/wp\/v2\/posts\/245\/revisions\/556"}],"wp:attachment":[{"href":"https:\/\/blog.felineflock.com\/index.php\/wp-json\/wp\/v2\/media?parent=245"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.felineflock.com\/index.php\/wp-json\/wp\/v2\/categories?post=245"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.felineflock.com\/index.php\/wp-json\/wp\/v2\/tags?post=245"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}