+ Start a Discussion
LyleDLyleD 

Possible to Build Model Number Field From Multiple Fields using CASE?

First off im very new to salesforce

 

Basically I want to build model number field based on 3 other fields (two of which are picklist). But it seems i can only use one case statement in the forumla field

 

Currently this works

---------------------------------

CASE( Color_Display__c,
"Tri Color", "LSS-TC",
"Full Color", "LSS-FC",
"Single Color", "LSS-SC","")

 

(oh also i dont know why i need the last comma and double quotes at the end of this case. If i leave it "Single Color", "LSS-SC") it gives me error when i check my syntax. It says, "Error: Incorrect number of parameters for function 'CASE()'. Expected 6, received 7" If anyone can explain that to me that would be great. But anyways the above works, i just would like to know why i need the last  ,""

 

So on with the question:

 

CASE( Color_Display__c,
"Tri Color", "LSS-TC",
"Full Color", "LSS-FC",
"Single Color", "LSS-SC","")

 

CASE(Software__c,
"Video Star", Model__c = Model__c & "VS-",
"Image Star", Model__c = Model__c &  "IS-","")

 

Model__c = Model__c & Pitch

 

Also i know it gives an error referencing its own self. This is how i build string in VB or any other programming language. How am i to do this in the formula field?

 


So at the end i just want the model field to read for example

LSS-TCVS-20

if the options Tri Color, Video Star and what ever numeric value is entered into pitch.

 

Thanks for any and all help in advance.

 

Best Answer chosen by Admin (Salesforce Developers) 
Shannon HaleShannon Hale

To address your first question: the format of the CASE() statement is as follows:

 

CASE(expression,value1, result1, value2, result2,..., else_result)

The last argument (the final , "" in your CASE() statement) is the result that will be returned if none of the other values are matched. In a picklist, you may never actually hit that else result -- but on the other hand, if at some point you added another value to the picklist and you forgot to change the formula, it would return that result.

 

So in your case:

 

CASE( 
  Color_Display__c, /* The picklist value we're checking */
  "Tri Color", "LSS-TC", /* If Color_Display__c = "Tri Color", returns "LSS-TC" */
  "Full Color", "LSS-FC", /* If Color_Display__c = "Full Color", returns "LSS-FC" */
  "Single Color", "LSS-SC", /* If Color_Display__c = "Single Color", returns "LSS-SC" */
  "" /* If Color_Display__c is not any of the above, returns "" */
)

 

For your second question: you can use several CASE() statements in a formula and concatenate the results together using &. For your formula, it will look something like this:

 

CASE( 
  Color_Display__c,
  "Tri Color", "LSS-TC",
  "Full Color", "LSS-FC",
  "Single Color", "LSS-SC",
  ""
) 
& CASE(
  "Video Star", "VS-",
  "Image Star", "IS-",
  ""
)
& TEXT( Pitch__c )

 

Formula expression language is more similar to writing an Excel formula than the programming languages you might be used to -- you don't reference the field within itself or like in a function; the value is only calculated at the end.

 

For more information see:

An Introduction to Formulas (Developerforce Wiki): http://wiki.developerforce.com/page/An_Introduction_to_Formulas

& (Concatenate): http://login.salesforce.com/help/doc/en/customize_functions_a_h.htm#Concatenate

CASE(): http://login.salesforce.com/help/doc/en/customize_functions_a_h.htm#CASE

 

 

All Answers

Shannon HaleShannon Hale

To address your first question: the format of the CASE() statement is as follows:

 

CASE(expression,value1, result1, value2, result2,..., else_result)

The last argument (the final , "" in your CASE() statement) is the result that will be returned if none of the other values are matched. In a picklist, you may never actually hit that else result -- but on the other hand, if at some point you added another value to the picklist and you forgot to change the formula, it would return that result.

 

So in your case:

 

CASE( 
  Color_Display__c, /* The picklist value we're checking */
  "Tri Color", "LSS-TC", /* If Color_Display__c = "Tri Color", returns "LSS-TC" */
  "Full Color", "LSS-FC", /* If Color_Display__c = "Full Color", returns "LSS-FC" */
  "Single Color", "LSS-SC", /* If Color_Display__c = "Single Color", returns "LSS-SC" */
  "" /* If Color_Display__c is not any of the above, returns "" */
)

 

For your second question: you can use several CASE() statements in a formula and concatenate the results together using &. For your formula, it will look something like this:

 

CASE( 
  Color_Display__c,
  "Tri Color", "LSS-TC",
  "Full Color", "LSS-FC",
  "Single Color", "LSS-SC",
  ""
) 
& CASE(
  "Video Star", "VS-",
  "Image Star", "IS-",
  ""
)
& TEXT( Pitch__c )

 

Formula expression language is more similar to writing an Excel formula than the programming languages you might be used to -- you don't reference the field within itself or like in a function; the value is only calculated at the end.

 

For more information see:

An Introduction to Formulas (Developerforce Wiki): http://wiki.developerforce.com/page/An_Introduction_to_Formulas

& (Concatenate): http://login.salesforce.com/help/doc/en/customize_functions_a_h.htm#Concatenate

CASE(): http://login.salesforce.com/help/doc/en/customize_functions_a_h.htm#CASE

 

 

This was selected as the best answer
LyleDLyleD

Yes, that works. Thank your for clearification and those links. will be very useful. Ill update the answer to this cause the other one was missing what second case field and my pitch field is a numeric field so needs to be converted. Thanks a bunch. Hope to someday pay it forward.

 

CASE(
  Color_Display__c,
  "Tri Color", "LSS-TC",
  "Full Color", "LSS-FC",
  "Single Color", "LSS-SC",
""
)
& CASE(
Software__c,
  "Video Star", "VS-",
  "Image Star", "IS-",

""
)
& TEXT(Pitch__c)

Shannon HaleShannon Hale
Ah yes - sorry, forgot to wrap the number. I'll update my post.