+ Start a Discussion
Michaela PrambsMichaela Prambs 

CASE formula for picklist doesn't work

hi,

I was trying to use the CASE formula for my picklist and tried almost everything and was also looking into this Forum here but haven't found a solution.

Here is my code:

IF(CASE( Select_Units__c,"Imperial",2.34, IF( Select_Units__c ,"SI",3.22,FALSE),

IF(CASE( Select_Product__c,"Apples",3.45, IF( Select_Product__c ,"Fish,Scampi",3.60,3.48),

IF(CASE( Select_Units__c,"Imperial","KJ/KG*C", IF( Select_Units__c ,"SI","Btu/lb*F")))))))

And the error message always is: "Error: Field Select_Units__c is a picklist field. Picklist fields are only supported in certain functions. Tell me more"
Anju Chacko QBAnju Chacko QB
Try use Text(Select_Units__c)

that is 

IF(CASE( Text(Select_Units__c),"Imperial",2.34, IF( Text(Select_Units__c) ,"SI",3.22,FALSE),....
Michaela PrambsMichaela Prambs
Now there is a new error message:

"Error: Incorrect number of parameters for function 'IF()'. Expected 3, received 4"

Code:

IF(CASE(Text(Select_Units__c),"Imperial",2.34, IF(Text(Select_Units__c),"SI",3.22,FALSE),

IF(CASE(Text(Select_Product__c),"Apples",3.45, IF(Text(Select_Product__c),"Fish,Scampi",3.60,3.48),

IF(CASE(Text(Select_Units__c),"Imperial","KJ/KG*C", IF(Text(Select_Units__c),"SI","Btu/lb*F")))))))
Shweta_AgarwalShweta_Agarwal
Hi Michaela,

For picklist filed you should use ISPICKVAL in formula field.
So in your formula where ever you are checking Select_Units__c filed's value you should use ISPICKVAL(Select_Units__c,"Imperial") 

Hpoe this will solve your issue.

Thanks,
Shweta
Pramodh KumarPramodh Kumar
for retreive the picklist values in the formula you need to use 

ISPICKVAL,
Case,
Text

Here are the example for the picklist formula and let me know if you need any other help

Example:1
if(ISPICKVAL(Select_Units__c,'Imperial'),'3.22','false')

Example: 2
CASE(Text(Reason) , 
"Installation", "stage is prospecting", 
"Equipment Complexity", "stage is qualification", 
"Performance", "stage is needs analysis", 
"Breakdown", "stage is value proposition", 
"Equipment Design", "stage is closed won", 
"Feedback", "stage is closed lost", 
"")



Thanks,
pRAMODH
Michaela PrambsMichaela Prambs
Now another lovely error message -.- I have no idea why but it keeps saying:  "Error: Syntax error. Missing ')'"
Although all the open Brackets are already closed

Here's my code. Looks so awful -.-

IF(ISPICKVAL(Select_Units__c,"Imperial")6.78))))), IF(ISPICKVAL(Select_Units__c,"SI")3.22,False,

IF(ISPICKVAL(Select_Product__c,"Apples")3.45))))), IF(ISPICKVAL(Select_Product__c,"Fish,Scampi")3.60,3.48,

IF(ISPICKVAL(Select_Units__c,"Imperial")"KJ/KG*C"))))), IF(ISPICKVAL(Select_Units__c,"SI")"Btu/lb*F")))))))
Michaela PrambsMichaela Prambs
Also this page doesn't help.
https://help.salesforce.com/HTViewHelpDoc?id=tips_for_using_picklist_formula_fields.htm&language=en_US
Shweta_AgarwalShweta_Agarwal
Try using this
IF(ISPICKVAL(Select_Units__c,"Imperial"),6.78,IF(ISPICKVAL(Select_Units__c,"SI"),3.22,IF(ISPICKVAL(Select_Product__c,"Apples"),3.45,IF(ISPICKVAL(Select_Product__c,"Fish,Scampi"),3.60,3.48))))
Michaela PrambsMichaela Prambs
Never ending story :(

Error code: Error: Incorrect number of parameters for function 'IF()'. Expected 3, received 2

Code:
IF(ISPICKVAL(Select_Units__c,"Imperial"),6.78,IF(ISPICKVAL(Select_Units__c,"SI"),3.22,IF(ISPICKVAL(Select_Product__c,"Apples"),3.45,IF(ISPICKVAL(Select_Product__c,"Fish,Scampi"),3.60,3.48,IF(ISPICKVAL(Select_Units__c,"Imperial"),"KJ/KG*C",IF(ISPICKVAL(Select_Units__c,"SI"),"Btu/lb*F"))))))
Shweta_AgarwalShweta_Agarwal
May be this will work for you....

IF(ISPICKVAL(Select_Units__c,"Imperial"),6.78,IF(ISPICKVAL(Select_Units__c,"SI"),3.22,IF(ISPICKVAL(Select_Product__c,"Apples"),3.45,IF(ISPICKVAL(Select_Product__c,"Fish,Scampi"),3.60,
IF(ISPICKVAL(Select_Units__c,"Imperial"),"KJ/KG*C",IF(ISPICKVAL(Select_Units__c,"SI"),"Btu/lb*F",3.48))))))
Michaela PrambsMichaela Prambs
No sorry :(
Not allowed to mix numbers and text in the same cell.
Pramodh KumarPramodh Kumar
please use single quotes for text fields and see the below screen shot and let me know you still have any issues,User-added image


Thanks,
Pramodh.
Michaela PrambsMichaela Prambs
I have now tried something different because there are always two items which need to match.
Can you tell me why the following code is wrong?

IF(ISPICKVAL(Select_Units__c,"Imperial" && IF(ISPICKVAL(Select_Product__c,"Apples" )0.86))),IF(ISPICKVAL(Select_Units__c,"SI" && IF(ISPICKVAL(Select_Product__c,"Apples")3.60))),IF(ISPICKVAL(Select_Units__c,"Imperial" && IF(ISPICKVAL(Select_Product__c,"Fish,Scampi")0.83))),IF(ISPICKVAL(Select_Units__c,"SI" && IF(ISPICKVAL(Select_Product__c,"Fish,Scampi")3.48))),IF(ISPICKVAL(Select_Units__c,"Imperial" && IF(ISPICKVAL(Select_Product__c,"Milk,(Skim)0.94))),IF(ISPICKVAL(Select_Units__c,"SI" && IF(ISPICKVAL(Select_Product__c,"Milk,(Skim)3.95)))

Thanks a lot again for your help!
Michaela PrambsMichaela Prambs
And here is sth. else although I have an "else"-statement....

IF(ISPICKVAL(Select_Units__c,"Imperial"),44.3,IF(ISPICKVAL(Select_Units__c,"SI"),35.8,IF( Product_piece_weight__c <= 10),54.3,65.9))

error:Error: Incorrect number of parameters for function 'IF()'. Expected 3, received 1
Jorge OrtegaJorge Ortega
Hi Michaela,

I used to write these complex formulas outside the salesfoce online editor, using a text editor to help me with syntax coloring.

Here you have the error
.... IF( Product_piece_weight__c <= 10 ,54.3 ....