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
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];

            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'];  
                    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,,, usertype
                    FROM User 
                    WHERE id 
                    IN (SELECT userorgroupid 
                    FROM groupmember
                    WHERE = 'Customer Success Managers')];

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

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

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

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