-3

My Trigger will send an email anytime one account has 7 cases created within 8 days. Towards the bottom in my Else block, I am trying to access fields in my first SOQL statement. I have tried many ways and read many help pages. Help with the syntax for the line: message.PlainTextBody = 'Account name: ' + cl.get('name') + ' has ' + (Integer)cl.get('co') + ' cases opened in the last 8 days.';`

is greatly appreciated.

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

                         Set<Id> AcctIds = new Set<Id>();
                for(AggregateResult aggr: AggregateResultList){

                    Id accId = (Id)aggr.get('AccountId');
                        AcctIds.add(accId);
                }

                List<Case> caseList = [SELECT Id, AccountId, Account.Name,Parent_Project_if_applicable__r.Implementation_status__c,
                                                Parent_Project_if_applicable__r.PM_Implementation_Status__c, 
                                                Parent_Project_if_applicable__r.RCM_Implementation_Status__c    
                                       FROM Case
                                       WHERE AccountId IN :AcctIds];
            Messaging.SingleEmailMessage message = new Messaging.SingleEmailMessage();
            for(Case cl:caseList){ 

                if(cl.Parent_Project_if_applicable__r.Implementation_status__c == 'Live - Closed Project'){    
                        //Private method *** getAddresses() *** retrieves email address from Customer_Success_Managers Public Group
                             message.toAddresses  = getAddresses();   
                        }
                        else{  
                             message.toAddresses = new String[] { Parent_Project_if_applicable__r.Client_Advisor_Email__c  };

                        }
                    message.Subject = 'Subject Test Message';
                    message.PlainTextBody = 'Account name: ' + cl.get('name') + ' has ' + (Integer)cl.get('co') + ' cases opened in the last 8 days.';
                    Messaging.SingleEmailMessage[] messages =   new List<Messaging.SingleEmailMessage> {message};
                    Messaging.SendEmailResult[] results = Messaging.sendEmail(messages);
            }
private List<String> getAddresses(){
List<User> UserList =
        [SELECT id, name, email
        FROM User 
        WHERE id 
        IN (SELECT userorgroupid 
            FROM groupmember
            WHERE group.name = 'Customer Success Managers')];

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

for(User u: UserList){
    emailstring.add(u.email);
}   
return (emailString);
}    
  • What do you want from the aggregateResultList to put into the message specifically? – Kris Goncalves Oct 31 '19 at 20:01
  • 1
    Please clarify what you mean by "having issues". If you get an error message, please edit your post to include it verbatim. – Adrian Larson Oct 31 '19 at 20:06
  • Line: 33, Column: 1 System.SObjectException: Invalid field name for Case – ZeroCalm Oct 31 '19 at 20:27
  • 1
    Please stop creating new versions of the question and instead edit this one. – Adrian Larson Nov 1 '19 at 15:36
1

Your problem is with this line

message.PlainTextBody = 'Account name: ' + cl.get('name') + ' has ' + (Integer)cl.get('co') + ' cases opened in the last 8 days.';

Specifically:

cl is defined of type Case

cl.get('name') // no such field Name on Case.  

Consult your schema for valid OOTB field names. Case happens to be one of the Sobjects that does not have a Name field. Instead, there's CaseNumber

Furthermore, you never retrieve Name, even if you could, in your SOQL.

  • In order to access the 'Name' field on the Account object, what does that syntax look like? I've tried using the Relational name in the manner: message.toAddresses = cl.get('Parent_Project_if_applicable__r.Client_Advisor_Email__c'); Error message: Line: 29, Column: 34 Illegal assignment from Object to List<String> – ZeroCalm Oct 31 '19 at 21:33
  • Case object has a Lookup relationship to the custom object Parent_Project_if_applicable__c – ZeroCalm Oct 31 '19 at 21:35
  • you can't use the get method to traverse relationships; use cl.Parent_Project_If_Applicable__r.Client_Advisor_Email__c – cropredy Oct 31 '19 at 21:40
  • Thank you. When I use the line else{ message.toAddresses = cl.Parent_Project_If_Applicable__r.Client_Advisor_Email__c; } . I receive the error message Line: 29, Column: 34 Illegal assignment from String to List<String> – ZeroCalm Oct 31 '19 at 21:48
  • 1
    that's because message.toAddresses is a list and you are trying to assign a singleton to it; you need to assign a list to it. – cropredy Oct 31 '19 at 22:32
-2

I found that instead of attempting to access the variable from the SOQL search, I would just place them in a String that is accessible by the If/Then Statement. I created List<String> ListOfMessages = new List<String>(); and pushed each line in to List.

List<AggregateResult> AggregateResultList = [SELECT AccountId, Account.Name name, COUNT(Id) co
                                         FROM Case
                                         WHERE CreatedDate = LAST_N_DAYS:2
                                         GROUP BY AccountId, Account.Name
                                         HAVING COUNT(Id)  >= 2];
         String messageToSend;
         List<String> ListOfMessages = new List<String>();
         Set<Id> AcctIds = new Set<Id>();

    for(AggregateResult aggr: AggregateResultList){
        String messageToSend = 'Account name: ' + aggr.get('name') + ' has ' + (Integer)aggr.get('co') + ' cases opened in the last 8 days.';
        //ListOfMessages.add(messageToSend);

        Id accId = (Id)aggr.get('AccountId');
        AcctIds.add(accId);
    }

List<Case> caseList = [SELECT Id, AccountId, Account.Name,Parent_Project_if_applicable__r.Implementation_status__c,
                       Parent_Project_if_applicable__r.PM_Implementation_Status__c, 
                       Parent_Project_if_applicable__r.RCM_Implementation_Status__c,
                       Parent_Project_if_applicable__r.Client_Advisor_Email__c
                       FROM Case
                       WHERE AccountId IN :AcctIds];
  Messaging.SingleEmailMessage message = new Messaging.SingleEmailMessage();

for(Case cl:caseList){ 
     //I can now access those fields through a String Variable, much easier.
     System.debug('in the loop: ' + messageToSend);   

}

  • 1
    This code's not correct as shown. It does not collect a List<String>. Even if it did, it's not clear that a List<String> is the right data structure - a Map<Id, String> is probably required to correlate messages back to Account Ids. – David Reed Nov 1 '19 at 20:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.