I have a requirement to keep a count of previous cases that were closed consecutively within 24hr periods on the latest closed case

pseudo logic would be something like this:

trigger when case is closed

has there been another case closed for this contact in the last 24hr?

if yes, is there another case closed in the previous 24hrs?

if yes, is there another case closed in the previous 24 hrs?

if no, stop and get total count and save in case that initiated the process

I can get the count of cases within a date range using aggregateresult but im having trouble with the consecutive approach without using multiple queries and forloops that will run-up the limits very quick

update: I'm able to partly achieve the results with the below approach. The new obstacle I have is handling multiple closed cases at once as they are all counted as one in my additions

    Set<id>contactIds = new Set<id>();
    List<Case>filteredCases = new List<Case>();
    List<Case>updateCasesList = new List<Case>();

    for(case c : updatedCases){

        if(c.status != oldCases.get(c.id).status && c.status == 'Closed'){


    //get contactIds from filtered cases
        for(case c : filteredCases){

    dateTime d = system.now().addDays(-1);
    //get all cases that have been closed in the last 24 hours that meet our test criteria  
    List<case>casesInFCRRange = [SELECT id, Latest_Case__c,ContactId, Number_of_Cases_in_Chain__c FROM Case WHERE Latest_Case__c = true AND isClosed = true AND closedDate >= :d AND id NOT IN:filteredCases AND contactId IN:contactIds];

    map<id,Decimal>numbersCasesInChain = new Map<id,Decimal>();
    //loop through the cases and mark as not first contact resolved 



            Decimal numbCases = 0;
            for(case c : casesInRange){
                    c.Latest_Case__c = FALSE;
                    numbCases = c.Number_of_Cases_in_Chain__c;



        for(case c : filteredCases){

            c.Number_of_Cases_in_Chain__c = numbersCasesInChain.get(c.contactId)+1;

    update updateCasesList; 

with the above, if multiple cases are closed for the same contact in bulk (unless they are in batches of 1) will only be counted as one because "casesInRange" SOQL is excluding any cases that started the process so they are not double counted. It feels like im missing something small but not looking at it from the right angle


If I correctly understand You, I would go for that aaproach:

    Case[] cases = new Case[]{};;
Set<Id> contactIds = new Set<Id>();
    for (Case c : (Case[])Trigger.new) {
        if (c.Status == 'Closed' && ((Case)Trigger.oldMap.get(c.Id)).Status != 'Closed') {

    Map<Id, Contact> contactsByIds = new Map<Id, Contact>([SELECT Id, (SELECT Id FROM Cases WHERE Status = 'Closed' AND ClosedDate > :System.now().addDays(-1)) FROM Contact WHERE Id IN :contactIds]);`

contactsByIds Is Your result map where as key You have Id of Contact for Who case just closed, and as value, A contact Object, wit list of related cases where closed date is in 24h.

Moreover this approach is bulkified and soql friendly.

Pls let me know if this is helpful, if not will try to re-arrange code.

| improve this answer | |
  • 1
    Yes, I started with this approach on the contactsbyIds you are including the cases that started the process and would potentially double count them but if you exclude them and there is multiple cases closed at the same time for the same contact then you would be missing cases. I updated my apex logic, have a look and let me know what you think Thanks! – JPG Apr 8 '18 at 13:38
  • Hi I am glad that this is useful for You, to solve that further issues I would create eg. Set<Id> casesIds, from Cases from List of cases (You can see in my snippet) and add to query WHERE clause (in Cases subselect): Id NOT IN :casesIds, then You will exclude only cases who just are closed. Sorry if my not perfect english is not 100% readeable :) If You will find it hard to understand I will update my code snippet, also feel free to ask further question. And 1 more, please remeber that upvoting helpful answers is always motivating :) – patryk Apr 8 '18 at 20:33
  • HI, did You manage to find working solution already? If no let me know what is excepted behavior in situation when there are eg. 2 cases closed in the same time, for 1 contact + eg 4 cases closed within 24 hours? One of those just closed cases should be counted together with them already closed, and then result would be 5 closed cases + 1 case with Counts of Closed = 5? Or both just closed cases should be stamped with the same value of already closed? Including or excluding other one just closed now. – patryk Apr 10 '18 at 17:05

I'm just contemplating if there might be a "no/low code" way to do this.

Let's say you use the DLRS tool to set up a Datetime rollup to Contact from Case - that pulls up the most recent ClosedDate. Let's call it "most recent case closed" (MRCC). Then also add an Integer field, "Case Closed Streak" (CCS). Default 1. You will also have an Integer field on Case for the result you want, default 0.

Now, have a Process Builder on Case. It proceeds when the Case becomes closed. It looks up to the Contact for the PRIORVALUE of MRCC (because DLRS triggers will have executed already before Process Builder) and checks if it's in the last 24h.

If YES: Set the counter on Case to Contact.CCS + 1, and then also set Contact.CCS to Contact.CCS. (So if this is the 2nd case in 24h, set the streak counter on the parent to 2 and the count on the Case to 1 previous case.)

If NO: Set Contact.CCS to 1 if it's not already 1. (So if a Case is closed and the last one was >24h ago, you reset the streak counter.) The counter on Case will remain at 0.

One flaw: If one transaction closes multiple Cases for the same Contact at once, the counter only increments by 1.

| improve this answer | |
  • Hi Charles, thanks for the above. the reason why I didn't go with your approach is because of the flaw you mentioned at the end. I need to be able to handle multiple cases closing at once as this tends to happen. Now I'm hitting that issue in my apex class see updated code above thanks! – JPG Apr 8 '18 at 13:01

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.