Monday, December 16, 2013

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:

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

SharePoint Eric's Holiday List of Unlikely Christmas Movies

For the last few years, I have been gathering a list of unlikely Christmas movies.  It all came about because around this time of the year, my family watches movies to get us into the holiday season, and frankly some of the more "standard" Christmas movies are a bit played out.  Sure, there are some of my personal favorites like 'A Christmas Story', 'National Lampoon's Christmas Vacation', 'Elf' and 'Bad Santa', but I wanted to find a list of movies that you wouldn't necessarily think of as "Christmas Movies", but would pleasantly surprised when you watched it.  The only real criteria to this list is it couldn't have a holiday related title and it must take place around Christmas time.

So without further adieu, here is my top 20 unlikely Christmas movies (in no particular order).

Happy Holidays!

  1. Gremlins
  2. Fast Times at Ridgemont High
  3. Die Hard
  4. Trading Places
  5. Edward Scissorhands
  6. Hook
  7. Screwed
  8. Iron Man 3
  9. Batman Returns
  10. The Shining
  11. Monty Python's Life of Brian
  12. Lethal Weapon
  13. Child's Play
  14. Planes, Trains and Automobiles
  15. Kiss Kiss Bang Bang
  16. Rocky IV
  17. The Ref
  18. Long Kiss Goodnight
  19. Ghostbusters II
  20. Home Alone

So if you're like me and looking for non-traditional holiday movies, go ahead and add these to your watch list this holiday season.

If you know of any I should add to my list, please feel free to mention it in the comments section below.

Thursday, December 12, 2013

UAG Connection Errors When Using Internet Explorer 11

Ok, this isnt exactly a SharePoint issue, but it was something that came up while supporting an external SharePoint collaboration site.  I was contacted by my customer recently because they were having issues connecting to a public-facing web site.  Previously they were able to open the page and log in, but after upgrading to IE11, they started receiving the following error.  This error is only present when using IE11, not in Chrome or Firefox:

We were able to narrow the problem down to the UAG (Unified Access Gateway) and there has been an update (SP4) that supposedly addresses this issue:

Until the UAG is updated, here is a work-around:

1) Launch Internet Explorer and go to tools menu and compatibility view settings.  Add the site to compatibility view

2) Hit the F12 key to open the Developer tools on the bottom of the screen

3) Change the user agent option to Internet Explorer 9 or 10

Now you should be able to browse back to the site and login with your credentials.

Wednesday, November 20, 2013

A Few Good SharePoint 2010 Tips & Tricks

Frequently I come across some neat tips and tricks in SharePoint that help either support my end user base or help with administration.  I wanted to post a few of them here, not only for my own recollection but also to hopefully help someone out in the Inter-Webs.

The first is some text you can add to the end of your site URL that takes you to a hidden Web Parts Maintenance Page.  This is a great trick if you are having issues with a web part on a page, or if an end-user has tried to insert multiple copies of the same web part.  This will also show you if anyone has chosen to "hide" the web part from the page (which might be causing a problem) instead of deleting it.

Simply add "?contents=1" to the end of the URL of the page, for example: 

My next trick came about when someone in leadership asked me to provide a list of all the site collections.  We have hundreds spread across multiple content databases.  Powershell is one of those things that's incredibly useful for all levels of SharePoint Administrators.  In the following commandlet, you will get a list of all the site collections for a particular content database:

Get-SPSite -Limit All  -ContentDatabase contentdbname | select url, @{label="Size";Expression={$}} 

Along the same lines is a good commandlet to run if you ever want to export all sites within a web application.  Again, we have thousands of sites across hundreds of site collections and many content databases, so this gives you a nice spreadsheet of all the sites:

Get-SPWebApplication | Get-SPSite -Limit All | Get-SPWeb -Limit All | Select Title, URL, ID, ParentWebID | Export-CSV C:\localhost.csv -NoTypeInformation

The final trick I'll post today is a way to see an entire list of all users that have ever logged into your SharePoint web application.  It is a hidden User Information List that comes in handy if you ever have any discrepancies with a user not displaying a proper name or if you simply want to remove them from the entire site collection.

Simply add "/_catalogs/users/simple.aspx" to the end of the root site URL.  This will display the User Information list, for example:

I hope these help you in your SharePoint Adventures.  I will post more of these as I come across them.

Thursday, October 17, 2013

Site Owner Unable to Edit a Page in SharePoint Designer Due to an Inherited Master Page

Ok SharePointers.. I'm wring this blog post to recap a tricky work-around I came up with as result of a Microsoft "feature" in SharePoint 2010.  Here are the details, the problem and finally the solution I came up with.  perhaps this "out-of-the-box" kind of thinking may help you if you encounter the same situation.

