Apex Code Development You need to sign in to do that
Don't have an account?
Need to find most recent cases
Hi there. Struggling with the limits of SOQL .... if looking for a way to retrieve the most recent case for all accounts. In SQL such would be very simple ...
SELECT ID FROM Case U1
WHERE CreatedDate = (SELECT MAX(CreatedDate) FROM Case WHERE AccountID= U1.AccountID)
But SOQL does not allow me to due to the bind variable. Would anyone be able to support ?
SELECT ID FROM Case U1
WHERE CreatedDate = (SELECT MAX(CreatedDate) FROM Case WHERE AccountID= U1.AccountID)
But SOQL does not allow me to due to the bind variable. Would anyone be able to support ?
I was simply sharing the syntax you could use to get the details in a single query. It is imperative that you do apply the LIMIT claue if the dataset for the Account entity is large and once you do so, the result set that you get will contain Accounts with a Case record as well as Accounts without any Case records in which case you need to seletively iterate the return list and attempt to retrieve the Case related information for only those rows where the size of "accList[n].Cases" stack (accList[n].Cases.Size()) is greater than 0 to avoid the "List index out of bounds: 0" issue.
for (Account accRecWCase : accList) { if(accRecWCase.Cases != NULL && accRecWCase.Cases.Size() > 0) { //Do this and Do that} }If at the end of the day, you wish to show case the Account record with specific details from the latest case created for the Account then you can do it using one of the following ways:Approach 1 - Real time updates on the Record and display consolidated data using Reports/Dashboards
- Configuration - If in your org, Cases will not be Deleted and Cases will not be re-parented to different Accounts then you can use a Process Bulder Process to have this feature implemented.
- Custom Code (Real time) - Triggers on Cases to update field(s) on Accounts when a Case is created or deleted or undeleted or re-parented from the Account perspective and use the Inner Query to kick start a Batch Apex to update existing records.
- Once the data is populated, use Reports and Dashboards to show case the information.
Approach 2 - No Real time updates on the Record but Display using a custom VF interface- Custom Coded VF (No actual Record Updates) which leverages the Inner queries to query information and then display it on a custom VF Page as per a format worked out with the business.
I hope this helps you in choosing the best fit approach for your business case(s).Kind Regards,
Swarna.
All Answers
SELECT Id,CreatedDate,Name, (SELECT Id,CREATEDDate FROM Cases WHERE AccountId!=null ORDER BY CreatedDate DESC) FROM Account order by createdDate
SELECT Id,CreatedDate,Name,
(SELECT MAX(Id) FROM Cases WHERE AccountId!=null ORDER BY CreatedDate DESC)
FROM Account
But ...this is giving me a hard time ... MALFORMED_QUERY: only root queries support aggregate expressions
Give this a shot:
select max(createddate),max(id),accountid from Case where accountid in (Select id from Account) group by accountid
Please let me know if this helps !
Thanks,
Apoorv
Thank for the proposal. However this throws an error .. ( EXCEEDED_ID_LIMIT: Aggregate query does not support queryMore(), use LIMIT to restrict the results to a single batch ) on the amount of accounts.
Jos
Could you please tell me what is your scenario, where are you trying you use this query, so that I might be able to help you out!
Secondly we can use similar to determine how long it has been between the most recent case and current date. This will help us to potentially spot customer defection in an early stage.
Here is a link that might help you : https://developer.salesforce.com/docs/atlas.en-us.apexcode.meta/apexcode/langCon_apex_loops_for_SOQL.htm
If your end goal is to get a list of Accounts and for each Account on the list you need the details of the most recently created Case record associated to it then you can do so using an Inner Query.
I have given an example of the code snippet below:
List<Account> accList = new List<Account>(); accList = [Select Id, Name, (Select Id, CaseNumber, CreatedDate From Cases ORDER BY CreatedDate DESC LIMIT 1) from Account]; System.Debug('Value of the 1st AccountId on the list is ' + accList[0].Id + ' and value of the most recent Case ID for the Account is ' + accList[0].Cases[0].Id + ' with the difference between Today and the last created case being ' + ((accList[0].Cases[0].CreatedDate).date()).daysBetween(System.Today()));I hope this helps and please do let me know how this workied out for you.Kind Regards,
Swarna.
Thanks. I'm getting the EXCEPTION: System.LimitException: Too many query rows: 50001 while executing this poem. When I limit the account selection i'm getting EXCEPTION: System.ListException: List index out of bounds: 0
STACKTRACE: AnonymousBlock: line 6, column 1
LINE: 6 COLUMN: 1
However .. the I would prefer to have the output as a report or wors case in a temporary object. For account deflection this is pretty fundamental ...
Thanks anyway !!
I was simply sharing the syntax you could use to get the details in a single query. It is imperative that you do apply the LIMIT claue if the dataset for the Account entity is large and once you do so, the result set that you get will contain Accounts with a Case record as well as Accounts without any Case records in which case you need to seletively iterate the return list and attempt to retrieve the Case related information for only those rows where the size of "accList[n].Cases" stack (accList[n].Cases.Size()) is greater than 0 to avoid the "List index out of bounds: 0" issue.
for (Account accRecWCase : accList) { if(accRecWCase.Cases != NULL && accRecWCase.Cases.Size() > 0) { //Do this and Do that} }If at the end of the day, you wish to show case the Account record with specific details from the latest case created for the Account then you can do it using one of the following ways:Approach 1 - Real time updates on the Record and display consolidated data using Reports/Dashboards
- Configuration - If in your org, Cases will not be Deleted and Cases will not be re-parented to different Accounts then you can use a Process Bulder Process to have this feature implemented.
- Custom Code (Real time) - Triggers on Cases to update field(s) on Accounts when a Case is created or deleted or undeleted or re-parented from the Account perspective and use the Inner Query to kick start a Batch Apex to update existing records.
- Once the data is populated, use Reports and Dashboards to show case the information.
Approach 2 - No Real time updates on the Record but Display using a custom VF interface- Custom Coded VF (No actual Record Updates) which leverages the Inner queries to query information and then display it on a custom VF Page as per a format worked out with the business.
I hope this helps you in choosing the best fit approach for your business case(s).Kind Regards,
Swarna.
I've already draw the conclusing that this apparently simple 'question' does not match a simple and out of the box solution. .... I think using the process builder (or workflow) to record the case created date/time and overwrite the value on the account (custom field) would be the prefered solution as this would be easy to maintain / adjust and does not require any custom code ... Alternativly I could extract the data (cases with account id and created date) and account (id, name) and create the report from excel..
Anyway .. I will submit a idea for such feature ...
SELECT Id,CreatedDate,Name, (SELECT Id,CREATEDDate FROM Cases WHERE ContactId!=null ORDER BY CreatedDate DESC LIMIT 1) FROM Contact order by createdDate
This will work. It will give most recently created case for each account.