Formula is too big to execute (5,221 characters). Maximum size is 5,000 characters [closed]

Question

Below is my formula that I want to execute I tried using '&& and ||' instead but I am still getting error Can you please help

IF(
    AND(
        $RecordType.Name = "Reseller",                    
        ProgramTypeGrouping__c != "Global SI",                      
        OR(
            ISPICKVAL(Status__c , "Contracted"), 
            ISPICKVAL(Status__c , "Terminating")
        ),
        OR(
            AND( 
                $RecordType.Name = "Service Provider"),  
                OR(
                    ISPICKVAL(Status__c , "Contracted"), 
                    ISPICKVAL(Status__c , "Terminating")
                )
            ), 
            OR( 
                AND(
                    ISPICKVAL( Account__r.PRM_IsDisplayedOnPartnerLocator__c , ""),
                    OR(
                        ISPICKVAL(PartnerTier__c, "Gold"), 
                        ISPICKVAL(PartnerTier__c, "Platinum"), 
                        ISPICKVAL(PartnerTier__c, "Premier"), 
                        ISPICKVAL(PartnerTier__c, "Platinum-Federal"), 
                        ISPICKVAL(PartnerTier__c, "Gold-Federal"), 
                        ISPICKVAL(ProgramType__c, "Global MB Edition"), 
                        ISPICKVAL(ProgramType__c, "MarketBuilder"), 
                        ISPICKVAL(ProgramType__c, "MEDITECH"), 
                        ISPICKVAL(ProgramType__c, "Velocity"), 
                        ISPICKVAL(ProgramType__c, "Velocity-Plus"), 
                        ISPICKVAL(ProgramType__c, "MarketBuilder-Velocity"), 
                        ISPICKVAL(ProgramType__c, "Premier-VAR"), 
                        ISPICKVAL(ProgramType__c, "Premier-Velocity"), 
                        ISPICKVAL(ProgramType__c, "MarketBuilder-VAR")
                    )
                ), 
                ISPICKVAL( Account__r.PRM_IsDisplayedOnPartnerLocator__c , "YES")
            )
        ), 
        True, 
        False
    )

Answer 1

First, you were using IF unnecessary, as Derek said; the result of a Boolean operation is already a Boolean operation. No need to IF(cond, true, false).

Second, your main problem was repeatedly using field references. They are very expensive, and not necessary. The CASE function is purpose-built to optimize this type of problem. We can use 1 = CASE(Field, Value1, 1, Value2, 1, ..., ValueN, 1, 0) instead of a long list of ISPICKVAL. This is far better for performance/size constraints.

Third, you were trying to use OR and AND as binary operators (X OR Y), but the keywords are actually functions/unary operators with multiple arguments (OR(X, Y)).

Given the above, I believe this is what you're trying to do.

1 = CASE(
    $RecordType.Name,
    'Reseller',
    IF(
        AND(ProgramTypeGrouping__c != 'Global SI',
            1 = CASE(Status__c, 'Contracted', 1, 'Terminating', 1, 0)
        ),
        1,
        0
    ),
    'Service Provider',
    CASE(
        Status__c,
        'Contracted', 1,
        'Terminating', 1,
        0
    ),
    IF(
        AND(
            ISPICKVAL(Account__r.PRM_IsDisplayedOnPartnerLocator__c, ''),
            1 = CASE(
                PartnerTier__c,
                'Gold', 1,
                'Platinum', 1,
                'Premier', 1,
                'Platinum-Federal', 1,
                'Gold-Federal', 1,
                'Global MB Edition', 1,
                'MarketBuilder', 1,
                'MEDITECH', 1,
                'Velocity', 1,
                'Velocity-Plus', 1,
                'MarketBuilder-Velocity', 1,
                'Premier-VAR', 1,
                'Premier-Velocity', 1,
                'MarketBuilder-VAR', 1,
                0
            ),
            ISPICKVAL(Account__r.PRM_IsDisplayedOnPartnerLocator__c, 'YES')
        ),
        1,
        0
    )
)

Or, slightly more legible (IMO):

