-1

Pick list categories (Field Type: Picklist multislect) it has 4 drop down values:

   Category 1
   Category 2
   Category 3
   Category 4

Category 4 present in Idea table:

enter image description here

Query returned:

 system.debug('Query------'+Query);  [Please Refer code in End]

select id,title,Stage__c,Categories,Image__c,NumComments,Body,status,createddate from idea Where Categories in('Category 4')

But no result values returned.

Analysed:

But If only Category 4 present in Table Result will Appear. In the table it contains (Category 2,Category4) thats why result isnot coming.

Requirement:

Converted Picklist multiselect to picklist in UI. Requirement here is if any one value matches need to display that record. [User cant select Category 2,Category 4 in one shot,he can select any one of the values at a time]

In above example category 4 present in Idea table but no results.

//Instead of In keyword tried INCLUDES as well,but result is same

   String Query = 'select id,title,Stage__c,Categories,Image__c,NumComments,Body,status,createddate from idea';
   boolean whereClauseAdded=false; 
       if (String.isNotBlank(categoryFilter)) {
                                              if(whereClauseAdded)
                                               query = query + ' AND Categories in('+ '\''+categoryFilter+'\'' +')'; 
                                              else
                                                  query=query+ 'Categories in('+ '\''+categoryFilter+'\'' +')';
                                              whereClauseAdded=true;
                                          } 

                                          system.debug('Query------'+Query);
                                          List<SObject> sObjectList = Database.query(Query);
1

I would recommend reviewing the detailed documentation on how to query multi-select picklists in SOQL.

There are at least two problems here. One is that you are using the incorrect syntax. To query a multi-select picklist based on a specific value being selected, you must use an INCLUDES clause. The following example is taken from the documentation:

SELECT Id, MSP1__c from CustObj__c WHERE 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'.

The second problem is that your field data isn't valid for a multi-select picklist. In an MSP, selected picklist items are delimited by semicolons (;). Your screenshot clearly shows a value of Category 2,Category 4, delimited by a comma. SOQL will not understand this value as having the single picklist value Category 4 selected.

If you've actually performed a conversion so that this field is no longer a multi-select picklist, you'd have to do yet a different query using LIKE, which is really not the right solution for what it appears you're trying to do.

7
  • I have used includes but still same problem exist May 21 '20 at 12:11
  • Did you read the rest of the answer? There are at least two problems here.
    – David Reed
    May 21 '20 at 14:41
  • Thanks David... May 21 '20 at 16:47
  • we use like keyword it through below error: System.QueryException: invalid operator on multipicklist field May 22 '20 at 13:27
  • As above, you would perform a LIKE query If you've actually performed a conversion so that this field is no longer a multi-select picklist.
    – David Reed
    May 22 '20 at 13:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.