My Trigger sends an email if the same Account has 8 or more cases created within 7 days. I have four status fields, where if any of them are 'Active' the IF block executes otherwise the Else BLock.

Inside my ElseBlock I need to access two email addresses on the Parent_Project_if_applicable__r object. This object has a Lookup Relationship to the Account object.

Within the Else Block how do I access the fields on the Parent_Project_if_applicable__r: Resource_Coordinator_Email__c and Resource_Coordinator_Email__c.

From my Case object I access the Parent_Project in this manner: Parent_Project_if_applicable__r.Resource_Coordinator_Email__c.

trigger CaseHandlerCountAlert on Case (after insert, after update) {
    if (TrggrUtility.RunOnce) {
    //Case trigger that will send email alert when 8 cases are created within 7 days.
        Set <String> ListOfMessages = new Set <String>();
        Set <Id> AcctIds = new Set <Id>();
        List < AggregateResult > AggregateResultList = [SELECT AccountId, Account.Name name, COUNT(Id) co
                                                        FROM Case
                                                        WHERE CreatedDate = LAST_N_DAYS:7
                                                        GROUP BY Account.Name, AccountId
                                                        HAVING COUNT(Id) >= 8
        Map <Id, String> accountIdEmailmessageMap = new Map <Id, String> ();

        for (AggregateResult aggr: AggregateResultList){
            String messageToSend =+ 'Account name: ' + aggr.get('name') +
                                    ' has ' + aggr.get('co') +
                                    ' cases opened in the last 8 days.';
            Id accId = (Id) aggr.get('AccountId');
            accountIdEmailmessageMap.put(accId, messageToSend);


        List <Case> caseList = [SELECT Id, AccountId, Account.Name, Account.Eyefinity_EHR_Status__c,
                                        Account.Eyefinity_PM_Status__c, Account.OfficeMate_Status__c,
                                FROM Case
                                WHERE AccountId IN: AcctIds];

        List<Messaging.SingleEmailMessage> lstASingleEmailMessage = new List<Messaging.SingleEmailMessage>();
        List<Messaging.SingleEmailMessage> lstBSingleEmailMessage = new List<Messaging.SingleEmailMessage>();

        for (Integer i=0; i<AggregateResultList.size(); i++){
            for (Case cl: caseList) {

                if (cl.Account.Eyefinity_EHR_Status__c == 'Active' ||
                    cl.Account.Eyefinity_PM_Status__c == 'Active' ||
                    cl.Account.Project_Imp_Status__c == 'Active'  ||
                    cl.Account.OfficeMate_Status__c == 'Active') {

                    String messageBody = accountIdEmailmessageMap.get(cl.AccountId);

                    List<String> emailaddr = new List<String>();
                    emailaddr.add('[email protected]');  

                    Messaging.SingleEmailMessage mail = new Messaging.SingleEmailMessage();
                    mail.Subject = 'Multiple cases created alert message';

                } else if (cl.Account.Eyefinity_EHR_Status__c == 'Implementation' ||
                    cl.Account.Eyefinity_PM_Status__c == 'Implementation' ||
                    cl.Account.Project_Imp_Status__c == 'Implementation' ||
                    cl.Account.OfficeMate_Status__c == 'Implementation'){
                    String messageBody1 = accountIdEmailmessageMap.get(cl.AccountId);        

                    List<String> emailAdds = new List<String>();
                    Messaging.SingleEmailMessage amail = new Messaging.SingleEmailMessage();
                    amail.Subject = 'Multiple cases created alert message';
                else {
                    //Notify admin



        Messaging.SendEmailResult[] r = Messaging.sendEmail(lstASingleEmailMessage);   
        Messaging.SendEmailResult[] rb = Messaging.sendEmail(lstBSingleEmailMessage);
        TrggrUtility.RunOnce = false;


1 Answer 1


After reading your question a couple times I think I've spotted what you're running into but feel free to comment if I've interpreted your question incorrectly.

The immediate problem is this query which doesn't include the email fields you're trying to use later in the for loop.

List <Case> caseList = [SELECT Id, AccountId, Account.Name, Account.Eyefinity_EHR_Status__c,
                                   Account.Eyefinity_PM_Status__c, Account.OfficeMate_Status__c,
                                FROM Case
                                WHERE AccountId IN: AcctIds];


Add those fields to your query and it appears you should be good to go.

List <Case> caseList = [SELECT Id
                            , AccountId
                            , Account.Name
                            , Account.Eyefinity_EHR_Status__c
                            , Account.Eyefinity_PM_Status__c
                            , Account.OfficeMate_Status__c
                            , Account.Project_Imp_Status__c
                            , Parent_Project_if_applicable__r.Resource_Coordinator_Email__c
                            , Parent_Project_if_applicable__r.Client_Advisor_Email__c
                        FROM Case
                        WHERE AccountId IN: AcctIds];
  • thank you for responding. Your method makes perfect sense, and I wish they were operating in a way this would be possible. However, there are Cases where the Parent_Project will not be populated so that route to the field is broken at times. The Parent_Project does show up as a Related List on the Account though. So I'm thinking, SOQL to traverse Case -> Account -> Parent_Project
    – Tim
    Nov 19, 2019 at 0:29
  • If Parent Project is a related list on the Account, then it is likely a child of Account. So you're describing that you want to go up from Case to Account and then come back down to any arbitrary Parent Project (which could be unrelated to the Case at hand) and grab the email addresses from that record?
    – Mark Pond
    Nov 19, 2019 at 0:45
  • Yes, that's it.
    – Tim
    Nov 19, 2019 at 0:49

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .