+ Start a Discussion
JosephTJosephT 

Trigger for Outgoing Tasks to Lead Counter field.

I want to create a counter field at the lead level.  This field would count the total of all 'Outgoing' tasks for a lead prior to the lead reaching a 'Qualified' lead status.  Activities to be included are Call, Call: Attempted, Call: Reached, Appointment Kept, Appointment Scheduled, Voicemail left  AND Emails.  It appears that I would need to create a Trigger to do this BUT, I am not a coding man...  Any ideas?

SFAdmin5SFAdmin5

I've done this before and I know I've got the code somewhere.  Let me see if I can dig it out for you...

JosephTJosephT

That would be awesome, thank you...

SFAdmin5SFAdmin5

no problem.  actually i did this from lead to a related account and it works, but let me try to clean it up and adjust it for task to lead.

 

also fyi, this is a trigger.  triggers are handy for real time results but you can run into governor limits with aggregate triggers like what I'll try to provide.  basically if you have a ton of task records this thing will fail, in which case you'd have to go with a batch job or scheduled batch job which could handle the record volume, but would sacrifice the real time counter functionality that the trigger can provide.  Theoretically you could run a batch job a lot (like every hour or something, or even more frequently if you really wanted to, though that would eat up your sched batch job limit) but unless it's a real business need for this counter, you might need to run the batch jobs daily to get a once a day rollup count. 

 

Anyway let me see if I can get this working for you.

 

 

SFAdmin5SFAdmin5

Alright here we go.

 

So, the trigger is below.  Put it on the task object.

 

Before doing so make sure you set up this formula type field on task object:

 

Task_Name_Count__c = a formula field with type Number (18,0).  Here's the formula:

 

IF( 
Id <> null, 

1,0 

)

 

 

AND this number type field on Lead object:

 

Task_Counter__c (18,0)

 

 

Here is the trigger.  This works for me in a simple dev environment.  Your reqs aren't totally clear so let me know if you don't know how to adjust the code for your specs.  Basically it's a counter trigger that runs off specific field values in the lead and task records.

 

trigger taskTrigger on Task (after insert, after update, after delete) {
 if ((Trigger.isInsert || Trigger.isUpdate || Trigger.isDelete) && Trigger.isAfter) {    
 
   set<Id> LeadIds = new set<Id>();
  
  if(trigger.isInsert || trigger.isUpdate){
    for(Task t : trigger.new){
      LeadIds.add(t.WhoId);
    }
  }
 
   if(trigger.isDelete){
    for(Task t : trigger.old){
      LeadIds.add(t.WhoId);
    }
  }
   map<Id,Double> LeadMap = new map<Id,Double> ();
 
  for(AggregateResult ar : [select WhoId,sum(Task_Name_Count__c)
    from Task where Status = 'Outgoing' 
                            AND 
                            (Type = 'Call' 
                            OR Type = 'Call: Reached'
                            OR Type = 'Appointment Kept'
                            OR Type = 'Appointment Scheduled'
                            OR Type = 'Voicemail left'
                            OR Type = 'Email')
                    
                    AND WhoId IN :LeadIds group by WhoId]){
      LeadMap.put((Id)ar.get('WhoId'),(Double)q.get('expr0'));
  }
 
  List <Lead> LeadsToUpdate = new List <Lead> ();
 
  for(lead l : [Select Id, Task_Counter__c from Lead where Status = 'Qualified' AND Id IN :LeadIds]){
    Double Sum = LeadMap.get(l.Id);
    l.Task_Counter__c = Sum;
    LeadsToUpdate.add(l);
  }
 
  update LeadsToUpdate;  
  
 
 
  } 
}

 Let me know if you have issues.  I'll help you out.

 

Haven't run any tests on this and it works in a dev environment, but might fail in prod.  If you can get this working I can help you with the test class.

JosephTJosephT

