+ Start a Discussion
kcpluspluskcplusplus 

Most recent 'completed' event, query question.

I'm working on a trigger to update the account and the contact with the most recent completed event, I'm running through trigger.net and creating a map of event what id's and values as the ActivityDate, which I use for another part of the trigger. I then have a method to check if the whatid is an account, working fine.

 

The issue is, I then get down to getting the Most Recent Completed event activity date, but I have to do it in a for loop, so I can theoretically do it for every event getting inserted and also query against all that accounts events, to make sure I'm returning the right value. Everything is working fine, and I'm sure I could get away with using this, provided it was turned off for mass data loads. However, I would prefer to write this trigger as bulkified, but I'm having trouble coming up with a good way to do that. Any thoughts would be appreciated.

 

Eventually, I may move more logic to a class, but for now I'm just working in the trigger. It will be for accounts and contacts, whoid and whatid, and do both the most recent completed and the most recent future event, which would require four queries in for loops, two for accounts and two for contacts. Plus the two other queries.

 

Any points to a good example of someone dealing with something similar would be great or another way I can approach thi, that I'm not seeing.

 

trigger setLastEvent on Event (after insert) {
   	MAP<ID,Date> mapAccount = new MAP<ID,Date>();
    MAP<ID,Date> mapContact = new MAP<ID,Date>();
    //loop through trigger.new to get all of the what and who id's and add them to  
    for(Event e : trigger.new){
    	Id what = e.WhatId; 
        Id who = e.WhoId;
        if(lastEventUtility.isAccount(what)){
        	mapAccount.put(what,e.ActivityDate);
        }
        if(lastEventUtility.isContact(who)){
        	mapContact.put(who,e.ActivityDate); 
        }
    }
    //queries for account & contact id's in the maps for loops, and creating update LISTs for DML
    LIST<Account> accountIds = [SELECT id FROM Account Where Id IN: mapAccount.keySet()];
    LIST<Account> updateAccount = new LIST<Account>(); 
    LIST<Contact> contactIds = [SELECT id FROM Contact Where Id IN: mapContact.keySet()];
    LIST<Contact> updateContact = new LIST<Contact>(); 
    Date varToday = Date.Today(); 
    
    //loop to make updates
    for(Account a : accountIds){
    	LIST<Event> getEvents = [SELECT id,ActivityDate
                                 FROM Event 
                                 WHERE ActivityDate < :varToday
                                 ORDER BY ActivityDate desc
                                 LIMIT 1 ];
        a.Last_Meeting2__c = getEvents[0].ActivityDate; 
        a.Most_Recent_Meeting__c = mapAccount.get(a.id);
        updateAccount.add(a);
    }
    update updateAccount; 
}

 

kcpluspluskcplusplus

Just for clarification, there are multiple things I need to add to this, particularly a check if the account and contact lists contain id's before the loop, but I'm trying to knock out the query issue first.

Hengky IlawanHengky Ilawan

Hi,

 

I assume you miss a line in red?

//loop to make updates
    for(Account a : accountIds){
    	LIST<Event> getEvents = [SELECT id,ActivityDate
                                 FROM Event 
                                 WHERE ActivityDate < :varToday
                                    AND WhatId = :a.Id
                                 ORDER BY ActivityDate desc
                                 LIMIT 1 ];
        a.Last_Meeting2__c = getEvents[0].ActivityDate; 
        a.Most_Recent_Meeting__c = mapAccount.get(a.id);
        updateAccount.add(a);
    }

Anyway, I think you should move the query out of the for loop, to prevent it from hitting the governor limit.

 

Regards,

Hengky

kcpluspluskcplusplus

Yes, that I just added a little while ago, doesn't answer the main issue though. I can move the query out of the loop, but that still leaves the issue of how to bulkify this, and I don't think I can. If there is a mass insert of accounts or contacts, there isn't going to be a way to query for the events for each one to get the most recent and most recent future events.

 

Besides adding the reference to make sure  it's associated with the account, I was doing this in a dev sandbox, and only had one account created anyway, I moved the queries out of the loops and I have a check on the size of the accountId LIST, if it's not equal to 1, then it throws a custom error to deactivate the triggr for a mass load. I think the nature of this will only allow it to work on a single record, since I still have to reference the index of the getEvents queries.

 

Like I said, I have something that will ultimately work, but if possible I'd like to get it to conform to best practices as much as possible.

 

