I need help with a Case formula that includes a picklist - Answers - Salesforce Trailblazer Community
Ask Search:
Peter MartensenPeter Martensen 

I need help with a Case formula that includes a picklist

I need a formula that looks at a picklist to see if it contains values, not exact values, and returns a different value.  I can't figure out how to do it with a CASE formula, and INCLUDES only works for MSP's.  If the picklist "Line_of_Service__c" contains "Charles City", return "Drug Substance":  if it contains "Durham", return "Early Testing": etc.  I tried the code I'm attaching, but I get an error that the answer is Boolean.  Can someone help with this?
CASE( Line_of_Service__c ,
CONTAINS(TEXT(Line_of_Service__c),"Charles City"), "Drug Substance",
CONTAINS(TEXT(Line_of_Service__c),"Durham"),"Early Testing",
""
)

 
Best Answer chosen by Peter Martensen
Sunil SarillaSunil Sarilla
Hi Peter,
I dont think you will be able to use CONTAINS and CASE function in the context you are using, you could use either of them.
just using the CONTAINS function, the formula will be
IF(CONTAINS(TEXT(Line_of_Service__c),"Charles City"), "Drug Substance",
IF(CONTAINS(TEXT(Line_of_Service__c),"Durham"),"Early Testing",NULL))
if you are using the CASE Function, you will need to use the full picklist value in the formula
CASE(Line_of_Service__c,
'Full Value that contains Charles City', 'Drug Substance',
'Full Value that contains Durham', 'Early Testing',
NULL)


 

All Answers

Sunil SarillaSunil Sarilla
Hi Peter,
I dont think you will be able to use CONTAINS and CASE function in the context you are using, you could use either of them.
just using the CONTAINS function, the formula will be
IF(CONTAINS(TEXT(Line_of_Service__c),"Charles City"), "Drug Substance",
IF(CONTAINS(TEXT(Line_of_Service__c),"Durham"),"Early Testing",NULL))
if you are using the CASE Function, you will need to use the full picklist value in the formula
CASE(Line_of_Service__c,
'Full Value that contains Charles City', 'Drug Substance',
'Full Value that contains Durham', 'Early Testing',
NULL)


 
This was selected as the best answer
Dave RiesDave Ries
Hmmm... where are you putting this formula?  Is it a formula field?  Or in a Flow?  Either way... when you declare that you're creating a Formula, you usually have to also declare what type of value the Formula will return.  Is it possible that when you declared the Formula Field or Flow Formula, you checked the box that would indicate the result will be Boolean?  If so... you'd get an error message along the lines that your formula is not returning a Boolean value as expected.