Before we get started, Multi-Select Picklists are one of Salesforce Administrators favorite things to hate:
- The Evils of Multi-Select Picklists in Salesforce
- A Big Pitfall in Salesforce Field Type: Multi-Select Picklist
And my personal favorite, a meme made famous by Steve Mollis:
With all that said, Jeffrey Berger knew the perils of what he was about to ask, but he asked on Twitter anyways seeking solution to compare selections between two multi-select picklist fields.
Multi-Select picklist fields store their values as semi-colon (;) delimited text, which is not an easy thing to parse in Flow without a little help from Apex. And even then, once parsed to know which individual values were selected there’s the challenge of comparing what was selected in the first picklist vs. the second picklist.
We can easily parse and compare the two picklist values in Apex, but that doesn’t necessarily mean our entire solution must reside in Apex. I’m a big proponent of using as little Apex as responsibly necessary to solve a problem.
At the time Jeff asked his question I was not clear on his specific use case, whether he needed to query for matching records whose picklist field contained at least one value that another record had or if he had two known records from Process Builder or Flow that he needed to compare.
I provided him advice on either approach which I go into a bit of detail here.
Find Matching Records via SOQL
Suppose you’re given scenario that you have a candidate applying for a job and you have a multi-select picklist on both objects, you want for any given candidate’s selected skills that you find all matching jobs needing at least one of those skills.
This requires querying Salesforce and best suited in Apex because SOQL has special functions for filtering by multi-select picklists:
I can find all jobs that require either the skill ‘Apex’ or ‘Visualforce’ using the INCLUDES operator:
SELECT id, name FROM Job__c WHERE skills__c INCLUDES( 'Apex;Visualforce' )
Compare Two Records via Flow & Invocable Apex
If you’re not searching for records based on multi-select picklist values but rather comparing the selected values between two records, then I developed a simple Invocable Apex plugin for Flow that identifies the common and unique values between the two picklist field values.
The purpose of this design was to fill a niche need that would be too complex or unreasonable to perform declaratively in Flow but without requiring the entire solution become an Apex trigger. Instead, by providing an Invocable Apex method then any Flow could use it as needed and this small piece of Apex now fits within larger declarative solutions.
Salesforce MVP Andy Fawcett has a great write-up on the power of Invocable Apex and how it helps bridge the power of Process Builder, Flow, and Apex for both developers and administrators.
Dear Douglas,
My name is Jean Fernandez, I began to work with Salesforce platform two weeks ago and currently, I have to solve a challenge about it. In fact, I am working around salesforce’s formula field in Process Builder, to compare two fields values. For instance, the field Contact_A__c and Contact_B__c are picklist field types. The challenge consists to compare the several values of the picklist fields. In particular, I am attempting to create a comparison formula field to update the value of a text field “Result of Comparison” as an immediate action when the following condition is fulfilled.
IF(OR(ISPICKVAL([Comparison__c].Contact_A__c.Tipo__c, “Gold Partner”, CONTAINS([Comparison__c].Contact_B__c.Tipo__c, “Silver Partner”)),
“The Contact A represent an excelent Opportunity”)
However, it is appearing the following error message:
“The formula expression is invalid: Field Comparison is a picklist field. Picklist fields are only supported in certain functions”
After to review some websites such as http://resources.docs.salesforce.com/208/20/en-us/sfdc/pdf/salesforce_useful_formula_fields.pdf I understood better how to compare two text fields. But, I have not been able to identify what is required to overcome the error message. So, could you please suggest me how do you think it can be solved the issue of the formula field?
Best regards,
LikeLiked by 1 person
Hi Jean, and welcome to the Salesforce ohana!
I think you need to first convert the picklist field value to text for use with the CONTAINS function. For example, TEXT( yourPicklistField ).
Try this:
IF( OR( ISPICKVAL( [Comparison__c].Contact_A__c.Tipo__c, "Gold Partner" ), CONTAINS( TEXT( [Comparison__c].Contact_B__c.Tipo__c ), "Silver Partner" ) ), "The Contact A represent an excellent Opportunity", "The Contact A does not represent an excellent Opportunity" )LikeLike