1

I have a method that searches for records based on a picklist value on the user record. We set the picklsit value and the query does a simple search based on the name.

Example:

user = [
        SELECT Department, BudgetToolRole__c, User_BU__c, User_Region__c, User_Sector__c 
        FROM User WHERE Id = :UserInfo.getUserId()
    ];

Practice__c p = [SELECT Id, Name FROM Practice__c WHERE Name = :user.User_BU__c]; 

Now, I have the requirement that the picklist field should really be a multi-select picklist and that there should be multiple practice records returned. So based on the above I want to get the values of the multi-select and then run the query on them.

Example:

String strPA = [SELECT Budget_Cross_Practice__c FROM User WHERE Id = :UserInfo.getUserId() LIMIT 1][0].Budget_Cross_Practice__c;

List<Practice__c> pas = [SELECT Id, Name FROM Practice__c WHERE Name = :strPA];

This complies in anonymous apex but no results are returned. The string returns a value in debug statement value1;values2;value3

I tried changing the = to IN but I get no luck.

How can I perform a query based on values in a multi-select?

  • The Name field cannot possibly be a multi-select picklist? – Adrian Larson Jun 27 '17 at 19:47
  • The custom field "Budget_Cross_practice__c" matches to the name of the Practice__c object. We have many business units called practices that get their own custom record to relate everywhere. users can be assigned a practice hence the singular picklist but then in some cases they can be assigned to multiple hence the need to have the multi-select. – Dan Wooding Jun 27 '17 at 19:49
1

You can use the LIKE operator:

String strPA = [SELECT Budget_Cross_Practice__c FROM User WHERE Id = :UserInfo.getUserId() LIMIT 1][0].Budget_Cross_Practice__c;
String likeStrPA = '%' + strPA + '%';
List<Practice__c> pas = [SELECT Id, Name FROM Practice__c WHERE Name LIKE :likeStrPA];

However, this will run into issues if any option is contained in another. A more robust solution is to split your multi-select field into a List<String>:

String strPA = [SELECT Budget_Cross_Practice__c FROM User WHERE Id = :UserInfo.getUserId() LIMIT 1][0].Budget_Cross_Practice__c;
List<String> strPASplit = strPA.split(';');
List<Practice__c> pas = [SELECT Id, Name FROM Practice__c WHERE Name IN :strPASplit];
1

I think you need to use IN clause to find the matching from multi-select picklist. IN clause always take Iterable element. So, either store strPA as List or Set and use that in SOQL.

String strPA = [SELECT Budget_Cross_Practice__c FROM User WHERE Id = :UserInfo.getUserId() LIMIT 1][0].Budget_Cross_Practice__c;

List<String> strList = strPA.split(';');

List<Practice__c> pas = [SELECT Id, Name FROM Practice__c WHERE Name IN:strList];
  • If I change to IN I get the error: IN operator must be used with an iterable expression. Thoughts? – Dan Wooding Jun 27 '17 at 20:04
  • updated my answer, it should run – Santanu Boral Jun 27 '17 at 20:11

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.