+ Start a Discussion
Steven HoughtalenSteven Houghtalen 

How to develop a query that can use a multiselect field as a filter

I have a very complex report that required developing code for several hundred queries which have been completed.   Now the requirements have changed (naturally).   The queries were developed to use a Houseing Grant field as a filter where the field could contain one of two possible strings in a variable named 'Grant' ('Grant-A' or 'Grant-B').  (See below) 

The new requrirement is to make the Grant field a multiselect field such that all the queries could be made by 'Grant-A'  or 'Grant-A' or 'Grant-B'.   I thought I could do this by simply mass changing all the queries to use an Include clause but it appears that the Include clause can only use literals.   I looked at using dynamic SOQL but that would be a huge amount of work as the SOQL statements vary signficantly.  Does anyone have any suggestions on how to solve this problem? 

List<AggregateResult> results1 = [Select Count (Client__c) ClientCount
            From Transitional_Housing__c 
            Where Project_Entry_Date__c <=: EndDate
                AND (Project_Exit_Date__c >=: StartDate 
                  OR  Project_Exit_Date__c =: null)
              AND Housing_Grant__c =: Grant 
              AND Client__r.Age__c >=: 18
              ]; 
Best Answer chosen by Steven Houghtalen
Steven HoughtalenSteven Houghtalen
Thank you for your response Devi.   I looked at the 3 links you sent and none of them quite worked for what I needed.   

I did, however, come up with a solution using a regular pick list.    What I did was change the name of the grants (Grant) to:
THC Grant A
THC Grant B
THC Grant A and THC Grant B

I then added a mapping field (formula) to form a Grant_Alias such that 
THC Grant A became 'THC Grant A'
THC Grant B became 'THC Grant B'
THC Grant A and THC Grant B became 'THC%'   (note the "%" is a wild card character such that records with either Grant A and Grant B get selected)

I then modified the query to this 

List<AggregateResult> results = [Select Count (Client__c) ClientCount
            From Transitional_Housing__c 
            Where Project_Entry_Date__c <=: EndDate
                AND (Project_Exit_Date__c >=: StartDate 
                  OR  Project_Exit_Date__c =: null)
              AND Housing_Grant__c LIKE: Grant_Alias__c 
              AND Client__r.Age__c >=: 18
              ]; 

This allowed me to go into all of my classes that assumed a regular picklist would be adequate and perform a find / replace to change all the queries at once.  This was much easier than changing several 100s queries to a dynamic query.

This is "poor man's" way to make a regular picklist appear like a multi-select picklist such that you can use a variable in the query instead of a literal or using a dynamic query. 

All Answers

Devi ChandrikaDevi Chandrika (Salesforce Developers) 
Hi Steven,
For this scenario you can use Includes clause in SOQL.
Please refer below links which might help you further

https://developer.salesforce.com/forums/?id=906F00000008yY9IAI
https://developer.salesforce.com/forums?id=9062I000000Xn3wQAC
https://developer.salesforce.com/forums/?id=906F00000008wp9IAA

Hope this helps you
Thanks 
Steven HoughtalenSteven Houghtalen
Thank you for your response Devi.   I looked at the 3 links you sent and none of them quite worked for what I needed.   

I did, however, come up with a solution using a regular pick list.    What I did was change the name of the grants (Grant) to:
THC Grant A
THC Grant B
THC Grant A and THC Grant B

I then added a mapping field (formula) to form a Grant_Alias such that 
THC Grant A became 'THC Grant A'
THC Grant B became 'THC Grant B'
THC Grant A and THC Grant B became 'THC%'   (note the "%" is a wild card character such that records with either Grant A and Grant B get selected)

I then modified the query to this 

List<AggregateResult> results = [Select Count (Client__c) ClientCount
            From Transitional_Housing__c 
            Where Project_Entry_Date__c <=: EndDate
                AND (Project_Exit_Date__c >=: StartDate 
                  OR  Project_Exit_Date__c =: null)
              AND Housing_Grant__c LIKE: Grant_Alias__c 
              AND Client__r.Age__c >=: 18
              ]; 

This allowed me to go into all of my classes that assumed a regular picklist would be adequate and perform a find / replace to change all the queries at once.  This was much easier than changing several 100s queries to a dynamic query.

This is "poor man's" way to make a regular picklist appear like a multi-select picklist such that you can use a variable in the query instead of a literal or using a dynamic query. 
This was selected as the best answer