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?