3

OBJECTIVE

I am simply trying to return the most recent activity for a given list of accounts: List<Id>. I just want 1 row for each Account ID that gets passed. For each row I need the ActivityDate and a couple custom fields.

MY RECENT ATTEMPT

SELECT 
    AccountId, MAX(ActivityDate), Subject_Prefix__c 
FROM 
    Task  //or Event
WHERE 
    AccountId IN :account_set
ORDER BY 
    AccountId DESC, ActivityDate DESC
GROUP BY
    AccountId

ISSUE

I've tried many combinations of solutions and each one has a different issue, such as:

  • MALFORMED_QUERY: unexpected token: GROUP
  • ActivityDate does not support aggregate operator MAX
  • Ordered field must be grouped or aggregated: ActivityDate

EDIT: If this is not possible...

If it is not possible to have the data I need prepared in a single row like I hoped then my follow up question would be how would I best iterate through the returned List (possibly 20+ rows per AccountId) to get the 1 row per Set<Id> account_set of the most recent activity?

2

Here's a something I came up with using some code I had that should provide you with a basis for a reasonably efficient solution provided you have ActivityHistory enabled on your Accounts.

List<Account> account_recent_task = new List([
    SELECT Id, 
    (
        SELECT Id, Subject_Prefix__c, ActivityDate 
        FROM ActivityHistories 
        WHERE IsTask = TRUE 
        ORDER BY ActivityDate DESC 
        LIMIT 1
    ) 
    FROM Account 
    WHERE Id in :accountId_set
]);

This SOQL can be a dynamic string for optional pieces like filtering by Task/Event (IsTask=boolean). Another example, the only thing needed for this scenario is the most recent Task/Event, so you only need to return 1 row (LIMIT 1) from the aggregate relationship query which helps with governing limits. Afterwards you can simple iterate through the list

Map<Id,Account> account_map = new Map<Id,Account>();

if(!account_recentTask.isEmpty()){
    for(Account a : account_recentTask){
        if( !a.ActivityHistories.isEmpty() ){
            account_map.get(a.Id).Last_Task_Id__c = a.ActivityHistories[0].Id;
            account_map.get(a.Id).Last_Task_Date__c = a.ActivityHistories[0].ActivityDate;
            account_map.get(a.Id).Last_Task_Subject__c = a.ActivityHistories[0].Subject_Prefix__c;
        }
    }
}

UPDATE account_map.values();

You now have several options. One would be to compare the size of the keyset for Ah2AcctId with it's values. If they're the same, then you only have the most recent activities for the Account Ids that returned them.

If not, then you'll need to sort on ActivityDate where the map Account Id values are the same. Alternatively, I could also have created a map of type <list<ActivityHistory>,Id> and put acctHist into it along with AId, resetting acctHist whenever AIdchanged as it was put it into the map. There was also the option to sort on the ActivityDate when pulling the data from the original query before putting it into the map.

Any of these would seem to me to serve to obtain a workable solution. Since I didn't need to solve this problem for myself, I've simply provided the above as a basis for what might help others solve it for themselves.

  • I'm working with this right now and have some minor tweaks but essentially to SOQL the Account object instead of the Task/Event object separately is the answer. To get the separated results I need I would just add AND IsTask = TRUE/FALSE Thanks so much! I just deployed my other solution :P But this makes a big enough difference in performance I can't not go back and implement it now. Thanks again – Xtremefaith Mar 17 '15 at 22:21
  • You're welcome. When I saw what you were trying to do, I felt compelled to share the path I believed would get you where you wanted to go. Glad to here it's going to get you there. – crmprogdev Mar 17 '15 at 23:15
0

I am pretty certain that you cannot achieve this with a straight SOQL query. I assume that you have tried just looping through all of the tasks for the designated accounts and picking out the latest Activity for each Account. Here is a slight variation of that same logic, except it will hopefully be a little more efficient by reducing the number of loops that are required.

Let me know if this works for you, but this is a really costly operation.

Alternative: You might want to consider investing some time on creating a trigger on the Task object that will save the "Latest Task ID" at the Account level. You can use the code provided to back fill the new field on existing accounts (One time operation), and moving forward you could just rely on the new field to very easily query for the latest task information.

//Assuming that you have a set of account ids that you need the lastest activity for
Set<Id> accountIdsForRecentActivitySearch = new Set<Id> {
    '001600000198xVN',
    '001600000198yVS'
        //Etc ...
};

Integer numOfAccounts = accountIdsForRecentActivitySearch.size();

/****** Optional!!! - START -  Might be overkil, but will reduce your CPU time if 
you are processing a lot of accounts */
//Determine how many accounts have an activity
List<AggregateResult> dummyAggregate = [SELECT AccountID, COUNT(Id) FROM Task
                                       WHERE ActivityDate != null AND
                                        AccountID IN :accountIdsForRecentActivitySearch
                                       GROUP BY AccountID];

