function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
Linda McIntoshLinda McIntosh 

Best way to create an email alert on Case trends with criteria

What would be the best way to send an email alert to the CEO if three cases with the same lot # (a text field) are created in a 45 day period?  This is a moving target so, for instance, there could be 5 cases created but if 3 cases aren't created in the 45 day period, it should not fire.

Any suggestions?
SlashApex (Luis Luciani)SlashApex (Luis Luciani)
Hi Linda,

I don't believe this is possible without having to write code. Are you open to that possibility?

You might want to look into Report Subscribe feature:
Linda McIntoshLinda McIntosh
I prefer to stay away from writing code if I can.  I have just set up a Report Subscription and added a formula column summing the lot #'s then creating a subscription that only notifies if the sum of the summary of the lot's are 3 or more.  This is pretty good.  The only thing is that I can't filter out the report to exclude those cases that are less than 3 (in the summary group).

If the subscribed report is not good enough, I will have to go to coding if this request is high enough to spend the time creating code.  Can you give me some idea as to what would be required if we went to writing some Apex code?
SlashApex (Luis Luciani)SlashApex (Luis Luciani)
I see, yes so if you cannot exclude the ones that are less than 3 that report can grow to be very big.

Going the code route will add complexities since it will be harder to manage, but there are ways to build it so that you can always turn the emails off. I can provide the code for you and some instructions. If you feel comfortable following them, do so, else you can probably find a developer to take care of this for you in a few hours.
  • Add a field to the Case object called "Fire Email" (Fire_Email__c api name)
  • Create a Workflow Field Update
    • Setup > Build > Create > Workflow & Approvals > Field Updates > New Field Update button
    • Name: Whatever you want
    • Object : Case
    • Field To Update: Fire Email (The new field you just created)
    • Re-evaluate Workflow Rules : Uncheck this
    • Specify New Value: Choose False
    • Save
  • Create Email Alert
    • Setup > Build > Create > Workflow & Approvals > Email Alerts > New
    • Description: Give it something desriptive of what the email we will be sending out will be.
    • Unique Name: Whatever you want
    • Object: Case
    • Email Template: Pick an email template you want to use (you might have to create one if you want something new for this scenario)
    • Recipient Type: For testing, select your user, once you have everything working come back and change it to the CEO.
    • Save
  • Add a Workflow Rule on the Case object that will run if the Fire Email field is set
    • Setup > Build > Create > Workflow & Approvals > Workflow Rules > New > Select Case > Next
    • Name: whatever you want
    • Evaluate on: created, and any time it’s edited to subsequently meet criteria
    • Criteria: Fire Email equals true
    • Save & Next
    • Click "Add Workflow Action" > Select Existing Action
    • Set Search to Email Alerts
    • Find the Email Alert you just created, select it, then click the right arrow
    • Set Search to Field Update
    • Find the Field Update you just created, select it, then click the right arrow
    • Save
    • Click Done
    • On the page you get redirected to (The Workflow page) click Activate
  • Now the fun part, create the trigger. You will have to do this on a sandbox and then deploy it to production.
  • Login to Sandbox and go to Setup > Customize > Cases > Triggers > New
  • Paste the following code, then click Save. I don't know the API name of the Lot field on the case so I just assumed it was Lot__c. If that is not the case, replace any mention of Lot__c in the code with the correct API name.
  • After deploying the trigger to production, everything should be working correctly.
trigger trg_Case_FireEmailOn3rdCasePerLot on Case (before insert)
    //get the lot numbers for all cases inserted
    Map<string, List<id>> lotToCasesMap = new Map<string, List<id>>();
    for(Case c :
                lotToCasesMap.put(c.Lot__c, new List<id>());
            List<id> temp = lotToCasesMap.get(c.Lot__c);
            lotToCasesMap.put(c.Lot__c, temp);            
    //Determine which lots have 2 or more cases in the last 45 days. We really care for 3, but the case being created would be the 3rd.
    List<AggregateResult> aggRes = [SELECT Lot__c lot, Count(ID) FROM Case 
                                    WHERE Lot__c IN :lotToCasesMap.keyset() AND CreatedDate = LAST_N_DAYS:45
                                    GROUP BY Lot__c
                                    HAVING COUNT(ID) >= 2];
    Set<string> lotsThatTrigger = new Set<string>();                               
    if(aggRes != null && aggRes.size() > 0)
        for(AggregateResult a : aggRes)
            string lotNum = string.valueOf(a.get('lot'));
        for(Case c :
            if(c.Lot__c != null && lotsThatTrigger.contains(c.Lot__c))
                c.Fire_Email__c = true;

The code is tested and it works.

If you ever want to disable this functionality, then you just need to disable the Workflow. 

Good Luck :)
Linda McIntoshLinda McIntosh
Luis....I did not expect this detailed solution but do appreciate it.  I'll create/test and let you know how I fair.
SlashApex (Luis Luciani)SlashApex (Luis Luciani)
Here's a video of the entire process, in case it comes in handy:
Linda McIntoshLinda McIntosh
Hi Luis,

I built everything as you suggested.  Only thing I changed was the code to use the correct API Name for the Lot Number.  Problem is that the trigger does not seem to be checking the box (I exposed for me so I can see what is happening) when three cases were created/edited with same lot number.  I do have other flows/validation rules going on but nothing that should impact.  Only updating record type (upon creation) and other field updates/email alerts not associated to the Lot Number field.  One validation rule that requires the Lot Number if a related Shipment object is created.

Also, what do I need to modify code to only update checkbox if the record type ID is "01240000000IbO4"?

Below is a snapshot of the new field, field update, email alert, W/F and trigger.  Really appreciate your help.



Field Update:

Field update

Email Alert:
email alert


Here is my code: