Apex Code Development You need to sign in to do that
Don't have an account?
Colin Loretz Querying Multiselect picklist fields
Hi all,
I found this documentation on querying multiselect fields.
When you do a query like:
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:
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
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.
Is that the best and/or only way to do this?
I found this documentation on querying multiselect fields.
When you do a query like:
Code:
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:
Code:
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
Code:
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.
Code:
MSP1__c includes (Cat1, Cat2, Cat3, etc).
Is that the best and/or only way to do this?
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];
System.assertEquals(check.FirstName,'Johnny');
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.