You need to sign in to do that

Don't have an account?

Michaela 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"

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 QBTry 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 PrambsNow 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_AgarwalHi 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 Kumarfor 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 PrambsNow 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 PrambsAlso this page doesn't help. https://help.salesforce.com/HTViewHelpDoc?id=tips_for_using_picklist_formula_fields.htm&language=en_US

Shweta_AgarwalTry 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 PrambsNever 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_AgarwalMay 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 PrambsNo sorry :( Not allowed to mix numbers and text in the same cell.

Pramodh Kumarplease use single quotes for text fields and see the below screen shot and let me know you still have any issues,

Thanks,

Pramodh.

Michaela PrambsI 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 PrambsAnd 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 OrtegaHi 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 ....