You need to sign in to do that
Don't have an account?
Rachel Linder 8
Case Statement With Two Variables/Picklist Fields
Below is my current formula field used in a field update WFR.
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.
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”, “”)
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).
I have tested the solution and that works.
Here is my test:
Did I write this incorrectly?
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?
"a field update WFR." = workflow rule, the update is perhaps not called (depending on the "Evaluation Criteria")
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.