Resume Tracking with InfoPath, Excel, and SharePoint

Most organizations that I work with have few if any ways to automate repeatable processes. In most cases, organizations are using a combination of Microsoft Office documents and e-mail to create informal processes that require users to attach documents for routing and manually track their progress. Using the Office System, however, developers can create automated solutions that remove many of the existing manual process steps. In this example, I will build a simple resume tracking system that automates the interview process.

The solution will utilize an InfoPath form to capture the metadata from a resume and make it easily available for reviewers. The resume itself will be attached to the form, which will subsequently be stored in a SharePoint form library. The InfoPath form will automate a simple phone screen and interview process that collects comments and sends e-mail notifications. An Excel spreadsheet will be used to report the status of all the candidates in the interview process. All of the source code for this article is avaible for download here.

Creating the InfoPath Form
The first part of the project involves creating the InfoPath form that will track the candidate throughout the process. Because I want the form to implement a workflow process, I created a new InfoPath Form Template project in Visual Studio 2005 using the InfoPath Toolkit. The InfoPath Toolkit allows me to design the InfoPath form and write managed code in Visual Studio to implement the workflow. Figure 1 shows the New project dialog in Visual Studio 2005 with the InfoPath Form Template project selected.

Figure 1. Creating a New InfoPath Project with Visual Studio

When you create a new InfoPath Form Template project, Visual Studio automatically opens InfoPath with a new form for you to design. Designing the user interface for the form is reasonably straightforward. I included text fields for the candidate's name, the position under consideration, and the status. I also made use of the File Attachment control so that a copy of the candidate's resume could be attached to the form.

Next, I added a section for creating an interview plan. This section includes options for requiring a phone screen, peer interview, and supervisor interview. The Human Resources department can select whether or not to include an activity in the process and assign a responsible individual. Finally, I included a repeating section for reviewers to record their comments. When you set up the form, take some care to properly name the fields so that they can be easily accessed later. Figure 2 shows the basic form.

Figure 2. The InfoPath Resume Form at Design-Time

As a candidate moves through the interview process, the status is updated by each reviewer. The form presents a button that can be used to send update e-mails when each activity is completed. Using managed code in Visual Studio, I implemented a simple e-mail function to update the reviewers. From the InfoPath form, you can right-click the button, select Properties from the context menu, and then push the Edit Form Code button. When the InfoPath Toolkit is installed, the action takes you back to Visual Studio where you can write managed code to respond to a button click. Listing 1 shows the complete code for the InfoPath form. Note that you should replace my information in the listing with the proper information for your e-mail system.

Because the managed code accesses the e-mail system, the security level of the InfoPath form must be increased. By default, InfoPath selects the most restrictive security setting for a form based on its capabilities. In this case, however, I had to override the default setting of "Domain" and set the form the "Full Trust". This is done in the Form Options dialog, which you can access by selecting Tools->Form Options.

When you specify that a form has Full Trust, you must also sign the form with a digital certificate. Furthermore, this certificate must be associated with a trusted root on the end user's machine. This process ensures that malicious forms do not have access to files and settings on the end user's machine.

Once the form is created, you can publish it to a SharePoint Form Library so that it is accessible to all of the team members. You can publish the form directly from Visual Studio 2005 by right clicking the project and selecting Publish Form from the context menu, which starts the publishing wizard. When you publish the form, be sure to promote the fields for the candidate's name, position, and status because you'll use them in the next section to create a report in Excel. Once the form is published, you should be able to fill out new forms, attach resumes, and set up an interview process. Figure 3 shows the SharePoint library with some sample forms.

Figure 3. A Library of Resume Forms in Sharepoint

Creating the Excel Sheet
Once you have several InfoPath forms in the library, you can create an Excel spreadsheet to track the status of the resumes. If you promoted the name, position, and status fields when you published the InfoPath form, you can create a list in Excel that will display the promoted columns by clicking the "Export to Spreadsheet" link associated with the Form Library. While the list is easy to create, the resulting report is read-only. If you want to manipulate the data in any way, you may have to take a different approach.

A second approach to tracking the status is to use Web services and managed code to query the form library. Using Visual Studio 2005 Tools for Office, I created a new Excel Template project. Within the new project, I wrote code in the Startup event for Sheet1 to retrieve the status information from the SharePoint library. This code utilizes a Web services call to the SharePoint Lists service, which returns the information from the promoted fields in the form library.

Calls to the SharePoint services typically return XML fragments in a format known as Collaborative Application Markup Language (CAML). Using the Lists service, you can return information about lists, items, and fields from any SharePoint list. Listing 2 shows the complete code for the spreadsheet reporting project, and Figure 4 shows how the data appears in Excel. Note that you should update the URL and credentials to reflect your environment.

Figure 4. The Resume Status Report in Excel

Solutions that automate business processes are becoming increasingly common, and the combination of SharePoint, InfoPath, and Visual Studio 2005 definitely streamlines the development process. In fact, the special relationships between these technologies make them an ideal platform for developing process-based solutions. A similar solution developed in ASP.NET, for example, would take considerably longer to create and deploy. That's why I think you'll see more and more solutions built on the Office System in the next few years.

Scot Hillier is an independent consultant focused on creating solutions for Information Workers with SharePoint, Office, and related .NET technologies. He is the author of 8 books on Microsoft technologies including 'Microsoft SharePoint: Building Office 2003 Solutions' and 'Advanced Windows SharePoint Services.' When not writing about technology, Scot can often be found presenting to audiences ranging from developers to C-level executives. For information on Scot's Microsoft books, visit www.sharepointstuff.com.

Submit article to: