0

Is There any way I reduce formula size.

IF(BLANKVALUE(TEXT(Engagement_Override__c),"Blank")=="Blank",
IF(OR(Account__r.Geo__c =="Americas",Account__r.Geo__c=="EMEA"),
IF(OR(Account__r.Account_Segment__c=="Major Account",
Account__r.Account_Segment__c=="Major Account Global",
Account__r.Account_Segment__c=="Named Account",
Account__r.Account_Segment__c=="Industry Target Account",
Account__r.Account_Segment__c=="Named Account Global"),"High Touch",
IF(AND(OR(
Account__r.Account_Segment__c=="Midmarket",
Account__r.Account_Segment__c=="Territory Custom",
Account__r.Account_Segment__c=="Territory",
Account__r.Account_Segment__c=="Emerging Strategic Account"),
Active_BIM_User_Subscriptions__c> 500),"High Touch",

IF(AND(OR(Account__r.Account_Segment__c=="Midmarket",
Account__r.Account_Segment__c=="Territory Custom",
Account__r.Account_Segment__c=="Territory",
Account__r.Account_Segment__c=="Emerging Strategic Account"),
AND(Active_BIM_User_Subscriptions__c<= 500, Active_BIM_User_Subscriptions__c>=50)),"Medium Touch",
IF(AND(OR(
Account__r.Account_Segment__c=="Midmarket",
Account__r.Account_Segment__c=="Territory Custom",
Account__r.Account_Segment__c=="Territory",
Account__r.Account_Segment__c=="Emerging Strategic Account"),
Active_BIM_User_Subscriptions__c< 50),"Tech Touch","Tech Touch")))),
IF(Account__r.Geo__c=="APAC",IF(AND(OR(
Account__r.Account_Segment__c=="Midmarket",
Account__r.Account_Segment__c=="Territory Custom",
Account__r.Account_Segment__c=="Territory",
Account__r.Account_Segment__c=="Emerging Strategic Account"),
Active_BIM_User_Subscriptions__c< 50),"Tech Touch","Medium Touch"),"Tech Touch")),
TEXT(Engagement_Override__c))
2
  • I'd probably move this to a Process or a Trigger. Formulas don't really work very well when they are this big. Too hard to read. – Caspar Harmer Sep 20 '18 at 23:10
  • Yes, this formula can be easily optimized, but it'll still look pretty complicated. – sfdcfox Sep 20 '18 at 23:30
1

Your formula has some impossible branches, so I took a bit of liberty in rewriting this formula. This is otherwise the most literal interpretation of what I read in your code, so if it's incorrect, please feel free to adjust it. Also, there may yet be compilation errors, as I don't have a way to test this without setting up objects and fields. Finally, I made some guesses on the data types based on what's in your formula, so you might need to alter the formula to account for those as well.

Basically, the "trick" here is that we use CASE to minimize the number of field references we need to a rather small amount. This should ultimately save a lot of overhead in the formula. Also, the nested CASE statements eliminate almost all of the IF formulas to limit the complexity of the formula to just a few small pieces.

BLANKVALUE(
    TEXT(Engagement_Override__c),
    CASE(
        CASE(Account__r.Account_Segment__c,
            "Major Account", 1, "Major Account Global", 1, "Named Account", 1, "Industry Target Account", 1, "Named Global Account", 1,
            "Midmarket", 2, "Territory Custom", 2, "Territory", 2, "Emerging Strategic Account", 2, 3
        ),
        1, CASE(
                CASE(Account__r.Geo__c, "Americas", 1, "EMEA", 1, 2),
                1, "High Touch", "Tech Touch"
            ),
        2, CASE(
                CASE(Account__r.Geo__c, "Americas", 1, "EMEA", 1, "APAC", 2, 3),
                1, IF(Active_BIM_User_Subscriptions__c<50, "Tech Touch", IF(Active_BIM_User_Subscriptions__c<500, "Medium Touch", "High Touch")),
                2, IF(Active_BIM_User_Subscriptions__c<50, "Tech Touch", "Medium Touch"),
                "Tech Touch"
            )
        "Tech Touch"
    )
)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.