You need to sign in to do that
Don't have an account?
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; }
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.
Hi,
I assume you miss a line in red?
Anyway, I think you should move the query out of the for loop, to prevent it from hitting the governor limit.
Regards,
Hengky
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.
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
Let me know if it works, since I didn't try it.
Regards,
Hengky
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.
Maybe add some checkings before you update the fields will do
Regards,
Hengky
Exactly, that's what I did and it looks like it's going to be the answer. Thanks for your help.
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