Using InfoPath 2010 and Cascading Drop-down boxes to Create a Conference Room Scheduling System

In late 2012, a customer asked me to create a custom conference room scheduling system.  The out-of-the-box template included in SharePoint 2010 did not fully meet their requirements.  They wanted the system to collect room requests while allowing the end-user the ability to choose the appropriate room based on a certain criteria such as location, room capacity and available features and services.  This was fairly straight forward and a great use-case for SharePoint 2010 using InfoPath list forms.  I also implemented some SharePoint Designer 2010 workflows to create custom email notifications and to create/assign a calendar item for each new room request.

The first thing I did was collect the requirements. They were as follows:

1. Create a reservation-based system for 13 unique conference rooms
2. The system needs to allow an administrative group to approve each conference room request
3. The requester should be able to select available rooms based on:
1. Room Seating Configuration
2. Maximum Seating Capacity
4. The requester should also be able to request additional equipment, furnishings, security and services as well as catering (if available to the selected room)
5. The system should automatically email individuals and groups depending on selections

The next thing I did was get a list of all the conference rooms and their maximum capacity per seating configuration.  In several cases, a single conference room could have several seating configurations and each of those configurations had different maximum capacities.  For this, I needed to create a Master Conference Room list as a single reference point.  This list contained the following columns which will be used later in the form design phase: 

• Name - this was for the common name of the conference room
• Maximum Room Capacity - this was for the absolute maximum capacity the room could hold regardless of seating configuration
• Seating Configuration Type - this was for the type of seating configuration
• Maximum Configuration Capacity - this was for the maximum capacity for a selected configuration
• Food and Beverage - this was a Yes/No field used to indicate whether a particular conference room allowed food or beverages
• Status - This was a 2 choice field which allowed the admin to make a certain room or configuration active or inactive depending on if it was available, etc
• Video Teleconferencing - this was also a Yes/No field to indicate whether video conferencing was available in that particular conference room

I also needed to create a few supplementary lists to populate additional choice fields:

• AV Equipment - this list contained all available Audio/Visual equipment
• AV Services - this list contained all available Audio/Visual services
• Food and Beverage - this list contained all Food/beverage services
• Furnishings - this list contained additional room furnishings

Once all these supporting lists were created, I was able to connect them as data sources in my InfoPath form.  In this particular application, I needed to reference the conference room list several times, so for each call to the list, I created a new connection:

I started building the system with a generic task list and then modified the list using InfoPath 2010:

I started with collecting the basic information.  These fields included Full Name, Phone Number, Email, Organization, Meeting Name, Start Date/Time and End Date/Time:

I added choices manually to the "Organization" Drop-Down List Box from within InfoPath, instead of setting up a lookup list for that column.  

I also created an InfoPath rule to automatically set the End Time/Date to 1 hour later once the Start Date/Time was entered:

At this point, is where some of the cool stuff starts happening.  I designed a simple call-out bar that gives the end-user some quick steps on how to fill out the rest of the form. 

Step 1: Select the Room Setup Configuration:

For this step, I have created a new external data source to the Master Conference Room list that I created to contain all of the conference rooms available along with the specific details for each room (ConferenceRooms_Setup).  In this data connection, I only selected the columns that For the value of this particular drop-down, I only want to show the Setup Type column from that list and only display unique display names (since there were multiple entries for each conference room based on its unique configurations).  I also created a filter on the external data connection to ONLY display entries of that list where the "Status" column (from the external conference room list) is equal to "active" so the rooms that were not available (or not yet ready for reservations  would not be included. 

The last thing I did for this particular field is, I created a few rules which will come into play later.  These rules basically blank out the capacity, conference room and food/bev fields so we are not left with artifacts in those fields as this selection changes.  This will make more sense later as I start to show how I displayed/hid sections of the form based on selections made by the end-user.

Step 2: Choose Configuration Capacity:

For this step, I have again created a new external data source to the Master Conference Room list (ConferenceRooms_Capacity).  For the value of this particular drop-down, I only want to show the Maximum Capacity for the Configuration Type they selected in Step 1.  This is where the fun begins!  When selecting the Entries, I filtered the data so that the MaxCapacity field only displayed the entries equal to the MaxCapacity associated with the SetupType selected in Step 1.  The image below shows almost all of the dialogue boxes needed to configure this but I'm sure it looks quite confusing:

This ultimately resulted in only displaying in the drop-down box ONLY the capacities which matched the configuration type selected in step 1.  I also had to create rules similar to what I did in Step 1 so that it blanked out any associated fields so that as the selection changes, we didnt leave incorrect artifacts in those fields.

Step 3: Select Available Conference Room

For this step, I once again created a new external data source to the Master Conference Room list (ConferenceRooms_Rooms).  For the value of this particular drop-down, I only want to show the Conference Rooms for the Maximum Capacity they selected in Step 2.  This one is even more complex than the last.  For this field, I only wanted to display the name of the conference room that met the first two criteria.  This required filtering the drop-down so that it displayed ONLY those items where the Maximum Capacity was greater than or equal to the Capacity selected in Step 2 AND the setup is equal to the configuration selected in Step 1:

This results in only being able to select those conference rooms that met the above criteria.  I also created rules on this field that blanked out additional selections as well as populating data based on this final selection.

If a user wants to go back and re-select any of the 3 fields, the rules are in place to blank out the rest of the options so there are no incorrect artifacts, or they can continue with their original selection.

But the fun doesn't stop there!  Yes.. there's more.....

Now that the conference room is selected, we also have the requirement to be able to also request equipment, services, etc.  One of the most important selection is whether the conference room we selected allows food/bev.  Based on the selection made in the Conference Room drop-down, I either need to allow or disable the food selection.  For that choice, I have set up a rule that looks back to the external conference room list and if the conference room they selected in Step 3 has its FoodBev column marked false, then disable this field:

There also some other cool things I did with hidden sections.  For instance if an Audio Conference was selected in the AV Services field, additional fields opened up that allowed them to populate the bridge and passcode.  Also, Video Conferencing was only available in 3 of the conference rooms, so that field was disabled unless the appropriate conference room was selected in Step 3.  Lastly, if Food and Bev was allowed in the conference room they selected, additional fields would open up, including the Food and beverages section, the name of the In-house caterer POC, and the Alcohol Waiver which could be opened, printed, signed, scanned and attached back to the request:

The last part of the form contained a few more services with hidden options and a section to approve/reject the request.  Each section shown in the image below would be either displayed or hidden based on other choices.  Again, all of these choices needed to be manually cleared out if any of the selections above changed, so that's where those rules came into play that I talked about earlier:

With the form working the way I wanted it, having accomplished almost all of the requirements, the only thing left was creating a custom workflows using SharePoint Designer to create the email notification and the calendar event (if approved).  These are pretty straight forward so i wont go into detail here about how they were created.

Cascading drop-down fields is a very useful tool with InfoPath 2010.  In this post, I have shown how to can take a selection, filter the results into another drop-down, and then make an additional selection to narrow the results down even further.  This completely accomplished the goal and provided my customer with a very robust conference room scheduling system.

PS: Here are a couple good references to creating cascading drop-down fields if you want to look more into it.  These are more specific to just creating cascading drop-downs, so the description and the example is probably easier to understand:

https://www.nothingbutsharepoint.com/sites/eusp/Pages/infopath-cascading-drop-down-screencast.aspx

http://blogs.msdn.com/b/bharatgupta/archive/2013/03/07/create-cascading-dropdown-in-browser-enabled-infopath-form-using-infopath-2010.aspx

If you have any questions or need additional clarification, explanation  feel free to post your comments in the section below.  Enjoy!