In Salesforce, SOQL (Salesforce Object Query Language) is used to fetch the data from database. Similar to SQL but custom made for Salesforce’s multi-tenant environment. There are two essential operators available in SOQL that INCLUDES and EXCLUDES so basically, these work with multi-select picklist fields. I am going to explain my approach on how this operators work.
Salesforce Multi Select Picklist Fields
Multi-select picklist field — Unlike a single select list, this allows users to choose multiple options from the predefined set of choices. A picklist could allow a user to choose from the options North America, Europe, Asia etc.
The issue is that multi-select picklists can store more than one value, thus making it difficult to query such fields with standard = or != more complicated. This is where INCLUDES and EXCLUDES are really useful.
SOQL Includes and excludes operators are mainly used to filter multipicklist field values in salesforce. These operators are used for only multipicklist values. Multipicklist values are those whose have more than one picklist values .
For example a student must have many skills to get a job (salesforce, Java, C, C#, ORACLE etc).
- SELECT name, country__c, Student_skills__c FROM Student__C .
From above SOQL statement all the records from Student object has retrieved.
INCLUDES Operator
The INCLUDES operator is for when you want to see if a certain value or set of values exists in the multi-select picklist field. It specifies the records that include one or more of the target values in the designated picklist. Includes operator is used to filter and retrieve the data that contains any of the specified values in SOQL statement.
- SELECT name, country__c, Student_skills__c FROM Student__C WHERE student_skills__C INCLUDES (‘salesforce’).
From above SOQL INCLUDES operator statement we filtering the data whose student skills is equal to ‘salesforce’.
Excludes Operator
The EXCLUDES operator, opposite to INCLUDES Filters out the records which does not have a specific values or value in multi-select picklist.
- SELECT name, Country__C, Student_skills FROM Student__c WHERE student_skills EXCLUDES (‘salesforce’)
From above SOQL statement we fetching the data whose student will have no salesforce skills. condition must be written in single quotations and braces as shown above.
Multiple Values Example:
SELECT Id, Name, Regions__c
FROM Account
WHERE Regions__c EXCLUDES ('North America', 'Asia')The above query will return all the Account records where Region__c field have not contain North America. It will filter out the record whose field contains North America.
Summary INCLUDES and EXCLUDES
- INCLUDES Returns records where the multi-select picklist contains any of the specified values
- EXCLUDES – Returns records where the multi-select picklist did not include ANY of the selected values.
These operators are used for relationships between multi-select picklist fields in Salesforce. With the help of INCLUDES and EXCLUDES you can uniquely filter records that should be done based on multiple selections, hence making it simple for complex situations like multi-criteria with its pick-list.