+ Start a Discussion
StaciStaci 

Multipicklist in Where Clause

I have the following code.  Business_Unit_new__c in the User object is the multipicklist.  
I want to pull records from the License Numbers object where the Business Unit (picklist) from the License record is equal to the Business Unit of the current user (multipicklist).  The users may have more than 1 Business Unit assigned to them.

I've tried WHERE Business_Unit__c INCLUDES (:currentuser.Business_Unit_new__c) and it errors saying includes can only be used with a multipick list

If I try 
currentuser.Business_Unit_new__c INCLUDES (:Business_Unit__c) it errors saying it doesn't understand the relationship with currentuser.

How do I fix this?(this is not the complete code, jus tthe snipit that i need the query in)
public with sharing class LicenseList { 
private final License_Numbers__c ln; 
public user currentuser{get;set;} 
public id tobeEdited{get;set;}
public decimal aTotal{get;set;}
public decimal tTotal{get;set;}
public List<AccountWrapper> wrappers{get;set;}
private Integer nextIdent=0;

public LicenseList(ApexPages.StandardSetController controller) { 
currentuser=new User(); 
currentuser=[Select Id, Business_Unit_new__c from User where Id=:userinfo.getuserId()]; 
this.ln = (License_Numbers__c)controller.getRecord(); 

wrappers=new List<AccountWrapper>();
for(Integer idx=0; idx<1; idx++)
{
    wrappers.add(new AccountWrapper(nextIdent++));
}

} 

//------------------------------------------------------------------------------------

public List<License_Numbers__c> lntypes16 = new List<License_Numbers__c>();

public List <License_Numbers__c> getLicenseList(){ 
lntypes16 = [select id, Name, X2016_Cost__c, Business_Unit__c, X2016_Starting_Amount__c, X2016_Subtotal__c, BMS_Code__c, License_Type__c, Monthly_Unit_Price__c, Running_License_Total__c, Org__c FROM License_Numbers__c where Business_Unit__c =:currentuser.Business_Unit_new__c  AND License_Year__c = '2016' ORDER BY Order_Number__c, License_Type__c];
       

return lntypes16; 
}



 
salesforceMannsalesforceMann
Hello

I would usually deal with munltipicklist as follows. Note my addition to your code is first two lines and then using "IN" in the where clause.
 
Set<String> multiselectValues = new Set<String>();
multiSelectValues.addAll(currentuser.Business_Unit_new__c.split(';'));

select id, Name, X2016_Cost__c, Business_Unit__c, X2016_Starting_Amount__c,X2016_Subtotal__c, BMS_Code__c, License_Type__c, Monthly_Unit_Price__c,Running_License_Total__c, Org__c FROM License_Numbers__c where Business_Unit__c IN multiSelectValues  AND License_Year__c = '2016' ORDER BY Order_Number__c,License_Type__c]
Hope this helps.
Regards,
Mann.

 
StaciStaci
Hi @Mann_
I tried that and it complained about the ( after split so I changed it to 
Set<User> multiselectValues = new Set<User>();
multiSelectValues.addAll(currentuser.Business_Unit_new__c);

public List <License_Numbers__c> getLicenseList(){ 
lntypes16 = [select id, Name, X2016_Cost__c, Business_Unit__c, X2016_Starting_Amount__c, X2016_Subtotal__c, BMS_Code__c, License_Type__c, Monthly_Unit_Price__c, Running_License_Total__c, Org__c FROM License_Numbers__c where Business_Unit__c IN :multiSelectValues.split(';')  AND License_Year__c = '2016' ORDER BY Order_Number__c, License_Type__c];

and it now complains about the ) after  multiSelectValues.addAll(currentuser.Business_Unit_new__c);
salesforceMannsalesforceMann
Hi Stacy,

Could you run the following code and paste the debug log:
Set<String> multiselectValues = new Set<String>();
multiSelectValues.addAll(currentuser.Business_Unit_new__c.split(';'));

system.debug(currentuser.Business_Unit_new__c + '\n' + currentuser.Business_Unit_new__c.split(';') + '\n' + multiSelectValues;

select id, Name, X2016_Cost__c, Business_Unit__c, X2016_Starting_Amount__c,X2016_Subtotal__c, BMS_Code__c, License_Type__c, Monthly_Unit_Price__c,Running_License_Total__c, Org__c FROM License_Numbers__c where Business_Unit__c IN multiSelectValues  AND License_Year__c = '2016' ORDER BY Order_Number__c,License_Type__c]

Regards/

 
salesforceMannsalesforceMann
Hi Stacy,

While I was waiting for you reply, I noticed something in your code:
why are you using Set<User>, as in here:
Set<User> multiselectValues = new Set<User>();

While the code that I wrote uses Set<String>, as in here:
Set<String> multiselectValues = new Set<String>();

Change that and it should get you rolling! !!

Cheers!
Mann
salesforceMannsalesforceMann
Hi Stacy, did it work ?
StaciStaci
Hi @salesforceMann  sorry didn't have time to look at this yesterday.  I put what you had back in and it still says Compile Error: unexpected token: '(' at line 27 column 59 which is this line
multiSelectValues.addAll(currentuser.Business_Unit__c.split(';'));
StaciStaci
mistyped the field name, it should be multiSelectValues.addAll(currentuser.Business_Unit_new__c.split(';'));  but still same error