1

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
    )
2
  • Instead of using OR formula,try using CONTAINS. For example: CONTAINS('|Gold|Platinum|Premier|', TEXT(ProgramType__c)) Mar 18, 2021 at 15:07
  • @HengkyIlawan While that construct does appear to be semantically valid, it also looks like it takes significantly more compiled characters than the OR(ISPICKVAL()) approach (512 vs 163 in a test in my dev sandbox).
    – Derek F
    Mar 18, 2021 at 15:20

3 Answers 3

2

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')
    )
)
1

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).

7
  • You forgot to mention CASE, which should work here.
    – sfdcfox
    Mar 18, 2021 at 15:26
  • @sfdcfox I did mention CASE(), but testing that approach in my dev sandbox turned out to use more compiled characters than a bunch of ISPICKVAL(). CASE(TEXT(Account_Status__c), 'Active', 1, 'Inactive', 1, 'New', 1, 0 ) = 1 = 212 size, whereas OR( ISPICKVAL(Account_Status__c, 'Active'), ISPICKVAL(Account_Status__c, 'Inctive'), ISPICKVAL(Account_Status__c, 'New') ) came out at 163
    – Derek F
    Mar 18, 2021 at 15:42
  • You don't use TEXT with CASE for picklist values.
    – sfdcfox
    Mar 18, 2021 at 15:44
  • @sfdcfox Huh, thought that was required. Guess that was for using CONTAINS(). Either way, Salesforce appears to optimize that out (which also appears to happen if you try to check a picklist value that doesn't exist). Removing the TEXT() function still has the formula come it at 212 for me.
    – Derek F
    Mar 18, 2021 at 15:46
  • That's interesting. Either CASE has gotten worse, or ISPICKVAL has gotten better. I wonder which one it is. I clearly remember using CASE to clean up formulas that were too big before. I have an Q&A on here somewhere that needs to be updated. 😢
    – sfdcfox
    Mar 18, 2021 at 15:49
1

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.

Not the answer you're looking for? Browse other questions tagged .