1

This trigger will send an email anytime an Account has 5 cases created within 8 days. I need to create two SOQL queries as I am accessing fields from Case and Project__r.

Currently receiving error message:

Invalid bind expression type of AggregateResult for Id field of SObject Case

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

List<Case> caseList =   [SELECT Id, AccountId, Account.Name, Project__r.Implementation_status__c,
                         Project__r.Client_Advisor_Email__c
                         FROM Case WHERE Id IN :AggregateResultList];


            for(Case cl:caseList){ 
                Messaging.SingleEmailMessage message = new Messaging.SingleEmailMessage();

                        if(cl != Null && Project__r.Implementation_status__c == 'LIVE - TRANSITION'){    
                             //Set Outgoing Email to Implementation Coordinator
                             message.toAddresses = new String[] { Project__r.Client_Advisor_Email__c }; 
                        }
                        else if (cl != Null && Project__r.Implementation_status__c == 'Live - Closed Project'){  
                             //Private method *** getAddresses() *** retrieves email address from Customer_Success_Managers Public Group

                             message.toAddresses = new String[] { getAddresses() };
                        } 

                    message.Subject = 'Subject Test Message';
                    message.PlainTextBody = '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);
            }

1 Answer 1

1
List<AggregateResult> AggregateResultList ...

... WHERE Id IN :AggregateResultList];

This is the problem, as the error is indicating. You're querying against an Id field, but binding a collection of AggregateResult objects. Those are not compatible types.

You'll need to iterate over the list of AggregateResult objects and accumulate their AccountId values into a Set<Id>. You can then bind the Set<Id> into your second query.

7
  • Thank you for your response. I understand what you are saying and it makes sense. I am just having a bit of trouble with the syntax.
    – Timothy
    Commented Oct 30, 2019 at 21:55
  • @ZeroCalm Help me understand how to help. Where are you stuck?
    – David Reed
    Commented Oct 30, 2019 at 22:02
  • Thank you. I understand what you are saying and it makes sense. I am just having a bit of trouble with the syntax. Set<Id> AcctIds = new Set<Id>(); for(AggregateResult aggr: AggregateResultList){ AcctIds.add(aggr.AccountId); } List<Case> caseList = [SELECT Id, AccoundId, Account.Name, Project__r.Implementation_status__c, Project__r.Client_Advisor_Email__c FROM Case WHERE Id IN :AcctIds]; Receiving Error: Variable does not exist: AccountId
    – Timothy
    Commented Oct 30, 2019 at 22:06
  • 1
    AggregateResults don't have much in the way of static fields. You need to access their contents dynamically via get(): aggr.get('AccountId')
    – David Reed
    Commented Oct 30, 2019 at 22:08
  • 1
    @ZeroCalm The return value from get() must be cast to the type Id: (Id)aggr.get('AccountId').
    – David Reed
    Commented Oct 31, 2019 at 13:55

You must log in to answer this question.

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