The Set Up: Currently on my contract, we have several farms, one of which is an internal portal that is used for employee only collaboration.  It requires an Active Directory account and the layout of the environment consists of a main Site Collection for each bureau and sub sites created for each different department below that as they are requested.

The Problem: A site owner contacted me unable to edit a site page using SharePoint Designer.  he sent me the following screenshots (names have been blurred to protect the innocent):

What this error tells us is that the user does not have permissions to the custom master page that we are using on the site collection.  Since it is a sub site that inherits it's master page from the top level site collection, we are having a permissions issue.

The Details: As you will read in this Microsoft support article:
Overall, a user needs to be a member of one of the following groups at the site collection level to be able to use SharePoint Designer and modify SharePoint content:•Site Collection Administrators•Designers•Owners

Since I cannot give this site owner site collection admin, owner or designer permissions to my top-level master page library, and I was unable to get the site to recognize the sub site's master page library in the master page settings, the user was getting the error above.  Simply creating a new site collection for the user and migrating the data was too much effort, so I had to be a bit tricky with how to make this work.  

The Solution: I created a new folder in the top-level master page library and broke inheritance to the folder, giving him designer permissions.  I took a copy of the master page and placed it in that folder, then set the site (and all sub-sites) to inherit this new master page.  He now had the necessary permissions to open site page and edit the content without the master page error, and I was able to maintain security by not allowing him elevated access to the real master page library.

I'm not sure if this is the best way, but this fix took all of 5 minutes to do and the user had all the functionality he needed and was back in business without any impact to the end-user look & feel (or needing to migrate content from one site to a new site collection).  If anyone has any other suggestions or perhaps a better way of being able to get the sub site to recognize it's own master page library (using the "_catalogs/masterpage/Forms/AllItems.aspx" string), please feel free to post a comment in the section below.


Wednesday, October 9, 2013

Launch a Hyperlink from the Quick Launch Bar or Top Navigation Bar into a new Tab or Window

How many times have you wanted to open a link to a list, library or site from the Quick Launch bar into a new tab, but when you right mouse-click on the link, those options aren't available?  If you're a casual SharePoint user, this is the menu you will get:

My personal work-around to this frustrating function was to duplicate the tab in the browser and then click the link normally.  It didn't take too much time to do this, but it was inconvenient.  I just couldn't understand why Microsoft wouldn't allow me to open a SharePoint link from the navigation to a new tab/new window. But I recently discovered a way to make that menu appear.  It takes some precision clicking, but once you get the hang of it, it will save you time and effort.  Plus you'll have a nifty new trick to show your colleges in the office.  Imagine how cool you will be!

First, you have to find out where the edge of the clickable area ends.  On the Quick Launch bar, it is typically right at the edge of the cell.  On the top navigation, you should be able to see the separator between the navigation items as shown in the image below:

If you hover over the very edge of these, the right mouse-click menu will change to the one we are all familiar with, the one with the option to open in a new tab, open in a new window, save target as, etc:

Now that you have the trick, go perfect your technique.  Go ahead, give it a shot.

Thursday, July 25, 2013

Manually Updating Passwords in SharePoint

I had the pleasure recently of being tasked with updating application pool and other service accounts in a mixed version, multiple SharePoint farm SharePoint environment.  Since this is routinely performed annually, I was disappointed to learn that there was very little documentation and truly no telling exactly where each of these accounts may have been used throughout the farms.  Since these environments were in production, it was very important that we took our time and tested functionality each step of the way.  Previously we had an issue where we issued a command to force a single password change through SharePoint, but it automatically changed every single farm password causing a mismatch between what SharePoint had as a password verses what it was actually set as in Active Directory.  This was a disaster!  We had to manually update the passwords in SharePoint using powershell.  Needless to say, we disabled all managed accounts from automatically changing the password.

So we started with the obvious, managed accounts.  Each account had to be simultaneously changed in Active Directory and then updated in SharePoint.  Once we completed the the managed accounts, we restarted the services to make sure they were working properly, and checked each server to make sure they replicated properly.  Below I've listed a few lessons learned and perhaps even some ideas for proactive approaches that you can take now to make the next password update process much more pleasant with a proven and predictable outcome. 

These are in no particular order:

  1. Make sure the timer service is started on all servers in the farm.  This will insure that the passwords are replicated from the managed accounts section in Central Administration to the services/applications across the farm.
  2. If possible, do your password changes off hours.  Although hearing user complaints about something not working right would be an excellent indication that a change was not applied properly, it's best practice to reduce the impact as much as possible on your user population.
  3. Take one step at a time.  Change the password in Active Directory, then change it in SharePoint, then test completly.  Only once you are positive the change was successful should you move to the next.
  4. If you do not already have a SharePoint list of all the service accounts you have in your farm and where they are used, this is the perfect time to start one. It will save a ton of research time before your next password change and allow for the process to be shared by multiple teams.  I use a custom list with columns such as: service account name; account type; app pool or application service, details, last password change, etc.  I also recommend using a program like KeyPass or PasswordSafe to keep track of current account passwords and pass phrases.
  5. Sometimes you have to manually change the password on the application pool itself in IIS Manager. Also be sure to update secure store service IDs.  This is where your list will pay off big time because once you go through this process once, you will know exactly where those accounts are being used so you can update all instances.
