1

I (attempted) to write a trigger after insert of a new task that queries for the first activity date, most recent call date, and count of calls, then saves those values to the lead. When looking at results, I see Most Recent Call Date and Calls populated on every record the trigger fires on. First Activity Date is only populating on roughly 7/8 of them. I've confirmed several times that the inserted tasks fit all the criteria. Is there something in the code I'm missing that would cause the second loop to fire 100% but the first loop to only fire most of the time?

ALSO, if I go into a record where the First Activity Date did not populate and make a small edit (i.e. add a period in the subject), the trigger fires and First Activity Date populates. Weird!

I know that seems strange but I can only check the inserted task fields so many times.

trigger repActivity3 on Task (after insert, after update) {
Set<Id> leadsToCheck = new Set<Id>();
for(Task t : Trigger.new) {
    if(t.WhoId != null && String.valueOf(t.WhoId).startsWith('00Q')) {
        leadsToCheck.add(t.WhoId);
    }
}

List<Lead> leadsToUpdate = [Select Id 
                            From Lead 
                            where ID in :leadsToCheck and isconverted = FALSE];
for(aggregateresult a:[select min(createddate)mind from task where 
                whoid IN :leadstoUpdate and ((not Subject like '%Inbound%')
                          and status ='Completed' 
                          and (created_role__c ='Sales Manager' 
                          or created_role__c = 'Sales Consultant' 
                          or created_role__c = 'Senior Sales Consultant') 
                          and (not subject like '%Pardot%')) ]){

for(Lead l : leadsToUpdate) {
    l.first_activity_date__c = (datetime)a.get('mind');
}
}
for(aggregateresult b:[select max(createddate)datet, count(id)c from task 
   where status = 'Completed' and whoid IN :leadstoUpdate and activity_type__c = 'Call']){
    for(lead l: leadstoUpdate){
        l.Most_Recent_Call_Date__c = (datetime)b.get('datet');
        l.calls__c = (integer)b.get('c');
    }
  }


update leadstoUpdate;
}
2

I suspect you may want to rewrite your initial query here:

for(aggregateresult a:[select min(createddate)mind from task where 
            whoid IN :leadstoUpdate and ((not Subject like '%Inbound%')
                      and status ='Completed' 
                      and (created_role__c ='Sales Manager' 
                      or created_role__c = 'Sales Consultant' 
                      or created_role__c = 'Senior Sales Consultant') 
                      and (not subject like '%Pardot%')) ]){

By using the min(createddate)mind and whoid IN :leadstoUpdate you may not necessarily be getting a min createddate for each whoId. If you wrote this as whoid = :leadstoUpdate, you might get a more consistent result. Alternatively, and I think a better approach would be to use this kind of for loop:

for(Lead l : [select min(createddate)mind from task where 
            whoid = :leadstoUpdate and ((not Subject like '%Inbound%')
                      and status ='Completed' 
                      and (created_role__c ='Sales Manager' 
                      or created_role__c = 'Sales Consultant' 
                      or created_role__c = 'Senior Sales Consultant') 
                      and (not subject like '%Pardot%')) ]{

   l.first_activity_date__c = (datetime)get('mind'); 
   //not certain if this is proper syntax here
}

To make this even more explicit, I recommend you specify in your WHERE clause that you want the createddate to be the MIN createddate which takes you totally out of an aggregate query entirely. When you do that, there's no confusion on the syntax above and you simply get the oldest record for that lead.

I see no reason to run your final query below at all:

for(aggregateresult b:[select max(createddate)datet, count(id)c from task 
where status = 'Completed' and whoid IN :leadstoUpdate and activity_type__c = 'Call']){
for(lead l: leadstoUpdate){
    l.Most_Recent_Call_Date__c = (datetime)b.get('datet');
    l.calls__c = (integer)b.get('c');
}

Everything above should be contained in trigger.new. Just map your Who.Id to the Task.Id when collecting the leads and you should be able to pull these back out, saving you the additional query in your trigger.

  • I knew someone would have a better way. l.first_activity_date__c = (datetime)get('mind'); appears to not be proper syntax however. – Ian Bury Aug 26 '15 at 15:00
  • You for loop suggestion seems to work fine but I'm having trouble with the syntax to assign MIN(createddate) to l.first_activity_date__c. – Ian Bury Aug 26 '15 at 16:06
  • Try l.first_activity_date__c = createddate; (or 'mind') since it should only return one result for each lead as there's no longer a need to do a 'get' as you were before. – crmprogdev Aug 26 '15 at 16:54
  • My thoughts exactly. For both, variable does not exist. – Ian Bury Aug 26 '15 at 17:53
  • Try expr0. Also, as I put in my original answer. I recommend that you restructure your query to move the min part to WHERE createddate = MINIMUM or however one might phrase that. I also suggest that you add a debug statement to see exactly what's being returned in the query to determine how best to retrieve and assign it. – crmprogdev Aug 26 '15 at 18:12

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.