1

I have a few global picklists which I using for multi picklists.

I'm trying to retrieve a data via SOQL but something goes wrong - not all the rows are back or sometimes none of them. It depends if I have one or more values in the multi picklist.

I tried this approach:

asd - is a set of all the Values in the multi picklists inside my custom object.

for example :

List<String> asd = new List<String>();
asd.add('agg03');
asd.add('agg05');
asd.add('agg06');
asd.add('agg07');
asd.add('agg08');
asd.add('agg09');
asd.add('agg10');

assuming that I have 3 different records that have the next values in the multi picklist :

1 - 'agg01' and 'agg03'

2- 'agg06'

3 - 'efg01' and 'agg08'

'SELECT Id,Active__c,PickList_1__c,PickList_2__c,PickList_3__c FROM Custom_Object__c WHERE Active__c = true AND (PickList_1__c IN: asd OR PickList_2__c IN: asd OR PickList_3__c IN: asd)';

This will return only row 2 - because it has only one value. but will not return row 1 and 3 - although it includes in the Set.

I read about INCLUDE - But Its not working when I did the following :

 String asdJoin= String.join(this.asd, ',');

'SELECT Id,Active__c,PickList_1__c,PickList_2__c,PickList_3__c FROM Custom_Object__c WHERE Active__c = true AND (PickList_1__c INCLUDES (:asdJoin) OR PickList_2__c INCLUDES (:asdJoin) OR PickList_3__c INCLUDES (:asdJoin))';

I also tried to put quotes:

String StringVar= '\'' + String.join(asd, '\',\'') + '\'';
List<Object__c> inti = [SELECT Id,Agriculture__c FROM Interest__c WHERE Active__c = true AND  Agriculture__c INCLUDES (:StringVar)];

Which does not work

But this is Work:

List<Object__c> inti = [SELECT Id,Agriculture__c FROM Interest__c WHERE Active__c = true AND  Agriculture__c INCLUDES ('ag03','ag05','ag06','ag07','ag08','ag09','ag10','ex01','ex02','hu01')];

WHY?! :(

Any Idea how does it can work?

1 Answer 1

1

That happens because the soql does not understand (:StringVar) as reference to list, it assumes it as String. Possible solution is to make a soql string manually and get query locator:

List<String> asd = new List<String>();
asd.add('agg03');
asd.add('agg05');
asd.add('agg06');
asd.add('agg07');
asd.add('agg08');
asd.add('agg09');
asd.add('agg10');

String condition =  '(\'' + String.join(asd, '\',\'') + '\')';

String query = 'SELECT Id, PickList_1__c, PickList_2__c, PickList_3__c FROM Custom_Object__c WHERE PickList_1__c INCLUDES ' + condition + ' OR PickList_2__c INCLUDES ' + condition + ' OR PickList_3__c INCLUDES ' + condition;

system.debug(Database.query(query));

This is example, try to rewrite it for your case, feel free to ask if you encounter any problem.

1
  • Excellent! Thank you!
    – Salvation
    Jun 19, 2019 at 7:39

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.