trigger setLastEvent on Event (after insert) {
   	MAP<ID,Date> mapAccount = new MAP<ID,Date>();
    MAP<ID,Date> mapContact = new MAP<ID,Date>();
    //loop through trigger.new to get all of the what and who id's and add them to  
    for(Event e : trigger.new){
    	Id what = e.WhatId; 
        Id who = e.WhoId;
        if(lastEventUtility.isAccount(what)){
        	mapAccount.put(what,e.ActivityDate);
        }
        if(lastEventUtility.isContact(who)){
        	mapContact.put(who,e.ActivityDate); 
        }
    }
    //queries for account & contact id's in the maps for loops, and creating update LISTs for DML
    LIST<Account> accountIds = [SELECT id FROM Account Where Id IN: mapAccount.keySet()];
    LIST<Account> updateAccount = new LIST<Account>(); 
    LIST<Contact> contactIds = [SELECT id FROM Contact Where Id IN: mapContact.keySet()];
    LIST<Contact> updateContact = new LIST<Contact>();
    LIST<Event> getPastEvents = [SELECT id,ActivityDate
                                 FROM Event 
                                 WHERE ActivityDate < :varToday AND WhatId = :a.id
                                 ORDER BY ActivityDate desc
                                 LIMIT 1 ];
    LIST<Event> getFutureEvents = [Select id,ActivityDate
                                   FROM Event
                                   WHERE ActivityDate > :varToday AND WhatId = :a.Id
                                   LIMIT 1];
    Date varToday = Date.Today(); 
    
    //loop to make updates
    for(Account a : accountIds){
        if(accountIds.size() == 1){
        a.Last_Meeting2__c = getPastEvents[0].ActivityDate; 
        a.Most_Recent_Meeting__c = mapAccount.get(a.id);
        updateAccount.add(a);
        }
        else{
        	a.addError('You cannot insert more than one account without the setLastEvent trigger deactivated');
        }
    }
    update updateAccount; 
}

 

Hengky IlawanHengky Ilawan

Hi,

 

If I dont understand you wrongly, when an event is created, the Most_Recent_Meeting__c value will become Last_Meeting2__c, correct?

So why not setting the Last_Meeting2__c with the old value of Most_Recent_Meeting__c before you update the Most_Recent_Meeting__c with value from trigger.new? Can save you a SOQL query to the Event object :)

 

I modified some of your codes related to Account only

 

trigger setLastEvent on Event (after insert) {
    MAP<ID,Date> mapAccount = new MAP<ID,Date>();
    MAP<ID,Date> mapContact = new MAP<ID,Date>();
    //loop through trigger.new to get all of the what and who id's and add them to  
    for(Event e : trigger.new){
    	Id what = e.WhatId; 
        Id who = e.WhoId;
        if(lastEventUtility.isAccount(what)){
        	mapAccount.put(what,e.ActivityDate);
        }
        if(lastEventUtility.isContact(who)){
        	mapContact.put(who,e.ActivityDate); 
        }
    }
    //queries for account & contact id's in the maps for loops, and creating update LISTs for DML
    Map<Id, Account> accountIds = new Map<Id, Account>([
        SELECT id, Last_Meeting2__c, Most_Recent_Meeting2__c FROM Account Where Id IN: mapAccount.keySet()]);
    
    LIST<Contact> contactIds = [SELECT id FROM Contact Where Id IN: mapContact.keySet()];
    LIST<Contact> updateContact = new LIST<Contact>(); 
    
    //loop to make updates
    for(Account a : accountIds.values()){
        a.Last_Meeting2__c = a.Most_Recent_Meeting__c; 
        a.Most_Recent_Meeting__c = mapAccount.get(a.id);
    }
    update accountIds.values(); 
}

 

Let me know if it works, since I didn't try it.

 

Regards,

Hengky

 

kcpluspluskcplusplus

That isn't necessarily true though, while for most cases that would probably be true. There could be cases where a event due date is far into the future, while another previously created is much closer. I'm realizing I have a lot of updates to do, because I need to change this to be after update as well, but still wondering how in the long run to best handle that scenario.

 

If event A is created on an account with a due date of 10/20/2012 today and then tomorrow one is created with a due date of 10/30/2012, I would still want that to say 10/20/2012. Which I realized while I'm writing this, may be able to be handled with some additional conditionals, comparing the new due date. That may be the answer actually.

 

Essentially I was using the query to order the events associated with the account, but if it's created or updated, and the field isn't blank, then compare the new date with the date in that field and keep the closer of the two.

 

Hengky IlawanHengky Ilawan

Maybe add some checkings before you update the fields will do

 

    Date varToday = Date.today();
    for(Account a : accountIds.values()){
        if (a.Most_Recent_Meeting__c <= varToday) {
            a.Last_Meeting2__c = a.Most_Recent_Meeting__c;
        }
        if (mapAccount.get(a.Id) < a.Most_Recent_Meeting__c) {
            a.Most_Recent_Meeting__c = mapAccount.get(a.id);
        }
    }

Regards,

Hengky

kcpluspluskcplusplus

Exactly, that's what I did and it looks like it's going to be the answer. Thanks for your help.

Hengky IlawanHengky Ilawan

No problem.

I think you might need to add a null checking for Last_Meeting__c and Most_Recent_Meeting__c before comparing them with some date values.

 

Regards,

Hengky