Reduce Formula size

Question

I have logic in Account object for Formula field, but while i try to save it gives me error

Error: Compiled formula is too big to execute (6,387 characters). Maximum size is 5,000 characters

Below is my code:

IF(
    AND(
        ISPICKVAL(Campaign.FCRM__FCR_Campaign_Sourced_By__c, 'Marketing'),
        OR(TAL__c = True, Tele_Rejected__c = True)), 'Marketing',
    IF(
        OR(
            AND(
                ISPICKVAL(Campaign.FCRM__FCR_Campaign_Sourced_By__c, 'Marketing'),

                OR(
                    TAL__c = False,
                    Tele_Rejected__c = False),
                OR(
                    Oppy_Last_30_Days_On_Create__c > 0,
                    Country = 'Canada',
                    Country = 'Mexico',
                    Theater__c = 'APAC',
                    AND(Theater__c = 'EMEA',
                        OR(Country = 'United Kingdom',
                            Country = 'Germany',
                            Country = 'France',
                            Country = 'Sweden',
                            Country = 'Norway',
                            Country = 'Finland',
                            Country = 'Denmark',
                            Country = 'The Netherlands',
                            Country = 'Belgium',
                            Country = 'Luxembourg',
                            Country = 'Ireland')
                    ),
                    Account_Type_on_Create__c = 'Active Prospect',
                    Account_Type_On_Update__c = 'Active Prospect',
                    Sales_Rejected__c = True,
                    Sales_Engaged__c = True,
                    ISPICKVAL(Contact.Account.Type, 'Client'),
                    ISBLANK(Contact.SA__c)
                )
            ),
            TEXT(Contact.Owner.Sales_Responsibilities__c) = "AM"), 'Marketing-RA',
        IF(
            AND(
                ISPICKVAL(Campaign.FCRM__FCR_Campaign_Sourced_By__c, 'Marketing'),
                TAL__c = False,
                FCRM__FCR_SAR__c = True), 'Marketing-RA',
            IF(
                ISPICKVAL(Campaign.FCRM__FCR_Campaign_Sourced_By__c, 'Marketing'), 'Marketing',
                '')
        )
    )
)

Can anyone suggest how to reduce this formula?

Answer 1

I don't know if these changes will be enough to get this specific formula under the compile size limit, but here are some ways to bring it down:

Don't compare Boolean/checkbox fields to TRUE/FALSE. Use just the field name, or NOT(field_name__c). In a quick test on a checkbox field Inactive__c:

• IF(Inactive__c,"Active","Inactive") - compiled size 75 chars
• IF(NOT(Inactive__c),"Inactive","Active") - compiled size 81 chars
• IF(Inactive__c = TRUE,"Active","Inactive") - compiled size 107 chars
• IF(Inactive__c = FALSE,"Inactive","Active") - compiled size 107 chars

Reduce repeated calls to fields. If I'm reading things correctly, this formula will only return a non-blank value when Campaign.FCRM__FCR_Campaign_Sourced_By__c is 'Marketing'. So make that check a single element of an IF and then put the rest in, rather than checking for it at each point.

Don't check field superfluously. Unless there are cases where Theater__c != EMEA AND one of the named European Countries is specified, you can take that check for EMEA out.

Finally, while referencing other formula fields won't help, as a last resort you can create new custom fields directly on the object where the formula is and populate them with workflow or Process Builder. The Campaign.FCRM__FCR_Campaign_Sourced_By__c field might be a good candidate for that.

Answer 2

You can decrease the size of your formula by breaking it into multiple formulas. For instance, you could create a separate formula field called Country_Tier__c with the following formula:

 CASE(Country,  
    'United Kingdom', 1,
    'Germany', 1,
    'France', 1,
    'Sweden', 1,
    'Norway', 1,
    'Finland', 1,
    'Denmark', 1,
    'The Netherlands', 1,
    'Belgium', 1,
    'Luxembourg', 1,
    'Ireland', 1,
    2)

Then, you can reference the answer to this formula in your original formula.