2

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?

2
  • The Name field cannot possibly be a multi-select picklist?
    – Adrian Larson
    Jun 27, 2017 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. Jun 27, 2017 at 19:49

2 Answers 2

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];
2
  • If I change to IN I get the error: IN operator must be used with an iterable expression. Thoughts? Jun 27, 2017 at 20:04
  • updated my answer, it should run Jun 27, 2017 at 20:11

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .