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
Rachel Linder 8Rachel Linder 8 

Case Statement With Two Variables/Picklist Fields

Below is my current formula field used in a field update WFR. 
IF(or( 
ISPICKVAL(Opportunity.Data_Partners_Entire_Campaign__c,"NCS"), 
ISPICKVAL(Direct_Match_Vendor__c,"NCS")), 
"NCS", 
IF(or( 
ISPICKVAL(Opportunity.Data_Partners_Entire_Campaign__c,"Comscore"), 
ISPICKVAL(Direct_Match_Vendor__c,"Comscore")), 
"Rentrak", 
IF(or( 
ISPICKVAL(Opportunity.Data_Partners_Entire_Campaign__c,"Experian"), 
ISPICKVAL(Opportunity.Data_Partners_EU_Entire_Campaign__c, "Experian"),
ISPICKVAL(Direct_Match_Vendor__c,"Experian"),
ISPICKVAL(Data_Partners_EU__c, "Experian")), 
"Experian", 
IF(or( 
ISPICKVAL(Opportunity.Data_Partners_Entire_Campaign__c,"Argus"), 
ISPICKVAL(Direct_Match_Vendor__c,"Argus")), 
"Argus", 
IF(or( 
ISPICKVAL(Opportunity.Data_Partners_EU_Entire_Campaign__c,"Epsilon"), 
ISPICKVAL(Data_Partners_EU__c, "Epsilon")), 
"Epsilon", 
IF(or( 
ISPICKVAL(Opportunity.Data_Partners_Entire_Campaign__c,"StatSocial"), 
ISPICKVAL(Opportunity.Data_Partners_EU_Entire_Campaign__c, "StatSocial"),
ISPICKVAL(Direct_Match_Vendor__c,"StatSocial"),
ISPICKVAL(Data_Partners_EU__c, "StatSocial")), 
"StatSocial", 
IF(or( 
ISPICKVAL(Opportunity.Data_Partners_Entire_Campaign__c,"Crossix"), 
ISPICKVAL(Direct_Match_Vendor__c,"Crossix")), 
"Crossix",  
IF(or( 
ISPICKVAL(Opportunity.Data_Partners_Entire_Campaign__c,"Symphony"), 
ISPICKVAL(Direct_Match_Vendor__c,"Symphony")), 
"Symphony", 
IF(or( 
ISPICKVAL(Opportunity.Data_Partners_Entire_Campaign__c,"Urban Science"), 
ISPICKVAL(Direct_Match_Vendor__c,"Urban Science")), 
"Urban Science", 
IF(or( 
ISPICKVAL(Opportunity.Data_Partners_Entire_Campaign__c,"84.51"), 
ISPICKVAL(Direct_Match_Vendor__c,"84.51")),
"84.51",
IF(or( 
ISPICKVAL(Opportunity.Data_Partners_Entire_Campaign__c,"Acxiom"), 
ISPICKVAL(Direct_Match_Vendor__c,"Acxiom")),
"Acxiom",
IF(or( 
ISPICKVAL(Opportunity.Data_Partners_Entire_Campaign__c,"Alliant"), 
ISPICKVAL(Direct_Match_Vendor__c,"Alliant")),
"Alliant",
IF(or( 
ISPICKVAL(Opportunity.Data_Partners_Entire_Campaign__c,"AMEX Advance"), 
ISPICKVAL(Direct_Match_Vendor__c,"AMEX Advance")),
"AMEX Advance",
IF(or( 
ISPICKVAL(Opportunity.Data_Partners_Entire_Campaign__c,"Ansa/RSi"), 
ISPICKVAL(Direct_Match_Vendor__c,"Ansa/RSi")),
"Ansa/RSi",
IF(or( 
ISPICKVAL(Opportunity.Data_Partners_Entire_Campaign__c,"Datalogix"), 
ISPICKVAL(Direct_Match_Vendor__c,"Datalogix")),
"Datalogix",
IF(or( 
ISPICKVAL(Opportunity.Data_Partners_Entire_Campaign__c,"Dun & Bradstreet"), 
ISPICKVAL(Direct_Match_Vendor__c,"Dun & Bradstreet")),
"Dun & Bradstreet",
IF(or( 
ISPICKVAL(Opportunity.Data_Partners_Entire_Campaign__c,"HG Data"), 
ISPICKVAL(Direct_Match_Vendor__c,"HG Data")),
"HG Data",
IF(or( 
ISPICKVAL(Opportunity.Data_Partners_Entire_Campaign__c,"Infogroup"), 
ISPICKVAL(Direct_Match_Vendor__c,"Infogroup")),
"Infogroup",
IF(or( 
ISPICKVAL(Opportunity.Data_Partners_Entire_Campaign__c,"Inscape"), 
ISPICKVAL(Direct_Match_Vendor__c,"Inscape")),
"Inscape",
IF(or( 
ISPICKVAL(Opportunity.Data_Partners_Entire_Campaign__c,"IRI"), 
ISPICKVAL(Direct_Match_Vendor__c,"IRI")),
"IRI",
IF(or( 
ISPICKVAL(Opportunity.Data_Partners_Entire_Campaign__c,"Kantar"), 
ISPICKVAL(Direct_Match_Vendor__c,"Kantar")),
"Kantar",
IF(or( 
ISPICKVAL(Opportunity.Data_Partners_Entire_Campaign__c,"Neustar"), 
ISPICKVAL(Direct_Match_Vendor__c,"Neustar")),
"Neustar",
IF(or( 
ISPICKVAL(Opportunity.Data_Partners_Entire_Campaign__c,"Oracle (BlueKai)"), 
ISPICKVAL(Direct_Match_Vendor__c,"Oracle (BlueKai)")),
"Oracle (BlueKai)",
IF(or( 
ISPICKVAL(Opportunity.Data_Partners_Entire_Campaign__c,"PeopleFinders DaaS"), 
ISPICKVAL(Direct_Match_Vendor__c,"PeopleFinders DaaS")),
"PeopleFinders DaaS",
IF(or( 
ISPICKVAL(Opportunity.Data_Partners_Entire_Campaign__c,"Transunion"), 
ISPICKVAL(Direct_Match_Vendor__c,"Transunion")),
"Transunion",
IF(or( 
ISPICKVAL(Opportunity.Data_Partners_Entire_Campaign__c,"Nielsen Catalina"), 
ISPICKVAL(Direct_Match_Vendor__c,"Nielsen Catalina")),
"NCS",
null 
))))))))))))))))))))))))))

