1

I am trying to query the most recent ActivityDate on an Account. Since aggregate function does not work on the Standard ActivityDate field on Tasks, I created a custom field ActivityDate__c to return the most recent activity entered. However, my code below keeps getting the following error "only root queries support aggregate expressions". Can anyone assist?

public static void generateReport(String startDate, String endDate, String poolState){
    list<Task> taskListForSort = new list<Task>();
    list<wrapSortData> wrpSortList = new list<wrapSortData>();
    OwnerWrapWithoutCountyList = new list<OwnerWrapperWithoutCounty>();        
    //map<String,list<task>> ownerAccountMap = new map<String,list<task>>();
    set<id>taskIds= new set<id>() ;      
    Date sDate = date.valueOf(startDate);
    Date eDate = date.valueOf(endDate);

    list<Account> accList = new list<Account>();
        accList = [select Id,County__c,Name,Client_State__c,AssignedBanker__c,Owner.Name,
                   (select Id,WhatId, ActivityDate,MAX(ActivityDate__C),Description,Subject,Account.Name,Account.AssignedBanker__c from Tasks 
                    where ActivityDate != null AND ActivityDate >=:sDate AND ActivityDate <=:eDate ORDER BY ActivityDate DESC Limit 1) 
                   from Account where RecordType.DeveloperName = 'xxxxx' and (BillingState= 'CA' OR ShippingState='CA') ORDER BY AssignedBanker__c ASC];


    for(Account acc : accList){        
        if(acc.Tasks.size() > 0){
            for(Task t : acc.Tasks){               
                taskIds.add(t.id);
            }
        }
    }
    list<task> taskListToAdd; 
    map<string,OwnerWrapperWithoutCounty>ownerAccountMap= new  map<string,OwnerWrapperWithoutCounty>();
    for(task t:[select Id,WhatId,ActivityDate,ActivityDate__c,Description,Subject,Account.Name,Account.AssignedBanker__c,Account.owner.Name from task where ID IN:  taskIds  ORDER BY ActivityDate asc ]){
        OwnerWrapperWithoutCounty wrap ;  
        if(!ownerAccountMap.containsKey(t.Account.owner.Name)){
            wrap = new OwnerWrapperWithoutCounty(); 
            wrap.owner = t.Account.owner.Name;
            wrap.taskList = new list<Task>(); 
            wrap.taskList.add(t);
            ownerAccountMap.put(t.Account.owner.Name,wrap);

        }else{
            wrap=  ownerAccountMap.get(t.Account.owner.Name);
            wrap.taskList.add(t);
            ownerAccountMap.put(t.Account.owner.Name,wrap);
        }
    }
    OwnerWrapWithoutCountyList.addAll(ownerAccountMap.values());
0

You were close! You should remove that custom field and use an order by clause to select the first one.

accList = [select Id,County__c,Name,Client_State__c,AssignedBanker__c,Owner.Name,
                   (select Id,WhatId,ActivityDate,Description,Subject,Account.Name,Account.AssignedBanker__c from Tasks 
                    where ActivityDate != null AND ActivityDate >=:sDate AND ActivityDate <=:eDate ORDER BY ActivityDate DESC Limit 1) 
                   from Account where RecordType.DeveloperName = 'xxxxx' and (BillingState= 'CA' OR ShippingState='CA') ORDER BY AssignedBanker__c ASC];
| improve this answer | |
  • Mitch, that's my original code, but I needed to add the MAX() function because the end user generates this by selecting a start date and an end date and if there are two Activities under an Account, one that falls under that date range and one that is more recent and is after the end date selected by the user, the Activity that falls in that date range shouldn't be displayed since there is one that is more recent. – Julian Jan 30 at 15:59

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.