+ Start a Discussion
Chad MoutesChad Moutes 

Aggregate Function MAX Help

I have an Apex Trigger that is currenty calling upon a MAX function to display the most recent date a Task has been created.
 
for(AggregateResult aggregateResult:[SELECT max(createdDate)MaxCDate,WhatId FROM Task WHERE WhatID IN: acc_set AND Status ='Completed' AND Subject ='Completed Call' Group By WhatId]){
I want to change the date field that it is referencing from createdDate to ActivityDate like below:
 
for(AggregateResult aggregateResult:[SELECT max(ActivityDate)MaxCDate,WhatId FROM Task WHERE WhatID IN: acc_set AND Status ='Completed' AND Subject ='Completed Call' Group By WhatId]){

But when I do I recieve the following error: field ActivityDate does not support aggregate operator MAX at line 26 column 42.

Can anyone help me out here?
 
Best Answer chosen by Chad Moutes
pconpcon
Unfortunately, it appears that ActivityDate is not supported for these type of operations.  There is an idea on the ideaexchange [1] dealing with this that is "partially delivered" but probably doesn't cover this.  That means you're going to have to do it manually, or do something like
 
List<Account> accounts = [
    select (
        select ActivityDate
        from Tasks
        where Status = 'Completed' and
            Subject = 'Completed Call' and
        order by ActivityDate desc
        limit 1
    )
    from Account
    where Id in :acc_set
];

And then iterate over those accounts to get the highest activity date.

[1] https://success.salesforce.com/ideaView?id=08730000000BqfzAAC

All Answers

pconpcon
Unfortunately, it appears that ActivityDate is not supported for these type of operations.  There is an idea on the ideaexchange [1] dealing with this that is "partially delivered" but probably doesn't cover this.  That means you're going to have to do it manually, or do something like
 
List<Account> accounts = [
    select (
        select ActivityDate
        from Tasks
        where Status = 'Completed' and
            Subject = 'Completed Call' and
        order by ActivityDate desc
        limit 1
    )
    from Account
    where Id in :acc_set
];

And then iterate over those accounts to get the highest activity date.

[1] https://success.salesforce.com/ideaView?id=08730000000BqfzAAC
This was selected as the best answer
Chad MoutesChad Moutes
Thanks for the response, but what I ended up doing was making a new Date field and using that field. THANKS!