Counting Open Activities in Salesforce
Overview
As it turns out there is no declarative way to counting open activities and updating the results on Account, Contact, Lead or Opportunity. At least not as of the time I’m writing this post. Counting open activities can be used in several ways, from finding leads that do not currently contain any open activities to gauging which accounts are getting the most attention. From a technical perspective, having these counts on the parent objects allows admins/developers to automate business process a whole lot easier.
Now, there are a few ways to tackle this issue, but the one we will focus on today will utilize one Apex class and two triggers to do the heavy lifiting.
Recipe
- 4 Custom Fields
- 1 Utility Class
- 2 Triggers
- 1 Unit Test
Create Open Activity Count fields for each object needed
The first thing you will need to do is create a Number field on each object you wish to count open actives. In this case we’ll be updating the following objects
- Account
- Contact
- Opportunity
- Lead
In this example you’ll need to Label these fields “Open Activity Count” with the Field Name “Open_Activity_Count“. You can leave the default Length at 18 and the Decimal places at 0. I would also recommend you update the Default Value to 0.
I’ll assume you know how to create custom fields in Salesforce so I’ll skip the visuals. For those of you who don’t know how to do this step either check out the Salesforce help documentation: https://help.salesforce.com/apex/HTViewHelpDoc?id=adding_fields.htm&language=en or go into your Salesforce sandbox and navigate to: {Your Name} > Setup > Customize > Account > Fields > New
Adding these fields to the UI would help for visual testing purposes, but its not required as we’ll be creating a unit test to validate our count assumptions.
Creating the Utility Class
Using your favorite IDE, Developer Console or SalesForce’s UI, create a new class called ActivityUtils and paste/save the following code
public class ActivityUtils {
//config
String fieldToUpdate = 'Open_Activity_Count__c'; //this field must be added to each object we're updating
//state
set<id> accountIds;
set<id> contactIds;
set<id> opportunityIds;
set<id> leadIds;
public ActivityUtils(sObject[] records) {
accountIds = new set<id>();
contactIds = new set<id>();
opportunityIds = new set<id>();
leadIds = new set<id>();
captureWhatAndWhoIds(records);
addAccountIdsFromRlatedObjects();
}
public void updateAccountActivityCount() {
if(accountIds.size() == 0) return;
updateActivityCount('Account','WhatId', getStringFromIdSet(accountIds));
}
public void updateContactActivityCount() {
if(contactIds.size() == 0) return;
updateActivityCount('Contact','WhoId', getStringFromIdSet(contactIds));
}
public void updateOpportunityActivityCount() {
if(opportunityIds.size() == 0) return;
updateActivityCount('Opportunity','WhatId', getStringFromIdSet(opportunityIds));
}
public void updateLeadActivityCount() {
if(leadIds.size() == 0) return;
updateActivityCount('Lead','WhoId', getStringFromIdSet(leadIds));
}
private void updateActivityCount(String objToUpdate, String queryFld, String updateIds) {
string strQuery = 'SELECT Id, (SELECT Id FROM OpenActivities) FROM ' + objToUpdate + ' WHERE Id IN (' + updateIds + ')';
sObject[] sobjects = new list<sobject>();
for(sObject so : database.query(strQuery)) {
OpenActivity[] oActivities = so.getSObjects('OpenActivities');
Integer openActivityCount = oActivities == null ? 0 : oActivities.size();
sObject obj = createObject(objToUpdate, so.Id);
obj.put(fieldToUpdate, openActivityCount);
sobjects.add(obj);
system.debug('openActivityCount: ' + openActivityCount);
}
update sobjects;
}
private void captureWhatAndWhoIds(sObject[] objects) {
for(sObject o : objects) {
Id whatId = (Id)o.get('WhatId');
Id whoId = (Id)o.get('WhoId');
if(whatId != null) {
String objectName = getObjectNameFromId(whatId);
if(objectName == 'account') accountIds.add(whatId);
if(objectName == 'opportunity') opportunityIds.add(whatId);
}
if(whoId != null) {
String objectName = getObjectNameFromId(whoId);
if(objectName == 'contact') contactIds.add(whoId);
if(objectName == 'lead') leadIds.add(whoId);
}
}
}
private void addAccountIdsFromRlatedObjects() {
for(Opportunity o : [SELECT AccountId FROM Opportunity WHERE Id IN :opportunityIds]) accountIds.add(o.AccountId);
for(Contact c : [SELECT AccountId FROM Contact WHERE Id IN :contactIds]) accountIds.add(c.AccountId);
}
private String getObjectNameFromId(Id objId) {
String preFix = String.valueOf(objId).left(3).toLowercase();
if(prefix == '001') return 'account';
if(prefix == '003') return 'contact';
if(prefix == '006') return 'opportunity';
if(prefix == '00q') return 'lead';
return '';
}
private String getStringFromIdSet(set<id> idSet) {
string idString = '';
for(Id i : idSet) idString+= '\'' + i + '\',';
return idString == '' ? idString : idString.left(idString.length()-1); //If idString contains some ids we want to ensure we strip out the last comma
}
//The main part of the method below was taken from //Taken from http://www.salesforce.com/us/developer/docs/apexcode/Content/apex_dynamic_dml.htm
//However we've modified this to accept an object id
private sObject createObject(String typeName, Id objId) {
Schema.SObjectType targetType = Schema.getGlobalDescribe().get(typeName);
if (targetType == null) {
// throw an exception
}
// Instantiate an sObject with the type passed in as an argument
// at run time.
return targetType.newSObject(objId);
}
}
Some of this code can be reused for other projects you may be working on. For example you could create a class called “utils” which holds the captureWhatAndWhoIds, getObjectNameFromId and getStringFromIdSet methods and convert them to “static“. This way you could reference them in your projects classes using dot notation like so: utils.getStringFromIdSet(someIdSet).
One could also argue that the methods contained in this class could be made so they are all static or that you could store the fieldToUpdate value. There are many ways to work this example, however this was the quickest way I was able to achieve our count result and share with you ! :)
Creating the Triggers
Create a new trigger on the Task object and paste/save the following code
trigger TaskTrigger on Task (after insert, after update, after delete, after undelete) {
sObject[] triggerRecords;
if(!trigger.isDelete) triggerRecords = trigger.new;
else triggerRecords = trigger.old;
//Update Open Activity Count
ActivityUtils au = new ActivityUtils(triggerRecords);
au.updateAccountActivityCount();
au.updateContactActivityCount();
au.updateLeadActivityCount();
au.updateOpportunityActivityCount();
}
Create a new trigger on the Task object and paste/save the following code
trigger EventTrigger on Event (after insert, after update, after delete, after undelete) {
sObject[] triggerRecords;
if(!trigger.isDelete) triggerRecords = trigger.new;
else triggerRecords = trigger.old;
//Update Open Activity Count
ActivityUtils au = new ActivityUtils(triggerRecords);
au.updateAccountActivityCount();
au.updateContactActivityCount();
au.updateLeadActivityCount();
au.updateOpportunityActivityCount();
}
Creating the Test Class
@isTest(seeAllData = true)
private class test_ActivityUtils {
static testMethod void mainTest() {
Account a = new Account();
a.Name = 'Test Account';
a.Industry = 'Other';
insert a;
Contact c = new Contact();
c.FirstName = 'Joe';
c.LastName = 'Smith';
c.AccountId = a.Id;
c.Email = 'test@test.com';
insert c;
Opportunity o = new Opportunity();
o.AccountId = a.Id;
o.StageName = 'Open';
o.CloseDate = Date.today() + 7;
o.Name = 'Test Opp';
insert o;
Lead l = new Lead();
l.LeadSource = 'Other';
l.FirstName = 'Joe';
l.LastName = 'Smith';
l.Industry = 'Other';
l.Status = 'New Lead';
l.Company = 'Test Co';
insert l;
Task[] tList = new list<task>();
Event[] eList = new list<event>();
for(Integer i=0; i<4; i++) {
Task t = new Task();
t.Status = 'Not Started';
t.Priority = 'Normal';
t.Type = 'Scheduled Call Back';
if(i==0) t.WhatId = a.Id;
if(i==1) t.WhatId = o.Id;
if(i==2) t.WhoId = c.Id;
if(i==3) t.WhoId = l.Id;
tList.add(t);
Event e = new Event();
e.StartDateTime = DateTime.now() + 7;
e.EndDateTime = DateTime.now() + 14;
if(i==0) e.WhatId = a.Id;
if(i==1) e.WhatId = o.Id;
if(i==2) e.WhoId = c.Id;
if(i==3) e.WhoId = l.Id;
eList.add(e);
}
insert tList;
insert eList;
test.startTest();
system.assertEquals(6, [SELECT Open_Activity_Count__c FROM Account WHERE Id = :a.Id].Open_Activity_Count__c);
system.assertEquals(2, [SELECT Open_Activity_Count__c FROM Opportunity WHERE Id = :o.Id].Open_Activity_Count__c);
system.assertEquals(2, [SELECT Open_Activity_Count__c FROM Contact WHERE Id = :c.Id].Open_Activity_Count__c);
system.assertEquals(2, [SELECT Open_Activity_Count__c FROM Lead WHERE Id = :l.Id].Open_Activity_Count__c);
//Delete some activities and run assertions again
delete eList;
system.assertEquals(3, [SELECT Open_Activity_Count__c FROM Account WHERE Id = :a.Id].Open_Activity_Count__c);
system.assertEquals(1, [SELECT Open_Activity_Count__c FROM Opportunity WHERE Id = :o.Id].Open_Activity_Count__c);
system.assertEquals(1, [SELECT Open_Activity_Count__c FROM Contact WHERE Id = :c.Id].Open_Activity_Count__c);
system.assertEquals(1, [SELECT Open_Activity_Count__c FROM Lead WHERE Id = :l.Id].Open_Activity_Count__c);
test.stopTest();
}
}
Update Historical Data
This part is optional, but strongly recommended. Since you probably have existing records in your system, those count values will not be populated (yet!).
Updating records using Dataloader is far beyond the scope of this post, but I’d like to give you a high level run-down of what you should do so you can get up and reporting on your new fields.
Since we are dealing with Events and Tasks, you’ll need to use Dataloader to update all Open Tasks and Open Events to fire off these newly created triggers and populate your count values.
To do so you would:
- Export all open Tasks
SOQL Example: Select Id, Subject FROM Task WHERE IsClosed = false - Export all open Events
SOQL Example: Select Id, Subject FROM Event WHERE EndDateTime <= TODAY - Without opening or updating any data in these CSV files, use Dataloader to Update the Task and Event objects using the corresponding exported CSV files.
Depending on your org and if you have reoccurring activities you may want to set Dataloader’s Batch Size to 1 under Dataloaders settings.
Now, this will only work on those objects that contain activities. You’ll also want to use Dataloader and update each object’s records where the Open_Activity_Count__c is Null and set the Open_Activity_Count__c to 0.
You’ll need to do this for Accounts, Contacts, Opportunities and Leads – but I’ll show you how to do this on Accounts and apply the example to the rest of the objects.
- Export Accounts without data in the Open_Activity_Count__c field
SOQL Example: Select Id, Open_Activity_Count__c FROM Account WHERE Open_Activity_Count__c = Null - Open this CSV using a Spreadsheet application like Excel or Google Docs and set every Open_Activity_Count__c to 0
- Update the Account object using newly exported and updated CSV file.
- Repeat steps 1 through 3 for Contacts, Opportunities and Leads
Wrapping Up
You should now have a working Open Activity counter and I’m sure your mind is starting to workout what new report or functionality you’d like to start working up.
Please leave me a comment if you have any feedback or questions!
Hi there! Is it possible to edit your code to show the count of all activities? I would really appreciate your help.
This was quite helpful and it does work. Have a nice day!
Thanks,
Melissa
This works very well – thanks!
If you add this to the end of your test, you can get 100% code coverage:
//Delete the rest activities and run assertions again for zero
delete tList;
system.assertEquals(0, [SELECT Open_Activity_Count__c FROM Account WHERE Id = :a.Id].Open_Activity_Count__c);
system.assertEquals(0, [SELECT Open_Activity_Count__c FROM Opportunity WHERE Id = :o.Id].Open_Activity_Count__c);
system.assertEquals(0, [SELECT Open_Activity_Count__c FROM Contact WHERE Id = :c.Id].Open_Activity_Count__c);
system.assertEquals(0, [SELECT Open_Activity_Count__c FROM Lead WHERE Id = :l.Id].Open_Activity_Count__c);
Awesome, thanks for the add Lawrence!
Hi Melissa, my apologies. I know we’ve discussed creating another post on this, but I’ve been pretty tied up lately.
The problem I see with pulling all activities is getting around the issue where Salesforce archives closed activities. I suppose it would be relatively simple to get closed tasks and events that aren’t yet archived. I’d have to actually code it to know for sure.
Looks like Roll-Up helper may solve the issue without us having to code it ourselves. Check here for more detail: https://success.salesforce.com/answers?id=90630000000gvHzAAI
I know this is from a while ago, but thank you for this. Really helpful in solving this particular problem (that my team keeps asking me about) and a really great example of generic reusable code that let’s us update multiple objects with the same code. Still wrapping my head around object-oriented programming and just had a little bit of a eureka moment reading this.
I tried doing this but whenever I run the test class I get this error:
System.AssertException: Assertion Failed: Expected: 6, Actual: 0
I copied and pasted everything as outlined. What did I miss.
Nevermind, for some silly reason I thought I could test the class before adding the triggers…not sure what I was thinking…..ha
The test passed.
Hi rjpalombo
Great post and great code – many thanks for sharing, it was just what I needed.
Melissa, I was also interested in counting Closed Activities so I amended rjpalombo’s code slightly to count both – I wrote up an example of having 2 fields counting Open and Closed Activities here http://mysalesforcestuff.blogspot.co.uk/2015/04/counting-open-and-closed-activities-in.html#more but I’d also recommend Rollup Helper.
Iain
Iain, good stuff! I’m glad this post is helping others and most importantly, others are adding to it. Thanks for taking the time to build out the closed portion!
Ahmad, happens to all of us! Glad it’s working :)
Mark, appreciate the kind words! I’ve been absent from this blog for a while and will come back to writing more very soon.
Great post! I have been looking to do something similar but just on Lead and Opportunity records as those are the only two we typically have an open task on. I am pretty new to triggers and was wondering if I edit your sample to remove any references to Accounts and Contacts would that generally work?
Thanks again!
Hi Kimberly, yes – in theory all you should have to do is remove any code specific to Account or Contact and that should give you what you are looking for :)
Hey – this has been great, but running into a few issues with “orphaned” activities – essentially activities that are created from our CTI/Telephone connect that are not associated with an AccountID or Related to ID – is there a way to throw an exception so we don’t ge this error anymore? Thanks for your help!!
Server error: Upsert failed. First exception on row 0 with id 00TU000001sxVdvMAE; first error: CANNOT_INSERT_UPDATE_ACTIVATE_ENTITY, OpenTaskTrigger: execution of AfterUpdate
caused by: System.QueryException: invalid ID field: null
Class.ActivityUtils.updateActivityCount: line 54, column 1
Class.ActivityUtils.updateAccountActivityCount: line 32, column 1
Trigger.OpenTaskTrigger: line 11, column 1: []
One thing you can do is ensure you do not pull any activities which do not have accounts.
So change this line:
'SELECT Id, (SELECT Id FROM OpenActivities) FROM ' + objToUpdate + ' WHERE Id IN (' + updateIds + ')';to
'SELECT Id, (SELECT Id FROM OpenActivities where whoid != null) FROM ' + objToUpdate + ' WHERE Id IN (' + updateIds + ')';I haven’t tried it, but in theory it should work. Let me know how it turns out!
Great post, very useful. Two quick tips from my implementation.
1. Before updating the Events and Tasks, load all of the counter fields with 0. It’s easier than to make sure you set the filters correctly when using DataLoader.
2. Each of the counters should have a default value of 0. That ensures you don’t create new records with null values in the counter.
Great post, very useful. Two quick tips from my implementation.
1. Before updating the Events and Tasks, load all of the counter fields with 0. It’s easier than to make sure you set the filters correctly when using DataLoader.
2. Each of the counters should have a default value of 0. That ensures you don’t create new records with null values in the counter.
Another great addition to this is the following post, which expanded on this one to include closed activities: http://mysalesforcestuff.blogspot.co.uk/2015/04/counting-open-and-closed-activities-in.html#more
Good adds, thx for the share!
Hi,
Thanks for the code this is amazing! I was just wondering how I can alter the code to ensure I only count activities in current FY. I am a noob at apex and would really appreciate the help.
Thank you
I would first change the names of the fields both on your objects and in the code to reflect that you only want Current Fiscal Year.
So something like: Open_Activity_Count_CFY__c
Then change the code
string strQuery = 'SELECT Id, (SELECT Id FROM OpenActivities) FROM ' + objToUpdate + ' WHERE Id IN (' + updateIds + ')';to
string strQuery = 'SELECT Id, (SELECT Id FROM OpenActivities WHERE ActivityDate >= THIS_FISCAL_YEAR) FROM ' + objToUpdate + ' WHERE Id IN (' + updateIds + ')';Believe it or not, “THIS_FISCAL_YEAR” is actually shorthand for the date range you are looking for and it will work!
Here is a full list that you can use within your code. Just make sure that if you are using these in APEX, that you replace the spaces with underscores as I have above.
https://help.salesforce.com/apex/HTViewHelpDoc?id=custom_dates.htm
Thank you!
Hi,
So I used this code, as well as Iain Clements code to input a code to count closed activities in Current FY. It works in my sandbox, but I cannot deploy it because my test keeps failing. I used the same test as here, except changed Open_Activity_Count__c to my field for counting closed activities ” Completed_Activities__c”. However I keep getting the error
System.AssertException: Assertion Failed: Expected: 6, Actual: 0
Is anyone experiencing this. I have been trying to tweek the test class but I cannot figure it out.
Hey Ram,
I’m in the same boat now … Are you able to figure out this problem ?
Thanks
Testing can be bittersweet. Many of the testing errors I come up against during deployments are not due to my code or unit tests, but because of something outside entirely. For example, you could have a workflow that is making your code act in a way you didn’t expect.
I would try to deactivate any activity workflows or validations and try your deployment again. I usually don’t state to make changes in a production org, but since you cannot reproduce in sandbox, I’d say there is something in prod that isn’t in the sandbox that is causing your issue.
Let me know how your make out!
I have run into an error which I haven’t been able to isolate 100% but seems to occur if there is an Account with a significant number of activities (>200). I’m not enough of a coder to fix this and am looking for some help.
Error text is:
“Aggregate query has too many rows for direct assignment, use FOR loop.”
The code it is pointing to is found on line 45 of the utility class. My code is shown below (in case I copied wrong and am overlooking it), starting with line 39:
private void updateActivityCount(String objToUpdate, String queryFld, String updateIds) {
string strQuery = ‘SELECT Id, (SELECT Id FROM OpenActivities) FROM ‘ + objToUpdate + ‘ WHERE Id IN (‘ + updateIds + ‘)’;
sObject[] sobjects = new list();
for(sObject so : database.query(strQuery)) {
OpenActivity[] oActivities = so.getSObjects(‘OpenActivities’);
Integer openActivityCount = oActivities == null ? 0 : oActivities.size();
sObject obj = createObject(objToUpdate, so.Id);
obj.put(fieldToUpdateOpen, openActivityCount);
sobjects.add(obj);
system.debug(‘openActivityCount: ‘ + openActivityCount);
Where does the for loop end? It seems there may be more that wasn’t included.
Was this ever resolved? I am running into this error whenever a task is logged in Salesforce and am struggling with how to reach a resolution.
Error: Invalid Data.
Review all error messages below to correct your data.
Apex trigger TaskTrigger caused an unexpected exception, contact your administrator: TaskTrigger: execution of AfterUpdate caused by: System.QueryException: Aggregate query has too many rows for direct assignment, use FOR loop: ()
Any help would be greatly appreciated.
Mollie
Hi ,
This is great post and Thanks for sharing.
I used the same code and which is working perfect in the user interface(I tested all possibilities) but the test class is not firing the trigger.
To keep it simple I just inserted an Account record then inserted only one task to see if it is firing the trigger.
Used Try, Catch and Debug statements to figure out if anything is wrong but everything looks good. Debug statements giving all record details properly but showing the Count as ‘0’.
Please provide your input.
Thanks
Hi AJ, please post your code so I can take a look.
How can you do this based on a specific activity type? Such as to just see tasks that have the Type= Call?
Thanks
you would just change the inner select like so
(SELECT Id FROM OpenActivities WHERE Type IN ('Type1', 'Type2'))Where Type can be any text field you wish and Type1 and Type2 are any textual values you need to filter by.
Hey RJ, that is not working. It is giving me the following error: Error: Compile Error: expecting a semi-colon, found ‘Demo’ at line 38 column 85
Here is the inner select:
string strQuery = ‘SELECT Id, (SELECT Id FROM OpenActivities WHERE Type In (‘Demo 1’, ‘Demo 2’)) FROM ‘ + objToUpdate + ‘ WHERE Id IN (‘ + updateIds + ‘)’;
Thoughts?
It’s all a matter of escaping your inner single quotes.
Example:
String strQuery = ‘SELECT Id, (SELECT Id FROM OpenActivities WHERE Type In (\’Demo 1\’, \’Demo 2\’)) FROM’ + objToUpdate + ‘ WHERE Id IN (‘ + updateIds + ‘)’;
Hi Molly, all you need to do is filter using the ActivityType field.
So add in the WHERE clause: ActivityType IN (‘Call’, ‘Email’) etc.
Thank you so much this code is amazing!
I want to rework this so that I am only counting Leads with Open Activities with a Due Date today or later (what, sales reps not closing out ‘open activities’? crazy I know but it does happen so having the code count tasks and events that are only in the future would be a huge help)
Thanks!
Glad it helped! Hope you figured it out and let me know if you need any help.
This code is great however whenever I try to modify the strQuery to something like
string strQuery = ‘SELECT Id, (SELECT Id FROM OpenActivities where RecordType.name = ‘ + ‘\” + RecordTypeName + ‘\’) FROM ‘ + objToUpdate + ‘ WHERE Id IN (‘ + updateIds + ‘)’;
I receive the following error:
System.QueryException: Didn’t understand relationship ‘RecordType’ in field path. If you are attempting to use a custom relationship, be sure to append the ‘__r’ after the custom relationship name. Please reference your WSDL or the describe call for the appropriate names.: Class.ActivityUtils.updateActivityCount: line 45, column 1″.
Any help would be appreciated
Hi Lawrence, RecordType is not a field on the OpenActivity object which is why you are getting that error.
You may want to resort to counting on Tasks/Events if you need to filter on RecordType.
I’m sure there are other options.
Hi this is kalyan.I had created field dails in last 30 days in contact object.I want to calculate total number of activities with call type.Can you please help me out.
Does anyone get this error:
System.QueryException: List has no rows for assignment to SObject
Class.NVMTaskTrigger.mainTest: line 126, column 1
Every time when I run the test class I get this error.
Can anyone help me how to fix it.