+ Start a Discussion
Timothy SmithTimothy Smith 

SOQL how to access children in Trigger

Account - Standard Object
Case - Child of Account
Milestone__Project__c - Child of Case

The After-Insert Trigger will send an email message anytime an account has 8 cases or more created in a 5 day period.  The email will be sent to one of two addresses (depending on Project.status).  How do I access the Milestone__Project__c.Status field?  I cannot get the syntax correct.
 
public class CaseHandlerCountAlert{
public static void CaseCounter(){

    List<AggregateResult> AggregateResultList = [SELECT AccountId, Account.Name name, COUNT(Id) co, (Id FROM Milestone1_Project__c mi)
                                FROM Case
                                WHERE CreatedDate = LAST_N_DAYS:5
                                GROUP BY AccountId, Account.Name
                                HAVING COUNT(Id)  >= 8
                                WHERE Id IN :Trigger.new];
                                

            for(AggregateResult aggr:AggregateResultList){ 

                    Messaging.SingleEmailMessage message = new Messaging.SingleEmailMessage();
                        if((aggr.get(mi.status) == "Transition"){    // If Status = Transition
                            // Set Outgoing Email to Implementation Coordinator "Implementation.Consultalt "
                            message.toAddresses = new String[] { Milestone1_Project__c.Client_Advisor_Email__c };
                        }
                        else if ((aggr.get(mi.status) == "Live"){  // If Status equals "Live" - Out Of Transition
                            // Private method retrieves email address from Customer Success Managers
                            message.toAddresses = new String[] { getAddresses() };
                        } 
                            // Set Email Template Id
                    //EmailTemplate templateId = [Select id from EmailTemplate where name = 'Template Name'];  
                    //mail.setTemplateID(templateId.Id);
                    message.setSubject = 'Subject Test Message';
                    message.setPlainTextBody = 'Account name: ' + aggr.get('name') + ' has ' + (Integer)aggr.get('co') + ' cases opened in the last 8 days.';
                    Messaging.SingleEmailMessage[] messages =   new List<Messaging.SingleEmailMessage> {message};
                    Messaging.SendEmailResult[] results = Messaging.sendEmail(messages);
                System.debug('Account Name: ' + aggr.get('name'));           
            }
            } 

            private List<String> getAddresses(){
            List<User> UserList =
                    [SELECT id, name, email, isactive, profile.name, userrole.name, usertype
                    FROM User 
                    WHERE id 
                    IN (SELECT userorgroupid 
                    FROM groupmember
                    WHERE group.name = 'Customer Success Managers')];

            Set<String> emailString = new Set<String>();

            for(User u: UserList){
                emailstring.add(u.email);
                // System.debug(u.email);
            }   
            //System.debug('The list ' + emailstring);
            return (emailString);
            }    
            }

 
EldonEldon
Hi Timothy,

Could you clarify which child project record you want to check the status? From what i understood there are more than one cases for the account and for each case also there are multiple child project records.

Also from which record you are accessing the emailId -> Milestone1_Project__c.Client_Advisor_Email__c

Regards
Timothy SmithTimothy Smith
NO, I was wrong.  MIlestone is a PARENT to Case, not a child.