How to Use an EventReceiver to Automate Task Status Updates
One of the challenges that consulting organizations (like ourselves) deal with on a daily basis is insuring that effective mechanisms exists to communicate with clients throughout the lifecycle of a project. In the old days (like, say, eons ago in the mid-nineties) weekly status meetings and conference calls would suffice but in today’s environment of instant communication these methods are outdated and ineffective; clients want to know what you did yesterday to move the ball closer to the goal line. Naturally, we use SharePoint to solve this problem; all of our engagements center around a project web site (WSS Team Site using a custom site definition) where project status is tracked in real time and, if it’s a custom development project, a TFS project site to track daily builds and code drops.
We use custom lists throughout our project sites to track project hours, milestones, tasks, issues, bugs, various project-related documentation, etc. This has the dual effect of a) concentrating all communication in a single place (no more "I never got that email attachment with the status report" calls), and b) forcing clients who are new to SharePoint to learn how to use it in a production environment that is contextually relevant to their job duties. As a project manager, I spend quite a bit of time coordinating the lists that our consultants (billable resources) use to track their time and deliverables and the lists that track the overall project status and task assignments. As we were kicking off a major new project, I realized that some of these processes could be automated using EventReceivers to calculate billable task hours in one list and update overall task status stored in another list.
Our process methodology employs a custom list to track daily resource hours and another custom list to display task and overall project status in summary form (backed by a more detailed Microsoft Project document). When resources enter time the project manager must aggregate the task totals each week and update the task list to maintain an accurate view of current project status. As the Task field in the time tracking list and the Title field in the Tasks lists are the same, it seemed logical to employ an EventReceiver that would fire whenever a time item is added, modified or deleted and automatically perform the calculations on the source list and update the corresponding status columns in the target list.
Here’s how it works:
- The user updates the Project Time Tracking list with the daily hour total and assigns the hours to a specific task (options in a standard Choice field type which match item Titles in the Tasks list).
- The EventReceiver fires on ItemAdded, gets a collection of all list items that match the task value of the new item being added, and sums the hourly totals.
- The EventReceiver accesses the Project Tasks list and, if the Status column is anything other than "Completed", changes it to "In Progress".
- Next, it gets the value in the Estimated Hours field and uses it to calculate the percentage complete using the hours total that was derived from Project Time Tracking list.
- If the user has selected the "Show as percentage" option in the Task "% Complete" field, it converts the numeric value to the proper decimal value.
- Finally, it modifies the total hours field and performs an update on the list item.
- The Task list now shows the updated Total Hours value and a correct % Complete value.
The same process applies to items that are modified or deleted, with the exception that, when deleted, the hours for the deleted task must be subtracted from the total hours as the item itself is returned in the collection of source items (when the ItemDeleting event fires the item still exists).
The code is fairly straightforward (please forgive the outmoded Hungarian Notation – some old habits just refuse to die an honorable death). First, global variables are declared to hold all the relevant column names:
public class TaskProgress : SPItemEventReceiver
// Declare variables for the source and target fields.
// Ideally, this should be associated with a custom list definition to insure that the field names are consistent.
protected string strSourceTaskField = "Task";
protected string strSourceTimeField = "Hours";
protected string strTargetTaskField = "Title";
protected string strTargetListName = "Project Tasks";
protected string strTargetTotalHours = "Total Hours";
protected string strTargetEstHours = "Estimated Hours";
protected string strTargetComplete = "PercentComplete";
protected bool bDeleted = false;
Next, the various events are overridden and the UpdateTasks() method is called:
// Override the appropriate events
public override void ItemAdded(SPItemEventProperties properties)
public override void ItemUpdated(SPItemEventProperties properties)
public override void ItemDeleting(SPItemEventProperties properties)
bDeleted = true;
Finally, the UpdateTasks() method does all the dirty work of collecting the hours, performing the calculations and updating the target list:
protected void UpdateTasks(SPItemEventProperties properties)
// Disable event firing for the life of this instance only to prevent concurrency issues
// Invoke the SPWeb object responsibly so that it will be properly disposed of
using (SPWeb web = properties.ListItem.ParentList.ParentWeb)
decimal iHours = 0;
// Get the task value from the current item
string strTask = properties.ListItem[strSourceTaskField].ToString();
// Set the source list
SPList lSource = properties.ListItem.ParentList;
// Get a collection of all the list items with the same task value as the initiating item.
SPQuery qrySrcQuery = new SPQuery();
string strSrcQuery = "<Where><Eq><FieldRef Name=’" + strSourceTaskField + "’ /><Value Type=’Choice’>" + properties.ListItem[strSourceTaskField].ToString() + "</Value></Eq></Where>";
qrySrcQuery.Query = strSrcQuery;
SPListItemCollection licSrcItems = lSource.GetItems(qrySrcQuery);
// Get each item that matches the given task. This prevents orphaned task items and keeps the target list in sync.
if (licSrcItems.Count > 0)
foreach (SPListItem liSrcItem in licSrcItems)
iHours = iHours + Convert.ToDecimal(liSrcItem[strSourceTimeField].ToString());
// Instantiate the target list. Pass in a CAML Query to get the matching list items.
SPList lTarget = web.Lists[strTargetListName];
SPQuery query = new SPQuery();
string strQuery = "<Where><Eq><FieldRef Name=’Title’ /><Value Type=’Text’>" + strTask + "</Value></Eq></Where>";
query.Query = strQuery;
SPListItemCollection licTargetItems = lTarget.GetItems(query);
// A quick and dirty way to prevent duplicates. This method is a bit fragile and could use better logic to insure uniqueness.
if (licTargetItems.Count > 0 && licTargetItems.Count < 2)
// Get the singular item
SPListItem liTargetItem = licTargetItems;
// Cast the field that contains the completion percentage. Note that this is the DISPLAY NAME of the field
// not the actual name as stored in the global variable. Modify this value if you have changed the DisplayName in the UI.
SPField liTargetField = liTargetItem.Fields["% Complete"];
decimal iTotalHours = Convert.ToDecimal(liTargetItem[strTargetEstHours].ToString());
decimal dPercComp = 0;
string sPercComp = "";
// If the event is ItemDeleting, subtract the value of the deleted item’s hours from the total.
iHours = iHours – Convert.ToDecimal(properties.ListItem[strSourceTimeField].ToString());
// Check to see if the display type is with Percentage or without and modify the value accordingly.
dPercComp = iHours / iTotalHours;
sPercComp = dPercComp.ToString().Substring(0, 4);
dPercComp = Math.Round((iHours / iTotalHours) * 100, 0);
sPercComp = dPercComp.ToString();
// Check the status value and update the target item with the new Status, Completion Percentage, and Total Hours values.
if (liTargetItem["Status"].ToString() != "Completed")
liTargetItem["Status"] = "In Progress";
liTargetItem[strTargetComplete] = sPercComp;
liTargetItem[strTargetTotalHours] = iHours;
// Turn event firing back on
catch // Add explicit exception handling
//Write to the event log if you wish.
The complete VS2005 project is available here. The project includes all the files required to build a WSS solution package (using MSBUILD – just CTRL+SHIFT+B to generate the .wsp) but does NOT include any list definition files; you’ll have to create the time tracking and task lists manually (they’re basic out-of-the-box lists with some additional choice and numeric fields). On activation of the feature the FeatureReceiver assembly will assign the EventReceiver to the target list (modify the list name in TaskProgressFeatureReceiver.cs to match the name of your particular list). Special thanks to Andrew Connell for his SharePoint Project Utility Tool Window which I used to generate the solution files.
The code is pretty basic and serves mostly as an example on how to build an EventReceiver, deploy it as a solution, and use a FeatureReceiver to associate the EventReceiver with a particular list (and disassociate it if the feature is deactivated). If you make improvements or add any cool functionality please post it here so everyone can benefit.