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
Hey Doug,
This tool is absolutely incredible. Thanks for the effort and the share!
I was able to deploy it to a dev box but when attempting to deploy to a sandbox I’m receiving the error:
Deployment Started
Status: Queued
Status: Completed
Deployment Complete
Failures:
flows/Multi_Select_Picklist_Field_Compare_Flow-1.flow(Multi_Select_Picklist_Field_Compare_Flow-1):An unexpected error occurred. Please include this ErrorId if you contact support: 981790133-57428831 (1439001270)
It’s a NA63 instance which is on the Winter ’19 Patch 16.21 version.
Any suggestions?
LikeLiked by 1 person
Hi Chad, thanks for the kind feedback. I’m glad that you like this solution.
Unfortunately, I’m not sure about that deployment error. Perhaps if you open a case with Salesforce Support that can determine the underlying cause.
Thanks
LikeLiked by 1 person
flows/Multi_Select_Picklist_Field_Compare_Flow-1.flow(Multi_Select_Picklist_Field_Compare_Flow-1):AccountContactRelationRolesChoice (Choice Set) – The objectType is invalid. Provide the API name of an object.
LikeLiked by 1 person
It is giving this error on deploy via git hub
LikeLike
Doug .. I ran across this and found it super-helpful as I have a lot of Multi-Picklist comparing to do for a record matching algorithm. Thanks so much for sharing wtih the community!
For those that ran into the errors listed above, I also found that error in one Org whereas it installed fine in another Org. Upon further review, I determined that the source of the problem is the example Flow. The flow is the last item installed and is just an example to demonstrate usage of the Apex comparison logic. The example attempts to populate a picklist on a Screen drop-down using the Salesforce object of “Account Contact Relationship”. In the Org that worked, this Object exists and the Org that didn’t work, this Object doesn’t exist. I didn’t dig too deep to confirm why the Object didn’t exist in the 2nd Org but it may be because we’ve implemented Person Accounts or maybe it’s a recent change because this Org has just been upgraded to Summer 2020 a few days ago and I haven’t looked to see if that matters.
In either case, if you are getting the error, go to Object Manager and look for the Object “Account Contact Relationship” and if it isn’t there, then that’s what’s throwing the error. Since this is only a problem with the example flow and not the Apex logic itself, there are workarounds. What I did was go to GitHub and download the two class files and then just manually created those classes in Salesforce by copying and pasting the code from the 2 .cls files. Keep in mind you need to edit the API Version on the classes and set it to version 40, which is what would happen if you installed it automatically using the GitHub deploy tool (you can see this in the meta.xml files if you open them in Notepad.
I can confirm that after doing these steps, I now see the Apex Action show up in Flow just the same as if I deployed it via GitHub deploy. I haven’t tested it yet in runtime but feel pretty confident it will work just fine.
Doug, if you were so inclined, you could probably just do a minor update to your GitHub source and choose a different object/picklist value set for your example since you can’t be assured that Account Contact Relationship is going to be present in all Orgs.
Hope that helps!
LikeLike
Hi Doug,
We have a somewhat different requirement where we have multi select picklist info1 which is being updated from external system and to mention the scenario if first we have values as 1, 2 , 3 and thereafter if there is an update which removes 1 then we have to identify which value has been removed and peformed certain operation specific to that value . can you help how we can achieve this.
LikeLike