function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
jldenningjldenning 

How to use multi-select picklist in WHERE clause of a dynamic query

I'm trying to construct a query for use in a trigger that uses a multi-select picklist field in the WHERE clause.  I started by hardcoding the choices in the where clause and that worked fine.  I then tried to dynamically get the choices and put them in a string called DeleteDepartmentsString.  The 2 strings DynamicQuery and HardCodedQuery (see below), appear to be identical according to the debug logs.  However, when I change Database.query(HardCodedQuery) to Database.query(DynamicQuery), I get the error System.QueryException: unexpected token: ')'

Can't figure it out what's different about the query strings.  What is the proper way to choose multiple selections from a multi-select picklist in the WHERE clause?  Thanks in advance.

Relevent code is below(debug log lines in bold).

System.debug(DeleteDepartmentsString);
15:14:20.276 (276302000)|USER_DEBUG|[101]|DEBUG|'Intellectual Property', 'Information Technology'

  String DynamicQuery = 'SELECT Id FROM Partner_Course__c WHERE Controlled_Document__c = \'a5fA00000004EJ2IAM\' AND Name__r.Department_MultiSelect__c INCLUDES ('+  DeleteDepartmentsString + ') AND Name__r.Department_MultiSelect__c EXCLUDES (\'Executive\') AND Course_Status__c = \'Enrolled\'';

  String HardCodedQuery = 'SELECT Id FROM Partner_Course__c WHERE Controlled_Document__c = \'a5fA00000004EJ2IAM\' AND Name__r.Department_MultiSelect__c INCLUDES (\'Intellectual Property\', \'Information Technology\')  AND Name__r.Department_MultiSelect__c EXCLUDES (\'Executive\') AND Course_Status__c = \'Enrolled\'';
 
  List<Partner_Course__c> CD = Database.query(HardCodedQuery);
15:14:20.276 (276700000)|SOQL_EXECUTE_BEGIN|[111]|Aggregations:0|SELECT Id FROM Partner_Course__c WHERE Controlled_Document__c = 'a5fA00000004EJ2IAM' AND Name__r.Department_MultiSelect__c INCLUDES ('Intellectual Property', 'Information Technology')  AND Name__r.Department_MultiSelect__c EXCLUDES ('Executive') AND Course_Status__c = 'Enrolled'
15:14:20.308 (308539000)|SOQL_EXECUTE_END|[111]|Rows:6
 
System.debug(DynamicQuery);
15:14:20.308 (308676000)|USER_DEBUG|[113]|DEBUG|SELECT Id FROM Partner_Course__c WHERE Controlled_Document__c = 'a5fA00000004EJ2IAM' AND Name__r.Department_MultiSelect__c INCLUDES ('Intellectual Property', 'Information Technology') AND Name__r.Department_MultiSelect__c EXCLUDES ('Executive') AND Course_Status__c = 'Enrolled'
  System.debug(HardCodedQuery);
15:14:20.308 (308694000)|USER_DEBUG|[114]|DEBUG|SELECT Id FROM Partner_Course__c WHERE Controlled_Document__c = 'a5fA00000004EJ2IAM' AND Name__r.Department_MultiSelect__c INCLUDES ('Intellectual Property', 'Information Technology')  AND Name__r.Department_MultiSelect__c EXCLUDES ('Executive') AND Course_Status__c = 'Enrolled'
Ashish_SFDCAshish_SFDC
Hi , 


Try this, 

SELECT Id, MultiPicklist__c from CustObj__c WHERE MultiPicklist__c includes :slist

Or you can use dynamic SOQL:

String squery = 'SELECT Id, MultiPicklist__c from CustObj__c ';
squery += ' AND MultiPicklist__c INCLUDES (' + slist + ')';


https://developer.salesforce.com/forums?id=906F00000008ydNIAQ


Using split: 

Multi-select picklists look like a semi-colon delimited string to your code. So, you can just use split(';') to get an array of all the picklist values. You can then use the IN operator.

SELECT Name from Object__c where PicklistField__c IN :multselect.split(';')

http://salesforce.stackexchange.com/questions/30088/query-single-pick-list-with-values-from-multi-select-pick-list

Or

It depends on how you write your query, since you want both to be present you can just use INCLUDES twice:

WHERE MultiPicklist__c INCLUDES ('Bank') AND MultiPicklist__c INCLUDES ('Agriculture')

http://salesforce.stackexchange.com/questions/11516/how-does-soql-consider-includes-for-multipicklist-and-or-or



Regards,
Ashish
Ashish_SFDCAshish_SFDC
Hi , 


Also see the article,  

Querying Multi-Select Picklists
Client applications use a specific syntax for querying multi-select picklists (in which multiple items can be selected).
Supported Operators
The following operators are supported for querying multi-select picklists:
Operator Description
= Equals the specified string.
!= Does not equal the specified string.
includes Includes (contains) the specified string.
excludes Excludes (does not contain) the specified string.
Semicolon Character
A semicolon is used as a special character to specify AND. For example, the following notation means ' AAA' and ' BBB':
'AAA;BBB'
Specifying AND is used for multi-select picklists when two or more items must be selected.
Examples
In the following example SOQL notation, the query filters on values in the MSP1__c field that are equal to AAA and BBB selected (exact match):
MSP1__c = 'AAA;BBB'
In the following example SOQL notation:
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.
A match will result on any field value that contains 'AAA' and 'BBB' or any field that contains 'CCC'. For example, the following will be matched:
matches with ' AAA;BBB':
             'AAA;BBB'
             'AAA;BBB;DDD'

matches with ' CCC':
             'CCC'
             'CCC;EEE'
             'AAA;CCC'

http://www.salesforce.com/us/developer/docs/officetoolkit/Content/sforce_api_calls_soql_querying_multiselect_picklists.htm


Regards,
Ashish
Mohd RizvanMohd Rizvan
for(Group g :[SELECT ID,Name FROM Group WHERE Id IN :groupIds]) {
    groupNames.add(g.Name);
}

String value = '(\'' + String.join(groupNames, '\',\'') + '\')';
String queryString = 'SELECT Id, Name, MultiSelectPicklist__c FROM Program__c where MultiSelectPicklist__c INCLUDES ' + value;
List<program__c> newlist = DataBase.query(queryString);
system.debug('List of records: '+newlist);