Having written proceedure and clearly documented details not only saves you time and effort the next time you need to manually change passwords, but it's also artifacts that can be passed down to newer team members as staff or responsibility changes.

Wednesday, July 17, 2013

KB2844286 Security Update on SharePoint 2010 - Causing List View Web Parts to Break

I received a call this week from an end-user reporting that a web part on his site was throwing the following error.  

Here is the text version of the error he was getting:
Unable to display this Web Part. To troubleshoot the problem, open this Web page in a Microsoft SharePoint Foundation-compatible HTML editor such as Microsoft SharePoint Designer. If the problem persists, contact your Web server administrator.

I logged into his site, but it initially rendered just fine for me so I figured it was on his end, but the next day, I too received the error.  So I looked into the ULS Logs on the WFEs to see if I could identify the problem.  As with most cases, the details were limited but there was one thing that stood out that I was able to search on:

Error while executing web part: System.NullReferenceException: Object reference not set to an instance of an object.

After a quick search, I was able to track this down to a Microsoft Security Update that was applied to our web front end servers over the course of the week.  We have two load-balanced Web Front End servers in our farm and it turns out that both did indeed receive the automatic update.  One server updated on the 13th of July (this was the server that the end-user was hitting) and the other server was updated on the 17th of July (which is the one that I was hitting and the reason why, a day later, I was getting the error).

At this point, I needed to check the OS to find the exact KB update that was associated with the issue.

Windows XP and Windows Server 2003:  KB2844285
Windows 7 Service Pack 1 and Windows Server 2008 R2 Service Pack: KB2844286 
Windows Vista Service Pack 2 and Windows Server 2008 Service Pack 2:  KB2844287

Here is the Microsoft Security Bulletin for July 2013:
Microsoft Security Bulletin MS13-052

As a temporary workaround, we found uninstalling the update (KB2844286) from the servers (all WFE’s and App servers), rebooting, and then performing an IISReset on the servers will resolve the issue. Several others have experienced this issue as I found here: and here:

At this point, this is the best information I was able to find.

Also according to other blog/forum posts I've read, it appears this error "typically" appears when a list or web part view has had the XSLT customized.

Monday, June 10, 2013

Post SharePoint Saturday Recap (SPSDC)

So this past weekend was SharePoint Saturday DC at the Microsoft facility in Chevy Chase Maryland (close enough to DC).  My company ( was a sponsor so we had a booth with Pint Glasses as a give-away.  I was also tasked by my boss to take some photos from the event.  Although I didnt attend as many sessions as I would have liked (between manning the booth and walking around with the camera), it was a fun time and I encourage anyone who is interested in SharePoint to attend as many of these free events as possible.  It's a great opportunity to meet people you follow on twitter, or just talk about how your companies can help provide services to others.  I find its a great way to spend some time with team members that you dont normally get to work with.  I always walk out of these events with more knowledge than I had walking in, whether that's learning new technology or just simply learning about a product/business that I didnt know existed.  It's all about the networking, baby!
So here are the top 10 things I got from Saturday's event (in no particular order):
  1. Pistachios are NOT just tasty snacks
  2. Parking is much easier when someone else is driving
  3. If you bring a pint, they will fill it - SharePint can happen anywhere, at any time
  4. Always prepare questions in advance for the "Ask a Microsoft Expert" Q&A session - use it as an opportunity to stump them and watch them squirm, dodge, and ask to follow up with you after the event
  5. Always grab extra SharePint tickets because they will come in handy later
  6. Even the most annoying/uninteresting SharePoint conversations are better with beer
  7. You can customize SharePoint Search in 2013 to really screw with people.  For example, when someone searches on a commonly used term, have it return the results of something oddly fascinating but not at all relevant - then let the fun ensue
  8. If you get 10 people from your company to show up to a free event on a Saturday, you're gonna have a blast, especially if at least one of them is from out of town
  9. Always pack breath mints and a spray can of deodorant, just in case someone in your group is a little funky
  10. Visit vendor booths early to get the best swag
Thanks again for everyone who came, saw and conquered the lastest installation of SharePoint Saturday DC.  The next event in this area is scheduled for Saturday December 7, 2013 in Reston Town Center.  You can sign up for updates/register here: