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
MarkLevyMarkLevy 

SOQL Query : LIKE / Substring in Multi-select picklist

Is it possible to query using SOQL for a substring on a multi-select picklist? LIKE seems to not work on multi-select picklists.

Example:

MultiSelectPicklist = 'myfavoritefruitisapple;myfavoritecolorisgreen;ihatered'

I'd want to query using WHERE and search for the substring 'favorite' such that it will pick up anything with 'myfavoritefruitisapple' or 'myfavoritecolorisgreen' but not pick up the 'ihatered' people.

Is something like this possible?

Thanks!
Best Answer chosen by MarkLevy
Harris M.Harris M.
Good point LBK,

Here is how I solved it at my end. I created a text field on the Object called TestColorText__c. I then write the following trigger, where TestColor__c is my multiselectPickList
  
trigger flatten_to_string on ColorObject__c (before insert, before update) {
    for (ColorObject__c a: Trigger.New) {
        a.TestColorText__c = a.TestColor__c;
    }
}

I was then able to use the LIKE operator in SOQL queries. Note that you wil have to update existing records to populate the TestColorText__c for this to work

Harris Mir
 

All Answers

LBKLBK
Hi Mark,

Multi Picklists are pretty limited in SOQL queries. Refer https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_querying_multiselect_picklists.htm for more details.

However, your requirement is absolutely possible in an APEX class.

You can fetch the data from your object using a Generic SOQL query and filter it further for the Multi Picklist field.
Harris M.Harris M.
How about the includes clause
  
SELECT * FROM myObjects WHERE MultiSelectPicklist__c includes ('favorite')



 
LBKLBK
Hi Harris,

INCLUDES works only if you provide the complete value of one of the entries in the optionset.

It doesn't take partial text, as Mark asked in his question.
Harris M.Harris M.
Good point LBK,

Here is how I solved it at my end. I created a text field on the Object called TestColorText__c. I then write the following trigger, where TestColor__c is my multiselectPickList
  
trigger flatten_to_string on ColorObject__c (before insert, before update) {
    for (ColorObject__c a: Trigger.New) {
        a.TestColorText__c = a.TestColor__c;
    }
}

I was then able to use the LIKE operator in SOQL queries. Note that you wil have to update existing records to populate the TestColorText__c for this to work

Harris Mir
 
This was selected as the best answer
LBKLBK
Great idea Harris.

Just to improve your idea a little more. If I were you, I would have tried a formula field instead of a text field.

This would have saved a trigger for me. Just a thought.
Harris M.Harris M.
I did think about it; the problem is that there is no simple way to convert a multiselectpicklist to text using formula. The only way I can think of doing it using a formula is to use hardcoded values with INCLUDES() so basically you go like this
  
IF(INCLUDES( TestColor__c , "myfavoritefruitisapple"), "myfavoritefruitisapple" + "~" , NULL) +
IF(INCLUDES( TestColor__c , "myfavoritecolorisgreen"), myfavoritecolorisgreenB" + "~" , NULL) +
IF(INCLUDES( TestColor__c , "ihatered"), "ihatered", NULL)

The problem with the above approach is that you will have to remember to update the formula if tomorrow you provide a new option. But it does save you a trigger
LBKLBK
That makes perfect sense Harris.

Mark, if Harris's trigger solves your problem, please mark it as best answer!

 
MarkLevyMarkLevy
Good workaround. It's too bad that something like this is necessary to get this functionality.