1

I have some formulas that use values stored in custom settings. For example If(X < custom setting-y && x >= custom setting , 'foo', 'bar') My formula seems to be fine and my custom setting values are correct but I am getting bar instead of foo.

    If(Email_Open_Rate__c >= $Setup.health_check_console__c.Email_Open_Rate_A_Value__c, 'A', 
If(Email_Open_Rate__c < $Setup.health_check_console__c.Email_Open_Rate_A_Value__c && Email_Open_Rate__c >= $Setup.health_check_console__c.Email_Open_Rate_B_Value__c, 'B', 
If(Email_Open_Rate__c < $Setup.health_check_console__c.Email_Open_Rate_B_Value__c && Email_Open_Rate__c >= $Setup.health_check_console__c.Email_Open_Rate_C_Value__c, 'C', 
If(Email_Open_Rate__c < $Setup.health_check_console__c.Email_Open_Rate_C_Value__c && Email_Open_Rate__c >= $Setup.health_check_console__c.Email_Open_Rate_D_Value__c, 'D', 
'F'))))

enter image description here

When I adjusted the value for C to .199 then my records with a grade of 20% were given a C.

1 Answer 1

2

The main feature of using nested if statements is that you don't need to recheck all of the previous conditions. Here's a revised version:

If(Email_Open_Rate__c >= $Setup.health_check_console__c.Email_Open_Rate_A_Value__c, 'A', 
If(Email_Open_Rate__c >= $Setup.health_check_console__c.Email_Open_Rate_B_Value__c, 'B',
If(Email_Open_Rate__c >= $Setup.health_check_console__c.Email_Open_Rate_C_Value__c, 'C', 
If(Email_Open_Rate__c >= $Setup.health_check_console__c.Email_Open_Rate_D_Value__c, 'D', 
'F'))))

Your formula looks otherwise correct, as far as I can tell, but this version should avoid any rounding errors, which I suspect may be what caused the situation at hand.

2
  • I think I am going to open a case. I tried your revised formula still no change. It seems like a rounding error but in this case, the percentage is 3 / 5. That comes out of 20% no matter how many decimals places I try to add. Sep 17, 2018 at 15:08
  • That's curious. Yeah, if the above formula isn't working, then it's definitely something wrong. I've used this formula at least a few times in the past with success, and I see no reason why it shouldn't.
    – sfdcfox
    Sep 17, 2018 at 15:13

You must log in to answer this question.

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