OR(
    AND(
        $RecordType.Name = 'Reseller',
        ProgramTypeGrouping__c != 'Global SI',
        1 = CASE(Status__c, 'Contracted', 1, 'Terminating', 1, 0)
    ),
    AND(
        $RecordType.Name = 'Service Provider',
        1 = CASE(Status__c, 'Contracted', 1, 'Terminating', 1, 0)
    ),
    AND(
        ISPICKVAL(Account__r.PRM_IsDisplayedOnPartnerLocator__c, ''),
        1 = CASE(
            PartnerTier__c,
            'Gold', 1,
            'Platinum', 1,
            'Premier', 1,
            'Platinum-Federal', 1,
            'Gold-Federal', 1,
            'Global MB Edition', 1,
            'MarketBuilder', 1,
            'MEDITECH', 1,
            'Velocity', 1,
            'Velocity-Plus', 1,
            'MarketBuilder-Velocity', 1,
            'Premier-VAR', 1,
            'Premier-Velocity', 1,
            'MarketBuilder-VAR', 1,
            0
        ),
        ISPICKVAL(Account__r.PRM_IsDisplayedOnPartnerLocator__c, 'YES')
    )
)

Answer 2

The formula you've provided is not well-formed (it's missing a closing parenthesis somewhere towards the end), but provided that's not actually an issue, what can you do?

Looking through the formula functions and operators to find alternatives and trying to simplify expressions are about the only tools at your disposal here. The community determined cost for functions/operators would definitely be a resource to use as well.

There is currently no cost given for ISPICKVAL(), but my initial testing seems to indicate that the cost for that is somewhere around 51 compiled characters per use. Since that seems to be the bulk of your formula, that's probably the place you want to spend most of your effort on.

Trying to replace that with CASE() is possible, but doesn't seem to help (the resulting formula consumes more compiled characters in my testing). Perhaps it would be shorter to test to see if PartnerTier__c and ProgramType__c aren't one of those values.

The top-level IF() there appears to be useless. IF(<condition>, true, false) can be simplified to just <condition>

You also have segments that can't possibly be true, such as

AND(
   RecordType.Name = <one rectype>,
   ...
   OR(
       RecordType.Name = <other rectype>
       ...
   )
)

The first record type check you perform guarantees that it will never be any other record type (because you did the first record type check in an AND()).

It may also very well be possible that what you're trying to do just simply can't be accomplished with a formula due to length restrictions. In that case, you'd need to make use of Apex (or possibly jump through a bunch of hoops to break things up into several workflow field updates).

Answer 3

If you are unable to shave off the extra 221 characters using the advice offered in the other answers, I created an app to deal with formula character limits.

Its called Unleash Your Formula https://appexchange.salesforce.com/appxListingDetail?listingId=a0N4V00000DVyH4UAL, basically all it does is sync a formula field to a "static" (i.e. non-formula) field.

You can thus extract a portion of your formula field into a new formula field, map the new formula to a static field, and reference the static field in the original formula field.

In your case, you can make a checkbox formula (lets call it Is_Partner__c) that will contain the following

OR(
    ISPICKVAL(PartnerTier__c, "Gold"), 
    ISPICKVAL(PartnerTier__c, "Platinum"), 
    ISPICKVAL(PartnerTier__c, "Premier"), 
    ISPICKVAL(PartnerTier__c, "Platinum-Federal"), 
    ISPICKVAL(PartnerTier__c, "Gold-Federal"), 
    ISPICKVAL(ProgramType__c, "Global MB Edition"), 
    ISPICKVAL(ProgramType__c, "MarketBuilder"), 
    ISPICKVAL(ProgramType__c, "MEDITECH"), 
    ISPICKVAL(ProgramType__c, "Velocity"), 
    ISPICKVAL(ProgramType__c, "Velocity-Plus"), 
    ISPICKVAL(ProgramType__c, "MarketBuilder-Velocity"), 
    ISPICKVAL(ProgramType__c, "Premier-VAR"), 
    ISPICKVAL(ProgramType__c, "Premier-Velocity"), 
    ISPICKVAL(ProgramType__c, "MarketBuilder-VAR")
)

Then map this formula field to a regular checkbox field that we'll call Is_Partner_Static__c, then replace the above in your original formula with Is_Partner_Static__c.