Thank you.  I am refreshing my Sandbox now with the new fields.  I need this to capture all of the Tasks mentioned for any lead UNTIL it is in a "Qualified by PA" status and then, it should no longer count the activities.  I wil create it when the Sandbox is refreshed and keep you posted.

SFAdmin5SFAdmin5

made some updates to this and also including the test class.  trigger passes at 100% in my dev environment with this.

 

trigger:

 

trigger taskTrigger on Task (after insert, after update, after delete) {
 if ((Trigger.isInsert || Trigger.isUpdate || Trigger.isDelete) && Trigger.isAfter) {    
 
   set<Id> LeadIds = new set<Id>();
  
  if(trigger.isInsert || trigger.isUpdate){
    for(Task p : trigger.new){
      LeadIds.add(p.WhoId);
    }
  }
 
   if(trigger.isDelete){
    for(Task p : trigger.old){
      LeadIds.add(p.WhoId);
    }
  }
   map<Id,Double> LeadMap = new map<Id,Double> ();
 
  for(AggregateResult q : [select WhoId,sum(Task_Name_Count__c)
    from Task where Status = 'Outgoing' 
                            AND 
                            (Type = 'Call' 
                            OR Type = 'Call: Reached'
                            OR Type = 'Appointment Kept'
                            OR Type = 'Appointment Scheduled'
                            OR Type = 'Voicemail left'
                            OR Type = 'Email')
                    
                    AND WhoId IN :LeadIds group by WhoId]){
      LeadMap.put((Id)q.get('WhoId'),(Double)q.get('expr0'));
  }
 
  List <Lead> LeadsToUpdate = new List <Lead> ();
 
  for(lead l : [Select Id, Task_Counter__c from Lead where Status = 'Qualified' AND Id IN :LeadIds]){
    Double Sum = LeadMap.get(l.Id);
    l.Task_Counter__c = Sum;
    LeadsToUpdate.add(l);
  }
 
  update LeadsToUpdate;  
  
 
 
  } 
}


 test class:

 

@isTest
private class TestLeadRollUpTasks {
 
    static testMethod void testRollup() {
 
        Profile pf = [Select Id from Profile where Name = 'System Administrator'];
 
        User u = new User();
        u.FirstName = 'Test';
        u.LastName = 'User';
        u.Email = 'testuser@test123456789.com';
        u.CompanyName = 'test.com';
        u.Title = 'Test User';
        u.Username = 'testuser@test123456789.com';
        u.Alias = 'testuser';
        u.CommunityNickname = 'Test User';
        u.TimeZoneSidKey = 'America/Mexico_City';
        u.LocaleSidKey = 'en_US';
        u.EmailEncodingKey = 'ISO-8859-1';
        u.ProfileId = pf.Id;
        u.LanguageLocaleKey = 'en_US';
        insert u;
 
        system.runAs(u){
 
            Lead l = new Lead();
            l.FirstName = 'Joe';
            l.LastName = 'Smith';
            l.Status = 'Qualified';
            l.Company = 'Test';
            insert l;
 
            system.assertEquals(l.Task_Counter__c, null);
 
            Task t1 = new Task();
            t1.WhoId = l.Id;
            t1.Subject = 'test';
            t1.Priority = 'Normal';
            t1.Status= 'Outgoing';
            t1.Type = 'Call';
            insert t1;
 
            Lead lu1 = [select Task_Counter__c from Lead where Id = :l.Id];
            system.assertEquals(lu1.Task_Counter__c,1);
 
Task t2 = new Task(); t2.WhoId = l.Id; t2.Subject = 'test'; t2.Priority = 'Normal'; t2.Status= 'Outgoing'; t2.Type = 'Call'; insert t2; AggregateResult ag1 = [select sum(Task_Name_Count__c) from Task where WhoId = :l.Id]; Lead lu3 = [select Task_Counter__c from Lead where Id = :l.Id]; system.assertEquals(lu3.Task_Counter__c,ag1.get('expr0'));
delete t2; AggregateResult ag2 = [select sum(Task_Name_Count__c) from Task where WhoId = :l.Id]; Lead lu4 = [select Task_Counter__c from Lead where Id = :l.Id]; system.assertEquals(lu4.Task_Counter__c,ag2.get('expr0')); } } }

 

