0

I have the following formula field that uses nested if statements:

IF
(
    AND
    (
        NOT(ISBLANK(Last_SMS_Sent__c)),
        NOT(ISBLANK(MobilePhone)),
        NOT(ISBLANK(Est_Annual_Quantity__c)),
        NOT(ISBLANK(Last_Call_Connect__c)),
        NOT(ISBLANK(Intro_Sent_Id__c))
    ),
    1.00,
    IF
    (
        OR
        (   
            AND
            (
                NOT(ISBLANK(Last_SMS_Sent__c)),
                NOT(ISBLANK(MobilePhone)),
                NOT(ISBLANK(Est_Annual_Quantity__c)),
                NOT(ISBLANK(Last_Call_Connect__c))              
            ),
            AND
            (
                NOT(ISBLANK(MobilePhone)),
                NOT(ISBLANK(Est_Annual_Quantity__c)),
                NOT(ISBLANK(Last_Call_Connect__c)),
                NOT(ISBLANK(Intro_Sent_Id__c))
            ),
            AND
            (
                NOT(ISBLANK(Last_SMS_Sent__c)),
                NOT(ISBLANK(Est_Annual_Quantity__c)),
                NOT(ISBLANK(Last_Call_Connect__c)),
                NOT(ISBLANK(Intro_Sent_Id__c))      
            ),
            AND
            (
                NOT(ISBLANK(Last_SMS_Sent__c)),
                NOT(ISBLANK(MobilePhone)),
                NOT(ISBLANK(Last_Call_Connect__c)),
                NOT(ISBLANK(Intro_Sent_Id__c))          
            ),
            AND
            (
                NOT(ISBLANK(Last_SMS_Sent__c)),
                NOT(ISBLANK(MobilePhone)),
                NOT(ISBLANK(Est_Annual_Quantity__c)),
                NOT(ISBLANK(Intro_Sent_Id__c))          
            )
        ),
        0.80,
        IF
        (
            OR
            (
                AND
                (
                    NOT(ISBLANK(Last_SMS_Sent__c)),
                    NOT(ISBLANK(MobilePhone)),
                    NOT(ISBLANK(Est_Annual_Quantity__c))                
                ),
                AND
                (
                    NOT(ISBLANK(MobilePhone)),
                    NOT(ISBLANK(Est_Annual_Quantity__c)),
                    NOT(ISBLANK(Last_Call_Connect__c))      
                ),
                AND
                (
                    NOT(ISBLANK(Est_Annual_Quantity__c)),
                    NOT(ISBLANK(Last_Call_Connect__c)),
                    NOT(ISBLANK(Intro_Sent_Id__c))              
                ),
                AND
                (
                    NOT(ISBLANK(Last_SMS_Sent__c)),
                    NOT(ISBLANK(Est_Annual_Quantity__c)),
                    NOT(ISBLANK(Last_Call_Connect__c))              
                ),
                AND
                (
                    NOT(ISBLANK(Last_SMS_Sent__c)),
                    NOT(ISBLANK(Last_Call_Connect__c)),
                    NOT(ISBLANK(Intro_Sent_Id__c))              
                ),
                AND
                (
                    NOT(ISBLANK(MobilePhone)),
                    NOT(ISBLANK(Last_Call_Connect__c)),
                    NOT(ISBLANK(Intro_Sent_Id__c))              
                ),
                AND
                (
                    NOT(ISBLANK(Last_SMS_Sent__c)),
                    NOT(ISBLANK(MobilePhone)),
                    NOT(ISBLANK(Last_Call_Connect__c))              
                ),
                AND
                (
                    NOT(ISBLANK(MobilePhone)),
                    NOT(ISBLANK(Est_Annual_Quantity__c)),
                    NOT(ISBLANK(Intro_Sent_Id__c))              
                ),
                AND
                (
                    NOT(ISBLANK(Last_SMS_Sent__c)),
                    NOT(ISBLANK(Est_Annual_Quantity__c)),
                    NOT(ISBLANK(Intro_Sent_Id__c))              
                ),
                AND
                (
                    NOT(ISBLANK(Last_SMS_Sent__c)),
                    NOT(ISBLANK(MobilePhone)),
                    NOT(ISBLANK(Intro_Sent_Id__c))              
                )
            ),
            0.60,
            IF
            (
                OR
                (
                    AND
                    (
                        NOT(ISBLANK(Last_SMS_Sent__c)),
                        NOT(ISBLANK(MobilePhone))               
                    ),
                    AND
                    (
                        NOT(ISBLANK(MobilePhone)),
                        NOT(ISBLANK(Est_Annual_Quantity__c))        
                    ),
                    AND
                    (
                        NOT(ISBLANK(Est_Annual_Quantity__c)),
                        NOT(ISBLANK(Last_Call_Connect__c))              
                    ),
                    AND
                    (
                        NOT(ISBLANK(Last_SMS_Sent__c)),
                        NOT(ISBLANK(Est_Annual_Quantity__c))                
                    ),
                    AND
                    (
                        NOT(ISBLANK(Last_Call_Connect__c)),
                        NOT(ISBLANK(Intro_Sent_Id__c))              
                    ),
                    AND
                    (
                        NOT(ISBLANK(Last_SMS_Sent__c)),
                        NOT(ISBLANK(Est_Annual_Quantity__c))                
                    ),
                    AND
                    (
                        NOT(ISBLANK(Last_SMS_Sent__c)),
                        NOT(ISBLANK(Last_Call_Connect__c))                  
                    ),
                    AND
                    (
                        NOT(ISBLANK(Last_SMS_Sent__c)),
                        NOT(ISBLANK(Intro_Sent_Id__c))                  
                    ),
                    AND
                    (
                        NOT(ISBLANK(MobilePhone)),
                        NOT(ISBLANK(Last_Call_Connect__c))                  
                    ),
                    AND
                    (
                        NOT(ISBLANK(MobilePhone)),
                        NOT(ISBLANK(Intro_Sent_Id__c))                                      
                    ),
                    AND
                    (
                        NOT(ISBLANK(Est_Annual_Quantity__c)),
                        NOT(ISBLANK(Intro_Sent_Id__c))                  
                    )
                ),
            0.40,
            IF
            (
                OR
                (
                    NOT(ISBLANK(Last_SMS_Sent__c)),
                    NOT(ISBLANK(MobilePhone)),
                    NOT(ISBLANK(Est_Annual_Quantity__c)),
                    NOT(ISBLANK(Last_Call_Connect__c)),
                    NOT(ISBLANK(Intro_Sent_Id__c))                  
                ),
                0.20,0.0
            )   
        )
    )
)

I've exceeded the max character length allowed in the formula. I'm trying to improve the algebraic logic so there isn't repetitive expressions and condense the number of characters used. I was trying to use a case statement instead, but no dice so far. When I removed the branch for the 20%, the formula saved and worked as intended, but it was missing the logic for calculating the 20%. Any formula gurus have a recommended better approach?

Improve this question
5
  • 3
    Could you not just generate a count of the non-blank fields and work from there?
    – Phil W
    Commented Feb 6 at 21:43
  • Thank you @Phil W...that pointed me in the right direction. This is what I changed it to: CASE ( IF(NOT(ISBLANK(Last_SMS_Sent__c)),1,0) + IF(NOT(ISBLANK(MobilePhone)),1,0) + IF(NOT(ISBLANK(Est_Annual_Quantity__c)),1,0) + IF(NOT(ISBLANK(Last_Call_Connect__c)),1,0) + IF(NOT(ISBLANK(Intro_Sent_Id__c)),1,0), 5,1.00, 4,0.80, 3,0.60, 2,0.40, 0.0 )
    – user1718612
    Commented Feb 6 at 21:57
  • Exactly. Glad you have a solution!
    – Phil W
    Commented Feb 6 at 21:58
  • 2
    You can reduce further by doing IF(ISBLANK(MyField__c), 0, 1)
    – David Cheng
    Commented Feb 6 at 22:04
  • 3
    Why don't you post the solution to your own problem as the actual solution? That will mark the question as answered and will prevent people from starting to work on it while it's actually already resolved. And also it will give you some exp points :-)
    – Guy Clairbois
    Commented Feb 6 at 22:32

