+ Start a Discussion
SeanCenoSeanCeno 

Sum Total Completed Events on Contact Page

All,
I'm trying to create a field that totals all the completed events on a contact page. I think I'm on the right track, but I'm not sure how to go about making the event an integer, then sum all of them. This will eventually be a batch job that just runs every morning, but I have to get the class right first obviously. Any help would be great! Here's my code thus far:
public class TotalCompletedEvents {
    //Grab list of contacts
    protected final Contact[] contactNewList = new Contact[] {};
    protected final Contact[] contactOldList = new Contact[] {};
        
    public TotalCompletedEvents(Contact[] contactOldList, Contact[] contactNewList) {
        this.contactNewList.addAll(contactNewList == null ? new Contact[] {} : contactNewList);
        this.contactOldList.addAll(contactOldList == null ? new Contact[] {} : contactOldList);
    }
    public void execute() {
        // Find all events associated to contacts
        Event[] eventList = [select ActivityDate, WhoId, EndDateTime, Subject, ActivityDateTime from Event where WhoId in :contactNewList];
        Map<Id, Contact> contactMap = new Map<Id, Contact>(contactNewList);
        for(Contact contact : contactNewList) {
            contact.Total_Completed_Events__c = null;
        }
        for(Event event : eventList) {
            Contact contact = contactMap.get(event.WhoId);
            if(Contact == null)
                continue;
            if(Event.EndDateTime < Date.Today())
                Contact.Total_Completed_Events__c += Event.EndDateTime;
        }
    }
}


 
Best Answer chosen by SeanCeno
David ZhuDavid Zhu
public class TotalCompletedEvents {
    //Grab list of contacts
    protected final Contact[] contactNewList;
    protected final Contact[] contactOldList;
        
    public TotalCompletedEvents(Contact[] contactOldList, Contact[] contactNewList) {
        this.contactNewList = contactNewList;
        this.contactOldList = contactOldList;
    }
    public void execute() {
        Map<Id, Contact> contactMap = new Map<Id, Contact>(contactNewList);
        AggregateResult[] ars = [select count(id),whoid from event where EndDateTime < :date.today() and whoid in :contactNewList group by whoid];
        Map<Id, Contact> contactMap1 = new Map<Id,Contact>(contactNewList);
        
        for (AggregateResult ar : ars) {
            //System.debug('Total Completed Events: ' + ar.get('expr0'));  //get count()
            //System.debug('Contact ID' + ar.get('whoid'));  //get id of contact
            Contact c = contactMap.get((id)ar.get('whoid'));
            c.total_completed_events__c = integer.valueof(ar.get('expr0'));
            contactMap1.remove((id)ar.get('whoid'));
        }
        
        for (Contact c : contactMap1.values())
        {
            c.total_completed_events__c = 0;
        }
        
        //update ContactNewList;
    }
}

All Answers

David ZhuDavid Zhu
public void execute() {

        Map<Id, Contact> contactMap = new Map<Id, Contact>(contactNewList);

        AggregateResult[] ars = [select count(id),whoid from event where EndDateTime < :date.today() and whoid in :contactNewList group by whoid];
	
	for (AggregateResult ar : ars) {

	        //System.debug('Total Completed Events: ' + ar.get('expr0'));  //get count()
		//System.debug('Contact ID' + ar.get('whoid'));  //get id of contact

		Contact c = contactMap.get((id)ar.get('whoid'));
		c.total_completed_events__c = integer.valueof(ar.get('expr0'));
	}

	update ContactNewList;

}
You may use this as refernece.
SeanCenoSeanCeno
Hey David,
Thanks for the response. I added this code to my class and wrote this trigger, but it doesn't seem to update the field.

Trigger:
trigger TotalCompletedEvents on Contact (before insert, before update) {
    Contact[] contactOldList = trigger.IsDelete ? null : trigger.old;
    Contact[] contactNewList = trigger.IsDelete ? trigger.old : trigger.new;
    new TotalCompletedEvents(contactOldList, contactNewList).execute();
}

Class:
public class TotalCompletedEvents {
    //Grab list of contacts
    protected final Contact[] contactNewList = new Contact[] {};
    protected final Contact[] contactOldList = new Contact[] {};
        
    public TotalCompletedEvents(Contact[] contactOldList, Contact[] contactNewList) {
        this.contactNewList.addAll(contactNewList == null ? new Contact[] {} : contactNewList);
        this.contactOldList.addAll(contactOldList == null ? new Contact[] {} : contactOldList);
    }
    public void execute() {
        Map<Id, Contact> contactMap = new Map<Id, Contact>(contactNewList);
        AggregateResult[] ars = [select count(id),whoid from event where EndDateTime < :date.today() and whoid in :contactNewList group by whoid];
        for (AggregateResult ar : ars) {
            //System.debug('Total Completed Events: ' + ar.get('expr0'));  //get count()
            //System.debug('Contact ID' + ar.get('whoid'));  //get id of contact
            Contact c = contactMap.get((id)ar.get('whoid'));
            c.total_completed_events__c = integer.valueof(ar.get('expr0'));
        }
        update ContactNewList;
    }
}

