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
MikeGillMikeGill 

SOQL Includes on multi-selects

Hi All,

 

I want to select records where the any of the values of two multi-selects match.

 

I am using includes which I think is the only way, but unless both multi-selects match, it doesn't return any values.

 

Any ideas?

 

Thanks

 

 

 

 

List<Account> fromLead = [select Id, Name, BillingCity, Country_ISO_Code__c, Type, Accreditations__c from Account where 
 	 			
 	 				Type = 'Dealer' 
 	 				
 	 				AND Country_ISO_Code__c =: lead.Country_ISO__c
 	 				
 	 			
 	 				
 	 				AND Accreditations__c includes (: lead.All_Accreditation_Endorsements__c ) 
 	 				
 	 				AND Accreditations__c != null
 	 				
 	 				LIMIT 1000];
 	 				
 	 				accs = fromLead;

 

 

Best Answer chosen by Admin (Salesforce Developers) 
sfdcfoxsfdcfox

You are absolutely correct. You need to do something like the following:

 

 

account a = // some account;
string query = 'select id from account';
String[] opts = a.multiselect__c.split(';');
boolean hasCondition = false;
if(!opts.isempty()) {
  hasCondition = true;
  query += '  where multiselect__c in (\''+opts[0]+'\'';
  opts.remove(0);
}
while(!opts.isempty()) {
  query += ',\''+opts[0]+'\'';
  opts.remove(0);
}
if(hasCondition)
  query += ')';
Database.query(query);

 It's not nice to read, but it gets the job done. Your milage may vary, and you'll definitely want to kick the tires before you give it a serious lap around the track.

 

All Answers

sfdcfoxsfdcfox

This is expected behavior. When you choose multiple values from a picklist, it gets represented as "Value 1;Value 2". If you consider your original query:

 

 

SELECT Id, Name, BillingCity, Country_ISO_Code__c, Type, Accreditations__c
FROM Account
WHERE Type = 'Dealer'
    AND Country_ISO_Code__c =: lead.Country_ISO__c
    AND Accreditations__c includes (: lead.All_Accreditation_Endorsements__c ) 
    AND Accreditations__c != null
LIMIT 1000

When you literally substitute a multiselect value in place, it gets translated to:

 

 

 

SELECT Id, Name, BillingCity, Country_ISO_Code__c, Type, Accreditations__c
FROM Account
WHERE Type = 'Dealer'
    AND Country_ISO_Code__c =: lead.Country_ISO__c
    AND Accreditations__c includes ('Value 1;Value 2')
    AND Accreditations__c != null
LIMIT 1000

If you check the syntax for filtering multi-select picklists, you can use the following syntax:

 

=               Matches the string

!=              Does not match the string

includes  Contains the specified string

excludes  Does not contain the specified string

 

;                 Specifies "and" for two or more strings.

 

As you can see, then, the translation becomes an "AND" value for "Value 1" and "Value 2". To use "OR", the string would have to appear as follows:

 

 

    AND Accreditations__c includes ('Value 1','Value 2')

To get the bind variable to look just like that, you would have to be able to use a Set or List. However, at the time of this posting, that syntax appears to be supported, based on my tests. It appears you'll need to use Dynamic Apex and create a query string that resembles the statement above. You'll need to a) split the string at the delimiter, b) build a comma-delimited value list, and c) place that into your query string.

 
MikeGillMikeGill

Thanks for response sfdcFox again - i will try that.

 
um-dontaskmeum-dontaskme

Hello.  I'm trying to do the same thing, and actually tried to do it the same way you're suggesting.  No luck so far. 

 

When I debug my code, it shows the variable being used in the includes statement is formatted correctly  'Value1','Value 2' etc.  But it returns no results. 

 

When I make sure the string has only one value, formatted 'Value 1', it still doesn't work. 

 

But, if I make sure the string has only one value and no apostrophe Value 1, it will return results. 

 

I thought maybe it was automatically putting in the apostrophes in front and back of the string, so a tried formatting Value 1','Value 2 - that didn't work either.  It appears that anything inside a string variable will be treated as one string, ignoring the commas and apostrophes. :(
sfdcfoxsfdcfox

You are absolutely correct. You need to do something like the following:

 

 

account a = // some account;
string query = 'select id from account';
String[] opts = a.multiselect__c.split(';');
boolean hasCondition = false;
if(!opts.isempty()) {
  hasCondition = true;
  query += '  where multiselect__c in (\''+opts[0]+'\'';
  opts.remove(0);
}
while(!opts.isempty()) {
  query += ',\''+opts[0]+'\'';
  opts.remove(0);
}
if(hasCondition)
  query += ')';
Database.query(query);

 It's not nice to read, but it gets the job done. Your milage may vary, and you'll definitely want to kick the tires before you give it a serious lap around the track.

 

This was selected as the best answer
MikeGillMikeGill

Then once you have the string built out,  you then pass it into dynamic SOQ like below

 

 

	// Dynamcic SOQ
 	 			String s = 'Select Id, Name, BillingCity, Country_ISO_Code__c, Owner.Name, Type, Accreditations__c from Account where Type= \'dealer\' AND Country_ISO_Code__c = \'' 
 	 			+ lead.Country_ISO__c + '\' AND Accreditations__c includes ' + endor + ' ORDER BY BillingCity';
 	 			
 	 				
 	 				List<Account> a = database.query(s);
 	 				accs = a;

 

Good luck

 
um-dontaskmeum-dontaskme

Thanks to both of you!!  It does hurt to read at first, but once you catch up it's very cool!  That'll work.
gazazellogazazello

Thanks! That helped!
MossMoss

cool