Marc D Anderson's Blog

Business Needs

The International School of Beijing needed a way for Elementary School parents to book time slots with teachers during bi-annual parent-teacher conferences.

The requirements:

• Easy to use (training is impossible).
• Integrate into the current intranet portal.
• Sync with the database to omit any kind of manual setup for secretaries.
• Deny parents the ability to book more than one slot with the same teacher.
• Allow parents to book the same slot for two different teachers (in case mother and father come in and meet with 1 teacher each to save time)
• If two or more parents are online at the same time, correctly queue the submissions so a double booking does not occur.
• Allow teachers to pre-block out slots where parents cannot book times.
• Allow teachers the ability to see their entire schedule, including which student is coming in during which time slot.

Solution (SharePoint, jQuery, and SPServices)

A SharePoint solution was built with jQuery to meet the requirements of the Elementary School. Let’s break down how the solution was created:

Part 1 – SharePoint

Within SharePoint three lists were created:

• Bookings (Each reservation had its own row in this list that stored the details of who booked it, for which teacher, etc.)
• Time Slots (A pre-determined list of time slots…in this case a list item was created for each 20 minute time slot from 12-6pm on Thursday and Friday Oct 18 and 19).
• People (An external content type that pulled records from the database for each student, each of their parents, and each of their teacher names)

One of the trickier parts of this solution was grabbing the external content from the database in a usable way. Once it was in, we were able to work with the data. However, this list has 10,000+ items, so dealing with larger data like this was quite challenging. Enter jQuery.

Part 2 – jQuery and SPServices

The bulk of this solution was created with the jQuery and jQuery SPServices Libraries. Using jQuery, we stepped through the process like this:

1. Get the details of the logged in user (parent) using SPServices
2. Hit the SharePoint external data list once, filter it by Parent (matching to the Parent login ID), and store it in a javascript object for later use. This was the biggest strain on the system. We minimized the data call to only one time, but even still, pulling a list of 10,000 records even once isn’t super quick.
3. Get all the children of the logged in parent, push them to a drop-down menu
   Child 1 Child 2
4. Get all the teachers of the selected child from #3, push them to another drop-down menu
   Teacher 1 Teacher 2 Teacher 3
5. Get all time slots from the Time Slots list, push to a third drop-down menu
   (Thu. Oct 18) 12:00pm – 12:20pm (Thu. Oct 18) 12:20pm – 12:40pm (Thu. Oct 18) 12:40pm – 1:00pm
6. Retroactively disable all time slot options that have already been booked (by searching through the Bookings list for records that match the time slot and the selected teacher)
   (Thu. Oct 18) 12:00pm – 12:20pm (Thu. Oct 18) 12:20pm – 12:40pm (Thu. Oct 18) 12:40pm – 1:00pm

Once the data was present, the parent could interact with it in two ways, book, and delete.

To book a time slot, the selected child, selected teacher, and selected time slot (along with a comments area) was submitted and saved to the list. To delete a time slot, a delete button is appended to each reservation in the on-screen schedule with the ID of the reservation as the ID of the link element.

The data from the Bookings list is checked once during the time slot selection process, and again during the form submit process to make sure no double bookings have occurred.

Benefits

This solution enables, for the first time at this school, parents to go online and manage their entire Parent-Teacher Conference schedule. Also, as an added bonus, if a mother and father both login, they can see the complete schedule for their child, even if only one of the parents made the bookings.

Once a parent books a time slot, they receive an email containing the details of their reservation.

An additional feature of this solution was that it was duplicated and tweaked for teachers to use a similar interface to pre-block out time slots where they did not want parents to come (ie, lunch, coffee break, going home). In that scenario the “one-block” restriction was removed for teachers, allowing them to customize when parents would be given the option to come in.