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
manliamanlia 

How to use CASE and BEGINS for Picklist field

Hi,

 

How do you combine a CASE function with BEGINS for certain characters in a picklist field?

Below is the formula that is working now: 

CASE( Industry_Size_Tier__c , "1_Tier 5", -1 , "2_Tier 4", 0, "3_Tier 1/3_Tier 2/3_Tier 3",1,0)

 

basicaly it returns:

If "1_Tier 5" selected, it will return number -1.

If "2_Tier 4" is selected, it will return number 0.

If "3_Tier 1/3_Tier 2/3_Tier 3", it will return number 1.

 

I'd like to simplify and possibly reduce the compile characters if I could just use the "BEGINS" function using the common characters which BEGINS with "3_" strings instead of spelling out all the "3_Tier 1/3_Tier 2/3_Tier 3"strings for the last expression above.

 

Please HELP.

 

Thanks,

Lia

 

wchristnywchristny

BEGINS returns TRUE and FALSE whereas you want to return 1 and 0 respectively.  How about putting the BEGINS clause as the default case and surrounding it with an IF clause that will translate TRUE to 1 and FALSE to 0?

 

CASE( Industry_Size_Tier__c , "1_Tier 5", -1 , "2_Tier 4", 0, IF( BEGINS( TEXT(Industry_Size_Tier__c), "3_"), 1, 0) )

 

I'm not sure if it will work, but it's worth a shot.

 
manliamanlia

Hi wchristny,

Thank you for your help.  However, when I plug-in the formula and it's missing ")" which I did add it, but it keeps saying that it's still missing.  I had this problem before also.

 

Thanks again,

Lia
wchristnywchristny

Sorry about the emoticon.  This is the way it should have looked:

 

CASE( Industry_Size_Tier__c, "1_Tier 5", -1, "2_Tier 4", 0, IF( BEGINS( TEXT(Industry_Size_Tier__c), "3_"), 1, 0) )
 
 


manliamanlia
Thanks wchristny.  That works.  However, the compile size is so large (403 char).
I have to reference several of this formula to another formula.  Hopefully it doesn't exceed the 5000 char.
Do you know if that's the only max char a formula field can have in Salesfore?
How does it work with Visual Force?  I've never used this before.
 
Thanks again!
Lia


wchristnywchristny
I believe the 5000 limit is per formula, not the sum total of all formulas.  I have a custom object with formulas that would have passed the limit if it was based off the combined total.  So as long as an individual formula doesn't push the limit, you should be okay.
 
FYI:  Apex has a combined limit.  You can find that at the top of the Apex Classes or Apex Triggers pages.


manliamanlia
Hi,
 
I have one more question.  can you put the  IF( BEGINS( TEXT())) more than one inside within the CASE statement?
I do have more than one selection to do with similar strings in 1 formula.
I tried to insert another one of it, but it gives me "Error: Incorrect number of parameters for function CASE(). Expected 8, received 9."
This below is what I have:
CASE( Industry_Fit__c, "1_", -3,"3_",-1, "4_",0,IF( BEGINS( TEXT(Industry_Fit__c), "2_"), -2, 0),
IF( BEGINS( TEXT(Industry_Fit__c), "5_"), -1, 0) )
 
Thanks again,
Lia


wchristnywchristny
Your second IF (BEGINS... clause needs to be the "false" condition of the first IF clause.  It should replace the zero from the first IF clause, like this:
 
 
CASE( Industry_Fit__c, "1_", -3,"3_",-1, "4_",0,IF( BEGINS( TEXT(Industry_Fit__c), "2_"), -2, IF( BEGINS( TEXT(Industry_Fit__c), "5_"), -1, 0) ) )
 
There may also be another option available to you.  If you can safely assume that the first two characters of the picklist entry always dictate what should happen, you could isolate the first two characters of the picklist entry up front and not have to deal with IF or BEGINS clauses.
 
CASE( MID( TEXT(Industry_Fit__c), 1, 2), "1_", -3, "3_", -1, "4_", 0, "2_", -2, "5_", -1, 0)