Apex Code Development You need to sign in to do that
Don't have an account?
Steven 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
];
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
];
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
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
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.