I needed a conference room scheduler that prevented duplicate assignments, in SharePoint 2007, to replace a classic ASP application that depended upon a different authentication model than we were planning to use in our SharePoint upgrade. I also wanted to remove anything specific to our building layout from the existing conference room scheduler, so it could be applied to other resources.
Microsoft’s Room and Equipment template, at first, seemed to be the perfect solution, but its shortcomings were soon evident. The biggest issues were: the user interface did not follow SharePoint standards, and details of the reservation were not visible. It also did not support well known calendar features like recurring appointments and Outlook integration.
After rejecting the Room and Equipment template, I decided to create a new custom SharePoint list based on the Calendar so I could leverage the existing features and functionality. The interface is well known to my users, so extending it would result in minimal training requirements. My biggest requirement was to prevent collisions. Since I wanted to keep things generic, I called the project Reservations. Once a user reserves a time slot, no other user could create an overlapping reservation. Additional requirements included: a custom POC field, and the start date has to be before the end date. The POC field is used when the user doing the scheduling is not the same as the person using the resource, but it can be removed if necessary. As additional resources need to be tracked, new reservation lists can be created by the site administrators.
Visual Studio 2005 extensions for Windows SharePoint Services 3.0, version 1.1 (VSeWSSv11.exe) did the bulk of the work when I created a project based on the list definition template. The list is based on the Event List (Type=106), so all the Calendar features were available automatically. The features necessary to make the list available within a site and the setup.bat that performs the deployment were automatically generated.
To use the code, run setup.bat in the sample application zip file on an Microsoft SharePoint 2007 server, activate the Reservation feature in the desired site, and then create a Reservation List on that site. The setup needs to be run once per server, and the feature activation needs to be run once per site. To activate the feature, select site settings and then site features. In the page that appears with the list of features, find the feature labeled Reservation, and press the Activate button as shown below:
Once the feature is activated, the Reservation option will appear on the Create screen as shown below:
When the item validation fails, a generic exception screen is displayed to the user, and almost all formatting is removed from the error message displayed, as shown below. The user can use the Back button to return to the edit form and adjust the values or cancel. Incidentally, the Room and Equipment template handles collisions the same way.
Since the collision detection occurs as a result of the current user querying the list for an overlapping entry, enabling content approval could result in problems. Two users could each create a reservation that was only visible to themselves and the content administrator. When the content administrator tries to approve a reservation, the other reservation will conflict. The imperfect solution is to delete one of the reservations before approving the other.
Using the Code
I created a content type to add my custom POC field, and the Item Event receiver is linked to the content type. The methods of the Item Event receiver are called upon specific user interface action related to a specific item in the list. They tend to be separated into two stages: before the action occurs (-ing) and after the action occurs (-ed).
The available event pairs are:
- Add (
- Update (
- Delete (
- Attachment Add (
- Attachment Delete (
- Check In (
- Check Out (
Unpaired events include:
Most of the code is in the
ItemEventReceiver class, except the recurring handling described below in Points of interest. To handle the validation, the
ItemUpdating methods of the Item Event Receiver were overloaded. Both methods take a
SPItemEventProperties object (properties) as a parameter, and all communications to SharePoint occur by modifying this properties object. To prevent the reservation from being saved, the
Cancel property of the properties object is set to
true. Any information returned to the user is done through the
ErrorMessage property. Due to the similarity of the functionality, both the
ItemUpdating methods delegate their functionality to the
TimeConflict method. The only difference is that when an overlapping appointment is discovered, the currently updated appointment is filtered from the conflict list by comparing the existing
ItemID with the
ItemID from the properties object.
TimeConflict method verifies the start date is less than the end date, and then calls the
FindOverlaps creates a
SPQuery object, and sets up a
DataTable for the conflicting appointments. I find
DataTables a convenient way to pass multiple rows of data around, but I could have stopped when I reached the first conflict.
FindOverlaps delegates the population of the
SPQuery object to the
populateQuery method. It then calls
AddQueryResultsToTable, which retrieves the current list values based on the
SPQuery object and adds them to the conflict list. If the conflict table contains any entries,
formatErrorTable to format the
ErrorMessage and sets the
cancel property to
populateQuery creates a Collaborative Application Markup Language (CAML) query as shown below:
To include recurring events, the query “
where” clause was restricted to
DateRangesOverlap which takes a single date and a value of week or month. The results returned are then filtered to isolate the truly conflicting entries. If the start date and end date fall on the same day, the entries for the week are retrieved. If the start date/end date spanned days within the same month, then a month of entries are retrieved. If the span is larger, then multiple passes are made through the date range, with each query serving a single month.
AddQueryResultsToTable (shown below) performs a query on the current list using the prepared query. It traverses the returned items to determine which items overlap the current event’s start date and end date. Overlapping entries are added to the conflict table.
aSPListItemCollection calendarItems = theList.GetItems(query);
foreach (SPListItem item in calendarItems)
if (item.ID != ItemID)
DateTime eventStart =
DateTime eventEnd =
if (!((startDate >= eventEnd) || (endDate <= eventStart)))
if (overlapList.Rows.Count > MAX_DUPS)
DataRow theRow = overlapList.NewRow();
theRow["Title"] = item.Title;
theRow["Start"] = eventStart;
theRow["End"] = eventEnd;
string modBy = item[item.Fields.GetFieldByInternalName
if (modBy.IndexOf("#") > 0)
modBy = modBy.Substring(modBy.IndexOf("#") + 1);
theRow["Modified By"] = modBy;
catch (Exception ex)
errorMsg += ex.Message;
formatErrorTable is a utility method that converts the conflict table to a
string to be assigned to the
System.Text.StringBuilder sBuilder = new System.Text.StringBuilder();
sBuilder.Append("The following reservation");
if (overlapList.Rows.Count == 1)
sBuilder.Append(" with the desired reservation.
foreach (DataRow row in overlapList.Rows)
sBuilder.Append("Title: " + row["Title"].ToString() + "\t from " +
row["Start"].ToString() + " to " + row["End"].ToString() +
" by " + row["Modified By"].ToString() + "
Points of Interest
The most difficult aspect required recreating the recurring appointment behavior. When a recurring appointment is saved, a query must be performed on the series of appointments that would result. When a recurring appointment is selected, the properties contain recurrence data in the form of an Extensible Markup Language (XML) scrap in
AfterProperties["RecurrenceData"]. The example below indicates that the appointment occurs daily for five days.
<daily dayFrequency=”1” />
There are two aspects to the behavior: repeat frequency and stopping behavior. Repeat frequency includes: daily, weekly, monthly, and annually. Monthly and annual frequencies can be based on a fixed day or a relative day. An example of a fixed day would be the second of the month or October second of each year. A relative day may be the first Monday of the month or the first Monday in October of each year. The stopping behavior can include: a set number of occurrences, a cut-off date, or repeat forever. In the example above, the appointment repeats five times. I set an end point in the repeat forever case of five years from today. I thought that would be far enough in the future to resolve conflicts by hand. When a specific cut-off date is provided for a recurring appointment, SharePoint adjusts the appointment end date to match the cut-off date. When this occurs, the end date is adjusted to match the date portion of the start date. To simplify the interface, I created a
Recurring class, and the constructor takes the start date, end date, and an XML fragment as parameters. It parses the XML, and creates handlers for the repeat frequency and stopping behavior in a classic Factory pattern, as shown below:
The constructor code is shown below:
XmlNode firstDayOfWeekNode = null;
XmlNode repeatNode = null;
XmlNode recurrNode = null;
ParseXML(RecurringXML, ref firstDayOfWeekNode, ref repeatNode,
XmlNode typeRepeatNode = repeatNode.FirstChild;
string typeRepeat = typeRepeatNode.Name;
oRecurPeriod = new DailyRecurPeriod(ref StartDate, ref EndDate,
oRecurPeriod = new WeeklyRecurPeriod(ref StartDate, ref EndDate,
oRecurPeriod = new MonthlyRecurPeriod(ref StartDate, ref EndDate,
oRecurPeriod = new MonthlyByDayRecurPeriod(ref StartDate,
ref EndDate, typeRepeatNode);
oRecurPeriod = new YearlyRecurPeriod(ref StartDate, ref EndDate,
oRecurPeriod = new YearlyByDayRecurPeriod(ref StartDate,
ref EndDate, typeRepeatNode);
string recurName = recurrNode.Name;
oCompletion = new RecurCompletionCount(ref StartDate,
ref EndDate, recurrNode);
oCompletion = new RecurForever(ref StartDate, ref EndDate,
oCompletion = new RecurCutOff(ref StartDate, ref EndDate,
The user interface allows the creation of an appointment before the recurring rules would allow it to exist. For example, if the entered start date is 10/15/2008 and the pattern is the first Monday of the month, the first start date is adjusted to 11/3/2008. So, this behavior is mimicked by the frequency handler, where appropriate.
The recurring object delegates the calculation of the next date to the handlers in
Recurring.GetNext(), as shown below:
bool returnValue = oRecurPeriod.GetNext(ref StartDate, ref EndDate);
returnValue = oCompletion.ProcessedAll(StartDate, EndDate);
In conclusion, I used a simple approach to a common problem that was based on existing standards. Since standards were followed, the resulting tool can be extended with typical SharePoint tools. Training requirements were reduced to a message about how to handle collisions since the user interface was unchanged.
- November 2008
- December 2008
- Catalin discovered two bugs in this project related to recurring appointments that I have incorporated and fixed in the downloads
- March 2009
- Corrected the issue with editing the page in Sharepoint Designer
- Added a filter for invalid recurring XML
- Due to popular demand, added the ability to add a column to allow multiple Resources to be tracked in the same list. After creating the list, go into Settings -> List Settings and Create Column. The column name must be "Resource" unless you change the code (
RESOURCE_COLUMN const defines the column name). The new column is defined as "lookup another list on this site". After selecting the list (a custom list of resources), select the display name or title for "In this column". For some reason, after adding the resource to the
CAML query, the query ignored the resource specified with
<Eq> and returned all rows for the date range. As a result, the returned list is filtered manually. I would love to know why if anyone has any ideas.