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
EtaussigEtaussig 

Help with ISPICKVAL IF AND FORMULA

I am trying to write a formula which compares 2 criteria, for quarterly reporting purposes.  One is a picklist value and one is a text formula field.  The scenario is this:

 

If ISPICKVAL = "MGM" and EXHIBITION MONTH = "JAN" THEN "MAR", EXHIBITION MONTH

 

OR

 

If ISPICKVAL = "MGM" and EXHIBITION MONTH = "FEB" THEN "MAR", EXHIBITION MONTH

 

OR

 

If ISPICKVAL = "MGM" and EXHIBITION MONTH = "APR" THEN "JUN", EXHIBITION MONTH...etc

 

Please help if you can.  Thanks!

Best Answer chosen by Admin (Salesforce Developers) 
Steve :-/Steve :-/

How about now?

 

 

IF(AND(ISPICKVAL( Picklist , "A"), FormulaField = "A"), "Result A",
IF(AND(ISPICKVAL( Picklist , "B"), FormulaField = "B"), "Result B",
IF(AND(ISPICKVAL( Picklist , "C"), FormulaField = "C"), "Result C", 
FormulaField )))
 
Message Edited by Stevemo on  01-19-2010 06:04 PM


All Answers
Steve :-/Steve :-/
Sounds like you want a formula something like this:
 
IF(AND(ISPICKVAL( Picklist , "A"), FormulaField = "A"), "Result A",
IF(AND(ISPICKVAL( Picklist , "B"), FormulaField = "B"), "Result B",
IF(AND(ISPICKVAL( Picklist , "C"), FormulaField = "C"), "Result C", 
FormulaField )))
 
 
Message Edited by Stevemo on  01-19-2010 03:19 PM


EtaussigEtaussig
I Can't see your formula :)


Steve :-/Steve :-/
How about now?
 
 
IF(AND(ISPICKVAL( Picklist , "A"), FormulaField = "A"), "Result A",
IF(AND(ISPICKVAL( Picklist , "B"), FormulaField = "B"), "Result B",
IF(AND(ISPICKVAL( Picklist , "C"), FormulaField = "C"), "Result C", 
FormulaField )))
 
Message Edited by Stevemo on  01-19-2010 06:04 PM
This was selected as the best answer
EtaussigEtaussig
Yes. I can see it.  I'll give it shot.  Thanks so much!


EtaussigEtaussig
Thanks Stevemo!!!  That worked perfectly.


EtaussigEtaussig
Ok.  SO now I have a new problem.  Stevemo's solution worked great, here is what I have:
 
 
IF(AND(ISPICKVAL( Studio__c , "MGM"), EXHIBITION_MONTH__c = "JAN"), "MAR",IF(AND(ISPICKVAL( Studio__c , "MGM"), EXHIBITION_MONTH__c = "FEB"), "MAR",IF(AND(ISPICKVAL( Studio__c , "MGM"), EXHIBITION_MONTH__c = "APR"), "JUN", IF(AND(ISPICKVAL( Studio__c , "MGM"), EXHIBITION_MONTH__c = "MAY"), "JUN", IF(AND(ISPICKVAL( Studio__c , "MGM"), EXHIBITION_MONTH__c = "JUL"), "SEP", IF(AND(ISPICKVAL( Studio__c , "MGM"), EXHIBITION_MONTH__c = "AUG"), "SEP", IF(AND(ISPICKVAL( Studio__c , "MGM"), EXHIBITION_MONTH__c = "OCT"), "DEC", IF(AND(ISPICKVAL( Studio__c , "MGM"), EXHIBITION_MONTH__c = "NOV"), "DEC",EXHIBITION_MONTH__c ))))))))
 
But now I need to do this for 5 different studios, and when I add the second studio, I get the error message:
Error: Compiled formula is too big to execute (6,554 characters). Maximum size is 5,000 characters
 
Does anyone have any thoughts as to how I can make this formula smaller?  Maybe using Contains?
 
Thanks in advance for any help.
 
