+ Start a Discussion
Serghei SleptovSerghei Sleptov 

Email alert if number of opened cases reaches certain value

How to created an email alert if total amount of opened cases reaches lets say 100 per day?

Best Answer chosen by Serghei Sleptov
Daniel B ProbertDaniel B Probert
trigger Trg_TooManyCasesAhhhh on Case (before insert) {
    date today =system.today();
    List<Case> numberofcases = [SELECT ID FROM CASE where LastModifiedDate>=:today];
        for(case cas:trigger.new){
            if(numberofcases.size() == 100 && cas.origin == 'Email'){
                cas.SendAlert__c = true;
            }
        }
}

note that i've add the && cas.origin == 'Email'

if you were to do the incremental email alerts - i.e. 100 & 200 as i mentioned previously then you could put your if like this(including comments on what's going on.

trigger Trg_TooManyCasesWarningHelper on Case (before insert) {
    // convert system.today which is a datetime into a date format
    date today =system.today();
    // create a list of all cases that were lastmodified equals or after today / could use createddate
    List<Case> numberofcases = [SELECT ID FROM CASE where LastModifiedDate>=:today];
        // loop through case
        for(case cas:trigger.new){
            // check for case origin
            if(cas.origin == 'Email'){
                // check to see if the number of cases equals 100
                if(numberofcases.size() == 100) {
                    cas.SendAlert__c = true;
                }
                // check to see if the number of cases equals 100
                if(numberofcases.size() == 200) {
                    cas.SendAlert__c = true;
                }
            }
        }
}


All Answers

Daniel B ProbertDaniel B Probert

you should be able to do this with a trigger on insert.

i'll put something together but want to verify you only want 1 alert right - i.e. when you hit 100 - not an alert each time there after?

also are you happy with it being a trigger and workflow combined.

i.e. a field would be updated on the cases that would trigger a workflow - this should then allow you to customer the email message without having to touch the code.

let me know and i'll send you an example code..
Serghei SleptovSerghei Sleptov
The idea is to fire an alert if number of cases inrease unxpectedly.
Lets say we normaly receive 20 cases per day via email-to-case 
If we started receiving signifincantly more I want  to be notifed .

Daniel B ProbertDaniel B Probert
ok so this should do the trick for you.

trigger Trg_TooManyCasesAhhhh on Case (before insert) {
    date today =system.today();
    List<Case> numberofcases = [SELECT ID FROM CASE where LastModifiedDate>=:today];
        for(case cas:trigger.new){
            if(numberofcases.size() = 100){
                cas.SendAlert__c = true;
                // i set my new field as a checkbox you could put it as a number and then reference it in your email alert.
            }
         if(numberofcases.size() = 200){
              cas.SendAlert__c = true;
          }
        }
}
i've put 2 examples in here.

first step is create a field on your cases object - that is either a checkbox like I have done or a number field - number field you can reference in your email so you can see the extent of the issue.

then create a standard email template - then a workflow with an email alert that if SendAlert = 100 or true in my case, send the email.

i hope that makes sense.

dan

Serghei SleptovSerghei Sleptov
Got an error:

*** Deployment Log ***
Result: FAILED
Date: June 9, 2014 1:40:14 PM EDT


# Deploy Results:
   File Name:    triggers/Trg_TooManyCasesAhhhh.trigger
   Full Name:  Trg_TooManyCasesAhhhh
   Action:  NO ACTION
   Result:  FAILED
   Problem: Expression cannot be assigned



Daniel B ProbertDaniel B Probert
Update lines 5 & 9 from = to ==
Serghei SleptovSerghei Sleptov
what is the purpose of  line 9 ?
Daniel B ProbertDaniel B Probert
i added it to show you how you can get incremental updates/alerts..
Serghei SleptovSerghei Sleptov
so it'll reset SendAlert__c to False if total number of cases are 101 and back to True if 200 ?
did I understand it correctly?
Daniel B ProbertDaniel B Probert
kind of reset isn't the word i'd use - basically every day the 100th and 200th record will have the flag set - all other records will appear as false.

when setting up the email alert work flow make sure you select for when created only.

cheers dan 

// if this has helped mark it as the correct answer to help others..
Serghei SleptovSerghei Sleptov
How can I fire the trigger only for a specific Origin?
my email-to-case sets Origin to be 'Email' but I think it will set it after the Trg_TooManyCasesAhhhh.trigger being executed.
Daniel B ProbertDaniel B Probert
trigger Trg_TooManyCasesAhhhh on Case (before insert) {
    date today =system.today();
    List<Case> numberofcases = [SELECT ID FROM CASE where LastModifiedDate>=:today];
        for(case cas:trigger.new){
            if(numberofcases.size() == 100 && cas.origin == 'Email'){
                cas.SendAlert__c = true;
            }
        }
}

note that i've add the && cas.origin == 'Email'

if you were to do the incremental email alerts - i.e. 100 & 200 as i mentioned previously then you could put your if like this(including comments on what's going on.

trigger Trg_TooManyCasesWarningHelper on Case (before insert) {
    // convert system.today which is a datetime into a date format
    date today =system.today();
    // create a list of all cases that were lastmodified equals or after today / could use createddate
    List<Case> numberofcases = [SELECT ID FROM CASE where LastModifiedDate>=:today];
        // loop through case
        for(case cas:trigger.new){
            // check for case origin
            if(cas.origin == 'Email'){
                // check to see if the number of cases equals 100
                if(numberofcases.size() == 100) {
                    cas.SendAlert__c = true;
                }
                // check to see if the number of cases equals 100
                if(numberofcases.size() == 200) {
                    cas.SendAlert__c = true;
                }
            }
        }
}


This was selected as the best answer
Serghei SleptovSerghei Sleptov
tested last night, however it didn't work
I created a new Origin and assigned it to case through email-to-case,
changed Line 11 in code above to be

if(numberofcases.size() == 1

to send email at 1st case created, however SendAlert__c wasn't set to true so workflow wasn't triggered.

Any idea?
Serghei SleptovSerghei Sleptov
NM, I found the problem on my side,

Daniel,
Thanks a lot for your help!