JosephTJosephT

The Trigger and Test work perftect.  I tweaked them to maych my custom fields BUT, when I package them, they try to install these (2) fields; Task Counter and Task Type.  I can't seem to create the package without the (2) field creations being included...

SFAdmin5SFAdmin5

not sure i follow you.  are you using this trigger to create an application?

JosephTJosephT

No, I created the trigger/test in my Sandbox but when I put it in a Package and tried to install it in production, I get an error message because those two fields already exist in my Production instance.

SFAdmin5SFAdmin5

you mean a change set?

 

if the fields already exist in prod you don't need to put them in the change set.  you should just need to upload the trigger and test class

JosephTJosephT

So, I rolled it out but, I am now receiving an Developer Script Exception;

 

Apex script unhandled trigger exception by user/organization: 00550000000vs5E/00D300000001Sh9

 

taskTrigger: execution of AfterDelete

 

caused by: System.QueryException: Non-selective query against large object type (more than 100000 rows). Consider an indexed filter or contact salesforce.com about custom indexing.

Even if a field is indexed a filter might still not be selective when:

1. The filter value includes null (for instance binding with a list that contains null) 2. Data skew exists whereby the number of matching rows is very large (for instance, filtering for a particular foreign key value that occurs many times)

 

Trigger.taskTrigger: line 19, column 1

SFAdmin5SFAdmin5

yep that's what i mentioned in my earlier post- a trigger will fail if you have a ton of task records.  this thing is hitting a governor limit and i'm pretty sure the only solution in this situation is a batch job over a trigger.  i'll see if i can do something 

JosephTJosephT

I am also seeing the error on a "Send and Add' from Outlook...

 

>>> emailtosalesforce@3juk8gao4vs19j43xzv4qq6n2.31sh9eae.7.le.salesforce

>>> .com (Undelivered): 554 System.UnexpectedException: taskTrigger:

>>> execution of AfterInsert  caused by: System.QueryException:

>>> Non-selective query against large object type (more than 100000

>>> rows). Consider an indexed filter or contact salesforce.com about

>>> custom indexing. Even if a field is indexed a filter might still not

>>> be selective when: 1. The filter value includes null (for instance

>>> binding with a list that contains null) 2. Data skew exists whereby

>>> the number of matching rows is very large (for instance, filtering

>>> for a particular foreign key value that occurs many times) 

>>> Trigger.taskTrigger: line 19, column 1  (System Code)

JosephTJosephT

COuld I filter this by Role?  AND CreatedBy.UserRole.Id='00E70000000lXI7'

JosephTJosephT

Still another bounce back from our "salesforce to outlook' integration;

 

A message that you sent could not be delivered to one or more of its recipients. This is a permanent error. The following address(es) failed:

>>> emailtosalesforce@4fwzu2eskqq2p92praa09uyrx.31sh9eae.7.le.salesforce
>>> .com (Undelivered): 554 System.UnexpectedException: taskTrigger:
>>> execution of AfterInsert caused by: System.QueryException:
>>> Non-selective query against large object type (more than 100000
>>> rows). Consider an indexed filter or contact salesforce.com about
>>> custom indexing. Even if a field is indexed a filter might still not
>>> be selective when: 1. The filter value includes null (for instance
>>> binding with a list that contains null) 2. Data skew exists whereby
>>> the number of matching rows is very large (for instance, filtering
>>> for a particular foreign key value that occurs many times)
>>> Trigger.taskTrigger: line 19, column 1 (System Code)

Bayarea 101Bayarea 101
I need to create a field on contact. It should be recording how many time this contact has been contacted. Based on the activity history and put it as number on contact counter how many time the contact has been contacted.