2 Answers 2

Reset to default
1

Counting how many fields are not blank is definitely a superior approach to enumerating every possible combination of non-blank fields.

The CASE() approach is fine, but now that I take a look at it, it can be improved further.

Simply dividing your count by 5 would get the job done. I.e.

(
    IF(ISBLANK(Last_SMS_Sent__c), 0, 1) 
    + IF(ISBLANK(MobilePhone), 0, 1)
    + IF(ISBLANK(Est_Annual_Quantity__c), 0, 1)
    + IF(ISBLANK(Last_Call_Connect__c), 0, 1)
    + IF(ISBLANK(Intro_Sent_Id__c), 0, 1)
) / 5

Since the only thing that can be 0 is in the numerator, this is safe to do.
As long as your formula is set to type Number with 2 decimal places, you'll get a Decimal result (Salesforce won't do any rounding to give you an Integer result).

The parens surrounding the IF()s is necessary due to operator precedence. The division would otherwise be done before the rest of the additions, and you would end up with a result like 3.2.

As a bonus, this approach is also ~90 compiled characters shorter than the CASE() approach. That could end up being important if you end up using this formula in other formulas (which adds the entire cost of the nested formula to the formula it's being used in, plus a little extra overhead).

Improve this answer
1

After re-working the formula from @Phip W suggestion, I vastly reduced the number of characters. Here is the improved formula for reference to others encountering a similar issue:

CASE(
    IF(ISBLANK(Last_SMS_Sent__c), 0, 1) 
        + IF(ISBLANK(MobilePhone), 0, 1)
        + IF(ISBLANK(Est_Annual_Quantity__c), 0, 1)
        + IF(ISBLANK(Last_Call_Connect__c), 0, 1)
        + IF(ISBLANK(Intro_Sent_Id__c), 0, 1),
    5, 1.00,
    4, 0.80,
    3, 0.60,
    2, 0.40,
    1, 0.20,
    0.0
)
Improve this answer
1
  • I've adjusted the formatting in the hopes that it makes it more clear that the sequence of IF() + IF() is the first argument, the "expression" for the CASE(). Hope that's alright.
    – Derek F
    Commented Feb 7 at 23:19

You must log in to answer this question.

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