Problem Statement:
The customer had created a shared calendar to manage the availability of a number of physical resources – mainly rooms and the equipment that each room contained. Those rooms were available to a number of colleagues across a number of disparate sites who required up to date information on room availability and possessed the IT skills to enable them to use either the relevant shared SharePoint calendar or Microsoft Office Outlook 2007 linked to a specific calendar.
All was going well, until duplicate bookings started appearing within the system or a user would reserve the room from 09:00 to 18:00 even though there was an existing booking from 12:00 to 14:00. Thanks to the auditing trail recorded by SharePoint it was easy to see who had created the duplicate and hence which the original was.
One option was to manually approve all calendar entries or amendments, but resources did not allow for this. The preferred method was for the system to automatically detect and remove potential clashes, informing the creator of the duplicate event of the clash and asking them to book another available slot.
Key information was that the service was provided by a central SharePoint service provider who would not permit the installation of custom code or other fundamental amendments which may affect the operation of the service for other users. Hence, any changes needed to be made at the particular site collection or calendar level using tools already available to us.
Initial Analysis:
Given that custom programming was out of the question, the easiest option available was to achieve the required result using Microsoft Office SharePoint Designer to create a bespoke workflow. It was also apparent that the system needed to cope with overlapping as well as duplicate entries to prevent anyone adding another appointment that either began or ended at the same time as an existing appointment.
The workflow therefore had to check for three conditions:
- Condition One: the start time of the new event occurs between the start time and end time of an existing appointment;
- Condition Two: the end time of the new event occurs between the start time and end time of an existing event;
- Condition Three: the start time of the new even occurs before the start time of an existing event and the end time of the new event occurs after the end point of an existing event;
First Attempt:
The first attempt at this was simply to test the above three conditions for any new appointment created or for any changed appointments. However, upon testing it was clear that the calendar would only allow the creation of a single event and would trigger the overlapping appointments workflow for any subsequent events.
It also became clear that creating an event could also cause the workflow to fire and in essence the new event was conflicting with itself.
Solution:
The solution was to introduce another variable which was not created when the event was created. I chose to duplicate the start and end times, only populating the duplicate values once the workflow had run. This prevents the event from conflicting with itself.
First, text the start time of the new event to see whether it falls between the duplicated start and end times of existing appointments. If it does, update the list to delete the new event and trigger an email to the site administrator and the user who created the duplicate event with useful information about why it’s been deleted.
Next, test the end time of the new event, again to see whether it falls between the duplicated start and end times of an existing appointment. If it does, delete it and generate an email.
Finally, check to see whether the start time of the new event is before the duplicate start time of any existing events and the end time of the new event after any of the duplicated end times of any existing events. If it is, then delete and generate email.
If all of these conditions are fine, then update the new event – populating the duplicate fields with the relevant start/end time. If you have existing appointments in your calendar, then get in touch and we can go through how to use Microsoft SharePoint Designer 2007 or Microsoft Access to tidy this up.
Screen shot of the workflow below. Let me know if you have any questions or if you’d like support with anything in particular. I’m currently constructing this site to support my consultancy work, so it’s very much a work in progress, but feel free to share your own tips and suggestions.
Videos:
We have recorded a number of videos to guide you through the process.
Part One – Creating a calendar and required columns
Part Two – Enable version control and approval
Part Three – Create workflow to check start time
Part Four – Create workflow to check end time
Part Five – Create workflow to check for overlapping appointments
Part Six – Create workflow to approve the appointment and update variables
Limitations of the Method:
The above workflow will detect where an all day appointment is being created over the top of an existing appointment for that date and for overlapping start/end times for a particular day. However, if you have an existing appointment on a particular day and create a new appointment that begins the day before and end the day after that existing appointment, then it does not appear to pick this up.
The above method is currently being refined to better differentiate between new and amended appointments.
This is currently the next stage of my investigations.
