+ Start a Discussion
Colin LoretzColin Loretz 

Querying Multiselect picklist fields

Hi all,

I found this documentation on querying multiselect fields.

When you do a query like:
MSP1__c includes ('AAA;BBB', 'CCC') 

 the query filters on values in the MSP1__c field that contains either of these values:

    * AAA and BBB selected
    * CCC selected.

So the ';' represents AND.

If we break it up:

MSP1__c includes ('AAA', 'BBB', 'CCC')

 represents AAA or BBB or CCC.

 Is there a way to query using OR instead?

I'm passing my variable, lets say CategoryOptions to a class. The code becomes

MSP1__c includes (CategoryOptions)

 The problem with this is that it will only return values that match the selected Categories exactly. I need any records that has any single 1 of the options in the multiselect field.

My idea is to just parse the variable CategoryOptions and build a query, seperating all the values with commas.

MSP1__c includes (Cat1, Cat2, Cat3, etc).

 Is that the best and/or only way to do this?

Colin LoretzColin Loretz
I think the solution I'm going to move with is to just do a string replacement function to replace the AAA;BBB with AAA','BBB and pop it as a variable into the query.

If anyone else runs into this, its faster than parsing the whole thing into seperate pieces.

Hello Colin,


Have you had any traction on this? I have a similar need.


In my situation, I have 2 objects with 1picklist each. Both picklists have identical values.


In my situation, I need to select all records from object B that have values included in object A. I tried your solution listed here, but Apex did not return a result.


Here is a snippet of my code:


Account TestAccount = new Account(Name='TestAccountX', RecordTypeId='01280000000AsuAAAS', Type='Employer - Non-Core');
insert TestAccount;

Job_Lead__c jobA = new Job_Lead__C(Name='TestJob', Employer__c=TestAccount.Id, Skill_Sets__c='Administrative/Clerical;Call Center', Education__c='Some College', Accept_Felonies__c=TRUE,Accepted_Felonies__c='Drugs',Felonies_Age__c='2',Accept_Misdemeanors__c=TRUE, Accepted_Misdemeanors__c='Verbal DC',Misdemeanors_Age__c='2');
insert jobA;

Job_Lead__c jobSkillTest = [SELECT Id, Skill_Sets__c,Hourly_Wage__c FROM Job_Lead__c WHERE Id =: jobA.Id];

String x = String.valueOf(jobSkillTest.Skill_Sets__c).replace(';','\',\'');
//System.assertEquals(x,'Call Center\',\'Administrative/Clerical');

Contact c = new Contact(FirstName = 'Johnny',LastName = 'Test', Marital_Status__c='Married',RecordTypeId = '01280000000Astb',Social_Security__c = '199-99-9999',Race__c = 'Other', Gender__c = 'Male',Type__c='General' , Sub_Type__c='General',Postal_Code__c='45000', Skill_Sets__c = 'Call Center');
insert c;
Contact b = [SELECT Id, Skill_Sets__c FROM Contact WHERE Id =: c.Id];
System.assertEquals(b.Skill_Sets__c, 'Call Center');

//x = '\''+ x + '\'';
Contact check = [SELECT Id, FirstName, Skill_Sets__c FROM Contact WHERE Skill_Sets__c INCLUDES (:x) LIMIT 1];



I get an error: List has no rows for assignment to SObject.


A bulky work around I came up with is to create a nested for loop. In the outter loop, I loop through the values on objectB. In the inner loop, I loop through the values on objectA. The goal is to split up the values in each multiselect picklist, assign them to an array, and compare to 2 lists to find a match.


This option seems to work, but have found a better solution?


Is salesforce looking into this?


I think changing the behavior of 'INCLUDES' from an AND operation to an OR operation is the solution.


...a lot to handle... sorry for the long post.