I have started a case statement below. My question is  how would I condense the above into to one case statement as there are two different picklist values being used one from opportunity and one from opportunity products. 
CASE(Data Partners Entire Campaign,
“NCS”, “NCS”,
“Comscore”, “Comscore”,
“Experian”, Experian”
“Argus”, “Argus”,
“Epsilon”, “Epsilon”,
“StatSocial”, “StatSocial”,
“Crossix”, “Crossix”,
“Symphony”, “Symphony”,
“Urban Science”, Urban Science”,
“84.51”, “84.51”,
“Acxiom”, “Acxiom”,
“Alliant”, “Alliant”,
“AMEX Advance”, “AMEX Advance”,
“Ansa/RSi”, “Ansa/RSi”,
“Datalogix”, “Datalogix”,
“Dun & Bradstreet”, “Dun & Bradstreet”,
“HG Data”, “HG Data”,
“Infogroup”, “Infogroup”,
“Inscape”, “Inscape”,
“IRI”, “IRI”,
“Kantar”, “Kantar”,
“Neustar”, “Neustar”,
“Oracle (Blue Kai)”, “Oracle (Blue Kai)”,
“PeopleFinders DAAS”, “PeopleFindersDAAS”,
“Transunion”, “Transunion”,
“Nielsen Catalina”, “NCS”,
“Vizio”, “Inscape”,
“”)

 
Alain CabonAlain Cabon
Hi,

You can just concat the values by using TEXT() and "+"

CASE(text(Opportunity.Data_Partners_Entire_Campaign__c) +"|" + text(Direct_Match_Vendor__c) , 
"NCS|NCS", "NCS", 
"Comscore|Comscore", "Rentrak", 
...
"not found")

"|" is not mandatory, it is just an "impossible" character that will separate perfectly the two values (a simple comma can be sufficient).
Rachel Linder 8Rachel Linder 8
@Alain - i am getting a syntax error upon saving. It seems to be underling the pcklist values in red.User-added image
Alain CabonAlain Cabon
@Rachel  : your double quotes have been writen with MS Word (probably) and it is not the same double quotes of notepad.

I have tested the solution and that works.

Here is my test: 
CASE(text(test_picklist__c) +"|" + text(test_picklist2__c) , 
"value1|test21", "found1", 
"not found")
Rachel Linder 8Rachel Linder 8
ahhh...i didn't think about that. let me give it another try.
Rachel Linder 8Rachel Linder 8
Ok - I wrote the case statement below and was testing in sandbox but the field it is supposed to update is not being updated.
  
CASE(TEXT( Opportunity.Data_Partners_Entire_Campaign__c )+"|"+TEXT(Direct_Match_Vendor__c ), 
"NCS|NCS", "NCS", 
"Comscore|Comscore", "Comscore", 
"Experian|Experian", "Experian", 
"Argus|Argus", "Argus", 
"Epsilon|Epsilon", "Epsilon", 
"StatSocial|StatSocial", "StatSocial", 
"Crossix|Crossix", "Crossix", 
"Symphony|Symphony", "Symphony", 
"Urban Science|Urban Science", "Urban Science", 
"84.51|84.51", "84.51", 
"Acxiom|Axciom", "Acxiom", 
"Alliant|Alliant", "Alliant", 
"AMEX Advance|AMEX Advance", "AMEX Advance", 
"Ansa/RSi|Ansa/RSi", "Ansa/RSi", 
"Datalogix|Datalogix", "Datalogix", 
"Dun & Bradstreet|Dun & Bradstreet", "Dun & Bradstreet", 
"HG Data|HG Data", "HG Data", 
"Infogroup|Infogroup", "Infogroup", 
"Inscape|Inscape", "Inscape", 
"IRI|IRI", "IRI", 
"Kantar|Kantar", "Kantar", 
"Neustar|Neustar", "Neustar", 
"Oracle (Blue Kai)|Oracle (Blue Kai)", "Oracle (Blue Kai)", 
"PeopleFinders DAAS|PeopleFinders DAAS", "PeopleFindersDAAS", 
"Transunion|Transunion", "Transunion", 
"Nielsen Catalina|Nielsen Catalina", "NCS", 
"Vizio|Vizio", "Inscape", 
"")

Did I write this incorrectly?
Alain CabonAlain Cabon

The formula is associated with a custom field ( __c ) must be saved for a VFP (nothing during the editing) but  are you using the inline editing in Lightning for a form or a list?
 
Rachel Linder 8Rachel Linder 8
@Alain -  we are not officially on lightning for the sales team. But we need this formula to work in both lightning and classic. Both fields are picklist fields and we are trying to update a TEXT field upon saving a new record or editing and saving a new record.
Alain CabonAlain Cabon

"a field update WFR." = workflow rule, the update is perhaps not called (depending on the "Evaluation Criteria")

 
Rachel Linder 8Rachel Linder 8
We do have a workflow rule that is using a Field Update Action. The Field Update action was the very long If/Then statement as the formula (see the first post). I was trying to combine that into a case statement to use in the Field Update formula. Reason for this is because the If/Then statement has gotten so long that we have run out of characters to be able to add on to it.
Alain CabonAlain Cabon
Ok, we just see the tip of the iceberg. 

Recent idea very close to our case: still a bug?
Use TEXT() with a picklist field in Workflow Rule formula criteria
https://success.salesforce.com/ideaView?id=08730000000gAhIAAU

That was solved (5 years ago)

TEXT(picklist) support in business rules and buttons and links: As of Summer '09 we will have TEXT(picklist) support in Validation Rules, Formula Fields, and Workflow Field Updates. 
https://success.salesforce.com/ideaView?id=08730000000Bq9SAAS

There is perhaps still exceptions.