0

Management asked my co-worker to create a formula field they can use for reporting.

He doesn't have a stack exchange account (and doesn't want to make one just yet /shrug) so I'm posting in his behalf.

The Blanks_Contract_Stage_to_Install__c field is a formula field that we are referencing inside of this, a new formula field. Management was wanting a formula field because formula fields always have current values without needing to go and trigger an update for each record. But if we need to some kind of timed automation to produce a similar result is not out of the question.

We need to reduce the compile size of this formula. Currently it compiles to 5,983 characters. Either that or we need something that can be run on a timer, hopefully that wouldn't require an Apex solution. (We can code it, but would like to not have to if possible).

CASE(TEXT(Territory_Picklist__c),
     "Buda",
     IF(Blanks_Contract_Stage_to_Install__c < 50, "Expected",

     IF(Blanks_Contract_Stage_to_Install__c < 80, "Past Expected", "Out of Bounds")),


 "Albuquerque",

     IF(Blanks_Contract_Stage_to_Install__c < 45, "Expected",

     IF(Blanks_Contract_Stage_to_Install__c < 75, "Past Expected", "Out of Bounds")),


  "Charleston",

     IF(Blanks_Contract_Stage_to_Install__c < 55, "Expected",

     IF(Blanks_Contract_Stage_to_Install__c < 90, "Past Expected", "Out of Bounds")),

  "Columbia",

     IF(Blanks_Contract_Stage_to_Install__c < 55, "Expected",

     IF(Blanks_Contract_Stage_to_Install__c < 90, "Past Expected", "Out of Bounds")),

  "Connecticut",

     IF(Blanks_Contract_Stage_to_Install__c < 40, "Expected",

     IF(Blanks_Contract_Stage_to_Install__c < 60, "Past Expected", "Out of Bounds")),

  "Pueblo",

     IF(Blanks_Contract_Stage_to_Install__c < 35, "Expected",

     IF(Blanks_Contract_Stage_to_Install__c < 45, "Past Expected", "Out of Bounds")),

     IF(Blanks_Contract_Stage_to_Install__c < 30, "Expected",

     IF(Blanks_Contract_Stage_to_Install__c < 60, "Past Expected", "Out of Bounds")) )

The formula in the referenced field Blanks_Contract_Stage_to_Install__c looks like this:

   BLANKVALUE(Installation__r.Solar_Install_Date__c, TODAY()) - Financing__r.Contract_Stage_Complete__c

Any tips would be greatly appreciated.

Thank you

  • 1
    Use custom labels you can reduce size and use flows if you dont want to write code – Pavan tej May 18 '17 at 16:00
1

The following formula should help:

IF(Blanks_Contract_Stage_to_Install__c < 

 CASE(TEXT(Territory_Picklist__c),
  "Buda",50,
  "Albuquerque",45,
  "Charleston",55,
  "Columbia",55,
  "Connecticut",40,
  "Pueblo",35,
 ,30),

"Expected",

IF(Blanks_Contract_Stage_to_Install__c < 
 CASE(TEXT(Territory_Picklist__c),
  "Buda",80,
  "Albuquerque",75,
  "Charleston",90,
  "Columbia",90,
  "Connecticut",60,
  "Pueblo",45,
 ,60),

"Past Expected",
"Out of Bounds")
)
  • 1
    This did exactly what we needed. I didn't know you could condense a CASE function that way in a Salesforce formula. The compile size was right around 1600. Thanks! I/we learned something very valuable from this response. – Genko May 18 '17 at 20:21
  • 1
    Glad to help. We all are learning something everyday. – o-lexi May 18 '17 at 20:23

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.