function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Sara-CASASara-CASA 

nested case - what's wrong?

I'm trying to make the following case statement work:

CASE( Actual_Shipping_Cost__c ,
null, CASE (Shipping_Method__c ,
    "Pick up" , 0, 
    "Media mail", CASE ( Order_Number_of_Items__c,
        1, 3, 
        2, 3,
        3, 3,
        4, 5,
        5, 5,
        6, 5,
        7, 5,
        8, 5,
        9, 5,
        10, 7,
        11, 7,
        12, 7,
        13, 7,
        14, 7,
        15, 7,
        16, 10,
        17, 10,
        18, 10,
        19, 10,
        20, 10,
        21, 10,
        22, 10,
        23, 10,
        24, 10,
        25, 10,
        26, 12,
        27, 12,
        28, 12,
        29, 12,
        30, 12,
        31, 12,
        32, 12,
        33, 12,
        34, 12,
        35, 12,
        (( Order_Weight__c +1 ) * 0.39 ) + 1.99 + 0.8 
        ),
    Actual_Shipping_Cost__c
    ),
Actual_Shipping_Cost__c 
)

 there are no syntax errors, but it always returns 0 when Actual Shipping Cost is null unless I take out a couple of the cases, leaving:

CASE( Order_Number_of_Items__c,
        1, 3, 
        2, 3,
        3, 3,
        4, 5,
        5, 5,
        6, 5,
        7, 5,
        8, 5,
        9, 5,
        10, 7,
        11, 7,
        12, 7,
        13, 7,
        14, 7,
        15, 7,
        16, 10,
        17, 10,
        18, 10,
        19, 10,
        20, 10,
        21, 10,
        22, 10,
        23, 10,
        24, 10,
        25, 10,
        26, 12,
        27, 12,
        28, 12,
        29, 12,
        30, 12,
        31, 12,
        32, 12,
        33, 12,
        34, 12,
        35, 12,
        (( Order_Weight__c +1 ) * 0.39 ) + 1.99 + 0.8 
        ),
 )

 

any thoughts?  i know its kind of complicated, couldn't figure out how to return 12 for 26-35 without listing them all separately, but that part at least works!

 

Thanks!
Sara

Steve :-/Steve :-/

What are the datatypes of the fields that you are evaluating in your formula?  

A few things you might want to look into:  

If they are numeric fields and you are performing an Arithmetic function you need to look out for things like Divided by 0 and stuff like that.  Also you need to make sure you have "Treat Blank Fields as..." set.  

If the field is a Picklist you need to make sure that the values in your Formula matches the Value in the Picklist EXACTLY (spelling, punctuation, upper/lower case, blank spaces, etc.)   
Sara-CASASara-CASA

I checked the picklist values already.  All the fields are number fields (except the picklist), treat blank fields as 0.
Steve :-/Steve :-/

Picklist Fields don't store numeric values (even if they are numbers) you need to put quotes around the Picklist value that your formula is evaluating.  

 

Also, you might want to try using a VALUE function so that you can use an IF function and group them together like this 

 

IF(VALUE(Order_Number_of_Items__c) >= 26, 12,
IF(VALUE(Order_Number_of_Items__c) >= 16, 10,
IF(VALUE(Order_Number_of_Items__c) >= 10, 7,
IF(VALUE(Order_Number_of_Items__c) >= 4, 5,
IF(VALUE(Order_Number_of_Items__c) >= 1, 3, NULL
)))))

 

 
Steve :-/Steve :-/

Are you all set now, or do you still need help with anything?