Formula Tip: Using Picklist fields with the CASE() function
Feb 1, 2006
Those of you experienced formula authors out there have probably groaned more than a few times when building advanced conditional logic based on a picklist field value. In the previous release of custom formulas, you had to build nested IF() functions with embedded ISPICKVAL() functions in order to accomplish this feat.
Well, there’s good news for you in the Winter ’06 release: You can now use picklist fields directly with the CASE() function. No ISPICKVAL() required. This has a number of big advantages:
- The formula is easier to create and read
- Using CASE can result in a significantly smaller compiled size, which is important for complex formulas that are approaching the 4K max compiled size limit.
Converting your existing picklist formulas to use the CASE function is easy. Here’s an example formula based on the Opportunity Stage picklist to use as a model:
Old approach:
IF( ISPICKVAL({!Stagename},
"Prospecting"), "stage is prospecting", IF( ISPICKVAL({!Stagename}, "Qualification"), "stage is
qualification", IF( ISPICKVAL({!Stagename}, "Needs
Analysis"), "stage is needs analysis", IF( ISPICKVAL({!Stagename}, "Value Proposition"), "stage is value
proposition", IF( ISPICKVAL({!Stagename}, "Closed Won"), "stage
is closed won", IF( ISPICKVAL({!Stagename}, "Closed Lost"),
"stage is closed lost", ""))))))
Compiled size: 445 characters
New Winter '06 approach:
CASE({!Stagename},
"Prospecting", "stage is prospecting",
"Qualification", "stage is qualification",
"Needs Analysis", "stage is needs analysis",
"Value Proposition", "stage is value proposition",
"Closed Won", "stage is closed won",
"Closed Lost", "stage is closed lost",
"")
Compiled size: 376 characters
Tips:
- For readability, consider putting each matching value and result pair on a separate line (as in the example above).
- Don’t forget the final argument of the CASE function: the default value to use if none of the specified values are matched.
Bookmark
Major improvement and help. Easier to write & understand. I just converted a large formula consisting of several nested if statements, each referencing the same pick val field. This makes it much easier to read, and I reduced my compiled size by 40%+!
Posted by: John Johnson | February 20, 2006 at 09:40 AM
We just installed it and cannot get it to work. Perhaps it is because it does not include multi-currency support.
It is good to hear that sf will be adding some quoting functionality. We got the products option thinking that we could generate quotes but that is not the case. The products option is really useless for us if we cannot generate quotes.
One thing I did not see is the ability to have different metrics for the same product. For example per user and per processor. On the same order, it is very possible that one product would be quoted by processor and the other by user. Hope this feature is included.
The products feature seems to be designed specifically for companies that have inventory. There are lots of users out there like us that sell software licences that come from various vendors. Please keep us in mind.
Thanks.
Posted by: Rebecca E. | March 02, 2006 at 03:43 AM
at location http://blog.crmsuccess.com/crmsuccess/2006/02/formula_tip_usi.html
via nested pickList search {google}.
The formula 'talking of' is the code presented? Not pickList of formulaS..
Looking for VB code
your focus is mainly "SalesForce" softWare product?
thankYou.
Posted by: marvin | March 20, 2006 at 10:10 PM
We have a custom object "Assessment" it has about 30 fields from which users need to select Gold,Silver,Bronze
I am hoping to use a formula to score the object overall.
Do I need to create a seperate custom field to calculate each and every fields value to then use in a calculation for overall score?
or is it possible to do this in 1 formula for the entire object? I hope that is not to confusing!
Posted by: Krishnan | April 05, 2006 at 11:05 PM
I read the blogs again and the solution was to make a custom "score" field for each field, along the lines of:
if ( ISPICKVAL( {!Assessment_detail1__c} , "Gold"), 100,
if ( ISPICKVAL( {!Assessment_detail2__c} , "Silver"), 70,
if ( ISPICKVAL( {!Assessment_detail2__c} , "Bronze"), 30, 0)))
Then create another formula to sum them up... easy!
Posted by: Krishnan | April 12, 2006 at 11:02 PM
What if your picklist values are numbers technically. On the backend, they are text, but I cannot use the CASE function and have it yield number results. if I use the ISPICKVAL function, my field does not show up as a field that I can sum or average.
Help.
Posted by: Marc Brandenburg | December 04, 2006 at 08:36 PM
I have a pick list of times
8:00 AM
8:30 AM
etc
I need to convert these values to TEXT and then TIME in oder to create a formula field Date + Time + Date Time
Any thoughts
Posted by: Eric | June 28, 2009 at 02:01 PM
Trying to get a Case formula on the Account using a checkbox to work so I can use the values to display on another object
Here's the syntax:
CASE(Master_Agreement_Signed__c, "True",
"Yes", "False", "No", "")
However, when I check the syntax I get the message: Error: Incorrect argument type for function CASE().
Ultimately, I want to display Yes or No on the Contact page as to whether this box was checked or not on the Account.
Am doing something wrong here or is this not possible?
Posted by: Chris | August 25, 2009 at 12:13 PM