numOfAccounts = dummyAggregate.size();
/****** Optional!!! - END */

//We will store all the latest activities on a Map < Account ID, Latest Activity >
Map<id, Task> resultLatestActivity = new Map<id,Task>();

if(numOfAccounts > 0)
{    
    for(Task t : [SELECT id, Subject, AccountId, ActivityDate 
                  FROM Task
                  WHERE ActivityDate != null AND
                  AccountId IN :accountIdsForRecentActivitySearch
                  ORDER BY ActivityDate DESC])
    {
        //Start populating the map with the first entry per account you find. 
        //It will be the most recent activity for that account.
        if(!resultLatestActivity.containsKey(t.AccountId))
        {
            resultLatestActivity.put(t.AccountId, t);
        }

        //If we already have collected the latest activities for all accounts,
        //then stop looping, we are done.
        if(resultLatestActivity.size() == numOfAccounts)
        {
            break;
        }
    }
}

//This is your list of latest activity for the requested accounts. Just one Activity per account.
system.debug(resultLatestActivity.values());

Good luck!

Luis Luciani

  • Based on the bombardment of responses I don't think it is possible either :( As you mentioned this is exactly what I'm trying to do. I have a Task/Event Trigger that passes the AccountId Set to my utility method which is attempting to set the last Task/Event Id, Date, and Subject. My approach is different then yours so I may post it as a viable answer for the record to perhaps get some feed back to make, as you said, a very taxing process as optimal as possible. Especially since we bulk sync with quickbooks. – Xtremefaith Mar 17 '15 at 17:50
0

So far I have not found this to be possible in SOQL alone so for the time being I have a list of all Activities for all included accounts (from the Trigger) ordered by ASC so that I can iterate through them and the most recent one should be the last of the loop

SELECT AccountId, Subject, ActivityDate, Subject_Prefix__c 
FROM Task 
WHERE AccountId IN :accountId_set
AND OwnerId NOT IN :supportIds
ORDER BY ActivityDate ASC

Then I begin to build a Map that I can use to track recent activity per AccountId for a single UPDATE call.

Map<Id,Account> account_map = new Map<Id,Account>();

First I wipe all the fields to be updated because if something was deleted then I need to clear the field and this is easier then adding complex if statements throughout my iteration

for( String id : accountId_set ) {
    Account account = new Account(
        Id = id,
        Last_Touch_Task_Id__c = '',
        Last_Touch_Task_Date__c = null,
        Last_Touch_Task_Subject__c = '',
        Last_F2F_Task_Id__c = '',
        Last_F2F_Task_Date__c = null,
        Last_F2F_Task_Subject__c = '',
        Last_Touch_Event_Id__c = '',
        Last_Touch_Event_Date__c = null,
        Last_Touch_Event_Subject__c = '',
        Last_F2F_Event_Id__c = '',
        Last_F2F_Event_Date__c = null,
        Last_F2F_Event_Subject__c = ''
    );
    account_map.put(account.Id,account);
}

Next, with both Task AND Events I do this but I will only show Task. I have to run the above SOQL a total of 4 times listActivitiesByAccountId(), once for Task AND Event, and another for each to get face to face activities only.

/* Setup TOUCH TASK Fields for Accounts */
List<Task> account_touch_tasks = listActivitiesByAccountId(accountId_set, 'Task', 'none'); /* 1 SOQL */
if(!account_touch_tasks.isEmpty()){
    for(Task t : account_touch_tasks){
        account_map.get(t.AccountId).Last_Touch_Task_Id__c = t.Id;
        account_map.get(t.AccountId).Last_Touch_Task_Date__c = t.ActivityDate;
        account_map.get(t.AccountId).Last_Touch_Task_Subject__c = t.Subject_Prefix__c;
    }

    /* Setup F2F TASK Fields for Accounts */
    List<Task> account_F2F_tasks = listActivitiesByAccountId(accountId_set, 'Task', 'f2f'); /* 1 SOQL */
    if(!account_F2F_tasks.isEmpty()){
        for(Task t : account_F2F_tasks){
            account_map.get(t.AccountId).Last_F2F_Task_Id__c = t.Id;
            account_map.get(t.AccountId).Last_F2F_Task_Date__c = t.ActivityDate;
            account_map.get(t.AccountId).Last_F2F_Task_Subject__c = t.Subject_Prefix__c;
        }
    }
}

UPDATE account_map.values();

Obviously I believe the original SOQL solution would have been the best so that I don't have to iterate through all the returned activities but given the limitation this is the best solution I can think of. Curious to get any feedback on how to optimize this. For now it works; pushing to production.

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.