Am I supposed to use an integer instead of aggregateResult? Something like:
    public void execute() {
        Map<Id, Contact> contactMap = new Map<Id, Contact>(contactNewList);
        Integer i = [select count() from event where EndDateTime < :date.today() and whoid in :contactNewList];
        for (Integer ar : i) {
            //System.debug('Total Completed Events: ' + ar.get('expr0'));  //get count()
            //System.debug('Contact ID' + ar.get('whoid'));  //get id of contact
            Contact c = contactMap.get(Integer.valueOf(i.size()));
            c.Total_Completed_Events__c = integer.valueOf(i);
        }
        update ContactNewList;
    }
David ZhuDavid Zhu
If using trigger, then the class should be: (I tested on my org and it works)
public class TotalCompletedEvents {
    //Grab list of contacts
    protected final Contact[] contactNewList;
    protected final Contact[] contactOldList;
        
    public TotalCompletedEvents(Contact[] contactOldList, Contact[] contactNewList) {
        this.contactNewList = contactNewList;
        this.contactOldList = contactOldList;
    }
    public void execute() {
        Map<Id, Contact> contactMap = new Map<Id, Contact>(contactNewList);
        AggregateResult[] ars = [select count(id),whoid from event where EndDateTime < :date.today() and whoid in :contactNewList group by whoid];
        for (AggregateResult ar : ars) {
            //System.debug('Total Completed Events: ' + ar.get('expr0'));  //get count()
            //System.debug('Contact ID' + ar.get('whoid'));  //get id of contact
            Contact c = contactMap.get((id)ar.get('whoid'));
            c.total_completed_events__c = integer.valueof(ar.get('expr0'));
        }
        //update ContactNewList;  //don't need this in trigger.
    }
}
SeanCenoSeanCeno
So I commented the update out, went into an already saved Event and resaved it, yet my field is not updating. Is my trigger incorrect? My field is an integer field, does it need to be text instead?
David ZhuDavid Zhu
you need to copy all my code. It is not only commented one line. the filed is integer field. you need to edit Contact to see the updated value.
 
SeanCenoSeanCeno
Yep. I was testing wrong. I edited the Contact page and it's working now. Thanks for the help!
David ZhuDavid Zhu
public class TotalCompletedEvents {
    //Grab list of contacts
    protected final Contact[] contactNewList;
    protected final Contact[] contactOldList;
        
    public TotalCompletedEvents(Contact[] contactOldList, Contact[] contactNewList) {
        this.contactNewList = contactNewList;
        this.contactOldList = contactOldList;
    }
    public void execute() {
        Map<Id, Contact> contactMap = new Map<Id, Contact>(contactNewList);
        AggregateResult[] ars = [select count(id),whoid from event where EndDateTime < :date.today() and whoid in :contactNewList group by whoid];
        Map<Id, Contact> contactMap1 = new Map<Id,Contact>(contactNewList);
        
        for (AggregateResult ar : ars) {
            //System.debug('Total Completed Events: ' + ar.get('expr0'));  //get count()
            //System.debug('Contact ID' + ar.get('whoid'));  //get id of contact
            Contact c = contactMap.get((id)ar.get('whoid'));
            c.total_completed_events__c = integer.valueof(ar.get('expr0'));
            contactMap1.remove((id)ar.get('whoid'));
        }
        
        for (Contact c : contactMap1.values())
        {
            c.total_completed_events__c = 0;
        }
        
        //update ContactNewList;
    }
}
This was selected as the best answer
SeanCenoSeanCeno
Any idea why I'd be running into this error when testing for test coverage for deployment?
System.DmlException: Insert failed. First exception on row 0; first error: CANNOT_INSERT_UPDATE_ACTIVATE_ENTITY, TotalCompletedEvents: execution of BeforeInsert

caused by: System.ListException: Row with null Id at index: 0

Class.TotalCompletedEvents.execute: line 11, column 1
Trigger.TotalCompletedEvents: line 4, column 1: []

Does the trigger need to be on Event instead of Contact?
trigger TotalCompletedEvents on Contact (before insert, before update, before delete) {
    Contact[] contactOldList = trigger.IsDelete ? null : trigger.old;
    Contact[] contactNewList = trigger.IsDelete ? trigger.old : trigger.new;
    new TotalCompletedEvents(contactOldList, contactNewList).execute();
}
SeanCenoSeanCeno
FYI
If anybody is having trouble with the trigger, it seems that you can't use a "Before Insert" with trigger.old. Removed the before insert and everything it fine.
SeanCenoSeanCeno
If I wanted to add queryAll() to this soql in order to access archived events as well, how would I go about doing that?