0

I have the following formula in my formula field that assigns a value to Contact that meet the listed criteria. However, I am not able to save the field as I get the following error: "Error: Compiled formula is too big to execute (10,999 characters). Maximum size is 5,000 characters " Is there any way to get around this?

This is the formula:

CASE(
    IF(
        OR(
            TEXT(Status__c) = "Current",
            TEXT(Status__c) = "Ongoing",
            TEXT(Status__c) = "Lapsed"
        ),
        "S",
        "X"
    ) +

    IF(
        TEXT(Practitioner_Group__c) = "Consultant",
        "C",
        "X"
    ) +

    IF(
        MailingCity = "London",
        "L",
        "X"
    ) +

    CASE(
        Practitioner_Speciality__c,
        "Orthopaedic",      "A",
        "Neurosurgery",     "B",
        "Neurology",        "C",
        "Rheumatology",     "D",
        "ENT",              "E",
        "Sports Medicine",  "F",
        "Pain Medicine",    "G",
        "X"
    ),

    /*Valid Status(S), Consultant(C), London(L) and one of the valid specialities(A-G).*/
    "SCLA", "3",
    "SCLB", "3",
    "SCLC", "2",
    "SCLD", "2",
    "SCLE", "2",
    "SCLF", "2",
    "SCLG", "2",

    /*Valid Status(S), Consultant(C), London(L) and no speciality(X)*/
    "SCLX", "1",

    /*Valid Status(S), Consultant(C), not from London(X) and one of the valid speciality(A-G.*/
    "SCXA", "2",
    "SCXB", "2",
    "SCXC", "2",
    "SCXD", "1",
    "SCXE", "1",
    "SCXF", "1",
    "SCXG", "1",

    NULL
)

Updated formula:

Case(

  IF(

    2 > CASE(
    Clinician_Status__c, "Current", 1, "Ongoing", 1, "Lapsed", 1, 0
    ) + IF(ISPICKVAL(Practitioner_Group__c, "Consultant"), 1, 0,NULL)
     )

   IF(
   MailingCity = "London",
   CASE(
       Practitioner_Speciality__c,
       "Orthopaedic", "3",
       "Neurosurgery", "3",
       "Neurology", "2",
       "Rheumatology", "2",
       "ENT", "2",
       "Sports Medicine", "2",
       "Pain Medicine", "2",
       1
        ),
    CASE(
         Practitioner_Speciality__c,
         "Orthopaedic", "2",
         "Neurosurgery", "2",
         "Neurology", "2",
         "Rheumatology", "1",
         "ENT", "1",
         "Sports Medicine", "1",
         "Pain Medicine", "1",
         NULL
         )
    )

)

  • This documentation about how to reduce formulas might help you. – Basti Nov 24 '17 at 9:38
6

The most obvious change off the bat is to collapse your section of the formula using multiple ISPICKVAL/TEXT calls.

Instead of:

IF(
    OR(
        TEXT(Status__c) = "Current",
        TEXT(Status__c) = "Ongoing",
        TEXT(Status__c) = "Lapsed"
    ),
    "S",
    "X"
)

Use:

CASE(
    Status__c,
    "Current", "S", "Ongoing", "S", "Lapsed", "S",
    "X"
)

Since your formula is always null if that initial concatenation evaluates to anything other than "SC", you can also just check your "SC" conditions once, and if either is false to short circuit the whole evaluation.

IF(
    2 > CASE(
        Status__c, "Current", 1, "Ongoing", 1, "Lapsed", 1, 0
    ) + IF(ISPICKVAL(Practitioner_Group__c, "Consultant", 1, 0),
    NULL,
    /*check the last two conditions here*/
)

Now that falsy condition can be simplified somewhat:

IF(
    BillingCity = "London",
    CASE(
        Practitioner_Speciality__c,
        "Orthopaedic", "3",
        "Neurosurgery", "3",
        "Neurology", "2",
        "Rheumatology", "2",
        "ENT", "2",
        "Sports Medicine", "2",
        "Pain Medicine", "2",
        1
    ),
    CASE(
        Practitioner_Speciality__c,
        "Orthopaedic", "2",
        "Neurosurgery", "2",
        "Neurology", "2",
        "Rheumatology", "1",
        "ENT", "1",
        "Sports Medicine", "1",
        "Pain Medicine", "1",
        NULL
    )
)
|improve this answer|||||
0

Counting your characters I got something about 1182. Doesn't look that bad. But as described here: Tips for Reducing Formula Size, other formulas used in your formula also count into your limit. I would suggest reading the whole document to come up with a more efficient way of writing your formulas.

So in your case if Status__c is a formula field, it is counted 3 times into your limit. Meaning that if it has abou 3000 characters 9000 characters are counted into this formula. Practitioner_Group__c and Practitioner_Speciality__c are counted once (in case they are formulas).

|improve this answer|||||
  • Thank you for the document; the issue is that I am not referencing any other formula fields in this formula, and it is still giving me that error. – Sophie Nov 24 '17 at 12:57

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.