IF(AND(ISPICKVAL(  Studio__c  , "MGM"),  EXHIBITION_MONTH__c = "JAN"), "MAR",IF(AND(ISPICKVAL(  Studio__c  , "MGM"),  EXHIBITION_MONTH__c  = "FEB"), "MAR",IF(AND(ISPICKVAL(  Studio__c  , "MGM"),  EXHIBITION_MONTH__c  = "APR"), "JUN",  IF(AND(ISPICKVAL(  Studio__c  , "MGM"),  EXHIBITION_MONTH__c  = "MAY"), "JUN",  IF(AND(ISPICKVAL(  Studio__c  , "MGM"),  EXHIBITION_MONTH__c  = "JUL"), "SEP",  IF(AND(ISPICKVAL(  Studio__c  , "MGM"),  EXHIBITION_MONTH__c  = "AUG"), "SEP",  IF(AND(ISPICKVAL(  Studio__c  , "MGM"),  EXHIBITION_MONTH__c  = "OCT"), "DEC", IF(AND(ISPICKVAL(  Studio__c  , "MGM"),  EXHIBITION_MONTH__c  = "NOV"), "DEC",
 
IF(AND(ISPICKVAL(  Studio__c  , "ICON"),  EXHIBITION_MONTH__c = "JAN"), "MAR",IF(AND(ISPICKVAL(  Studio__c  , "ICON"),  EXHIBITION_MONTH__c  = "FEB"), "MAR",IF(AND(ISPICKVAL(  Studio__c  , "ICON"),  EXHIBITION_MONTH__c  = "APR"), "JUN",  IF(AND(ISPICKVAL(  Studio__c  , "ICON"),  EXHIBITION_MONTH__c  = "MAY"), "JUN",  IF(AND(ISPICKVAL(  Studio__c  , "ICON"),  EXHIBITION_MONTH__c  = "JUL"), "SEP",  IF(AND(ISPICKVAL(  Studio__c  , "ICON"),  EXHIBITION_MONTH__c  = "AUG"), "SEP",  IF(AND(ISPICKVAL(  Studio__c  , "ICON"),  EXHIBITION_MONTH__c  = "OCT"), "DEC", IF(AND(ISPICKVAL(  Studio__c  , "ICON"),  EXHIBITION_MONTH__c  = "NOV"), "DEC",
EXHIBITION_MONTH__c ))))))))))))))))


Steve :-/Steve :-/
Yikes!!!  could you try re-posting that using the Code Clipboard?  That helps eliminate all of the smiley faces :smileywink: caused by right quotes and commas. Also, you should try re-posting this as a new question on the new Community Site https://sites.secure.force.com/answers/ideaList?c=09a30000000D9y3 Since this is really a separate issue to the one originally posted here. 
Message Edited by Stevemo on  01-22-2010 01:50 PM


EtaussigEtaussig
Thanks Stevemo.  I followed your advice and posted on the other site.


Tarun SuriTarun Suri
hi, i am trying this example on my org. and trying to set the validation rule on 2 picklists but its giving me error 
Error: Field State_and_Provision__c is a picklist field. Picklist fields are only supported in certain functions.
 

IF( AND( ISPICKVAL( Country__c , "Canada"), State_and_Provision__c = "Toronto"), "Calgary", IF( AND( ISPICKVAL( Country__c , "Montreal"), State_and_Provision__c = "Toronto"), "Calgary",State_and_Provision__c ))


pls help


Dharmin KansaraDharmin Kansara
@steve Can you help me?
 



I want to create a formula which is a combination of IF statement and maybe Ispickval!



 



The logic should be:



If owner Id = "112121212!2" than update a picklist value called "Apple" from the field Fruits



 



Thanks in advance!


nadia bica 2nadia bica 2
Hello,
I´m trying something similar but depending on my picklist value i wnat to show an image. Currently Saysing I'm missing ")" but I have counted and I can't find the error.

IF( 
ISPICKVAL( Kundenloyalitaet__c , "sehr hoch") ,

IMAGE("https://LINK1", "golden-laurel" [, 929, 820]),

IF
(ISPICKVAL( Kundenloyalitaet__c , "hoch") ,

IMAGE("https://LINK2", "Gold_medal" [, 320, 233]),


IF
(ISPICKVAL( Kundenloyalitaet__c , "mittel") ,

IMAGE("https://LINK3", "Silver_medal" [, 322, 251]),


IF
(ISPICKVAL( Kundenloyalitaet__c , "gering") ,

IMAGE("https://LINK4", "Bronze_Medal" [, 332, 248]),
)
 )
  ) 
   )


NAGASAI JANJANAMNAGASAI JANJANAM
Case(Kundenloyalitaet__c , "sehr hoch “, IMAGE("https://LINK1", "golden-laurel" [, 929, 820]),
"hoch", IMAGE("https://LINK2", "Gold_medal" [, 320, 233]),
"mittel", IMAGE("https://LINK3", "Silver_medal" [, 322, 251]),
"gering", IMAGE("https://LINK4", "Bronze_Medal" [, 332, 248]),”unknow”)

try using "case" function insted of "if"