In Salesforce understanding data types and their corresponding operators is key when trying to construct a dynamic query. It is important to understand which operators to use for which field. For instance the LIKE
operator is used as a mechanism to search for partial text strings. The caveat is that it only supports strings and it is case-insensitive. So when you need to do a search on a multi-select the LIKE
operator will not work and possibly blowup.
SELECT AccountId, FirstName, Skill_Grade__c From Contact Where LIKE 'A%'
The intent of this query is to match on any grade that is an A
Ex. A-,A or A+. In this case if the Skill_Grade__c
field is a multi-select it will break, you will get the invalid operator on multipicklist field
error. If it is a single-select or a text field it will work.
When there is a need to do a search on a multi-select it must match on the entire value not just a partial string. So you would need to use INCLUDES
or the IN
operator.
SELECT AccountId, FirstName, Skill_Level__c From Contact Where Includes ('A-','A','A+')
There is an obvious work around by using a formula field that returns a string based of the multi-select. That would still allow the user to have a good UI experience while allowing a SOQL query to do a partial match on the formula field.
SELECT AccountId, FirstName, Skill_Grade_Text__c From Contact Where LIKE 'A%'