Solve Common Business Problems with InfoPath and SharePoint

Design and create data-collection forms

Microsoft Office InfoPath 2007 is one of the lesser-known tools in the Microsoft Office suite of applications. Compared with Word and Excel, it has a much smaller user base and an even smaller number of people who actually know what to do with it. In this article I explain what InfoPath can offer, focusing on how to use it with Microsoft Office SharePoint Server (MOSS) 2007. I use a common real-world expense report example to illustrate InfoPath’s benefits. SharePoint’s recent popularity makes InfoPath a useful tool that your organization should investigate and evaluate.

Related: SharePoint and InfoParth: A Powerful Duo

InfoPath Basics

InfoPath is essentially a tool for designing and creating forms. The application allows nontechnical users to build and deliver methods to collect and manage data. Although a common perception is that you can accomplish the same tasks with Word or Excel, InfoPath provides greater functionality. In addition, you can easily convert Word and Excel files to more robust InfoPath data-collection forms.

InfoPath is really just a package of associated files. At its heart is an XML file that represents the data source for your collected data inside the forms. This flexible format is extremely useful for additional applications to read and process the form data. The designer or front-end view is simply XSL, with some additional files to manage rules, data connections, and so forth. If you rename your InfoPath template with the .XSN extension to a .CAB file, you can extract and view the individual components as text files, and you can easily see how they are connected.

InfoPath has built-in capabilities to connect with Microsoft SQL Server, Access, SharePoint, and Web Services to read and write data to a significant number of additional applications and data stores. These features make InfoPath an excellent option for building small applications that connect to multiple systems at once to select and update data. In addition, InfoPath can then collect and send data in human-readable formats via e-mail or to SharePoint. Most of these tasks can be accomplished with no compiled coding.

Two significant features of standard InfoPath development are the rich rules and validation components that users can build without code. The application lets the form designer view and manage common interface controls. The underlying data source can be viewed and manipulated with intuitive XPath functions abstracted away from the designer. For example, you can have a number of rules on a control; these rules check the contents or any other controls, process calculations, or immediately let you know which rules passed or failed. Rules can be strung together to cover some fairly sophisticated data validation and specific display control management.

You can save sections of forms as templates for reuse across multiple forms. This approach eliminates cutting and pasting and gives organizations the option to build components with specific functionality or required schema items to share with form designers.

SharePoint Integration

InfoPath connects natively to SharePoint in multiple ways. It can read data from SharePoint lists quickly and easily, query live SharePoint data, and return results to the form to process a variety of options for the designer or end user. Connection options include binding data to drop-down lists for selection, obtaining user profile information, and querying sources of configuration data for rules, validation, and much more. InfoPath forms can be stored locally in SharePoint document libraries in the same way as any other type of document. They can be made the default template for a given content type, allowing the New command on a list to automatically create an instance of these custom forms to open, fill out, and save locally in the library for business processing.

Forms Services

Some of the real power when using InfoPath with SharePoint lies in the use of InfoPath Forms Services, which is an enterprise feature of SharePoint that dynamically translates an InfoPath form to a web page via specific server technologies. Consider my previous example, in which an InfoPath form is used as the template for a content type. Web-enabling the form lets you build and publish forms directly to SharePoint and use them to start collecting XML data immediately, without requiring any additional client software beyond a web browser.

Forms Services is context-aware from a SharePoint perspective. It knows who is logged on, giving you additional flexibility in managing permissions and security for data access. When querying and using SharePoint data, you get the built-in security trimming to ensure that only appropriate access is given for each form instance.

Significant options are available for designing forms and collecting data. InfoPath is designed to send chosen fields to SharePoint fields as metadata, using out-of-the-box functionality with very little user effort. This data can then be searched with SharePoint’s robust indexing and searching components or used to drive workflow, business logic, or custom applications that already exist within your environment.

InfoPath is a significant upgrade over standard SharePoint data collection with built-in lists. Typically with SharePoint lists, the designer has limited ability to make changes to the out-of-the-box new forms or edit forms that are generated on all SharePoint lists. These standard forms lack certain flexibility, such as the ability to limit access to specific fields when editing a SharePoint list item, or provide dynamic access to controls or additional data sources outside of traditional SharePoint lookup columns. SharePoint’s native storage mechanism of list items limits the potential for exporting and accessing the list data in the robust way an InfoPath XML form can. All of these problems are quick and easy to address if you choose InfoPath as the form to collect data. InfoPath is also extremely easy to set up.

Using InfoPath

Now that I’ve given you a basic overview of InfoPath, let’s look at a common problem that organizations are using the application to solve. Perhaps this example will spark some ideas for improving efficiency or data management within your own company.

A common use for InfoPath is converting Excel or paper-based expense report forms to consolidate them into a digital environment. InfoPath’s design surface is well-suited to manage the repetitive nature of this data and upload it to a SharePoint list where it can be calculated, categorized, and sent to managers and accounting for approval using either an out-of-the-box or a custom-built workflow.

I’ll walk through the process at a high level to explain what pieces need to be built and how they are assembled. Our expense projects seem to break down roughly into the following steps:

  • Converting existing forms to InfoPath
  • Approval
  • Connecting to external applications

I’ll include some extra components that aren’t required, to give you an idea of how to easily extend the project with additional functionality.

Convert existing forms to InfoPath. Existing paper-based forms, along with Word and Excel files, can be cataloged and converted to equivalent InfoPath forms or aggregated into a single flexible form. For example, an expense form converted to an InfoPath form will collect expense data from individual users, to be submitted to a form library.

Figure 1 shows an InfoPath expense form that can look up current exchange rates, mileage allowances, and so forth, then calculate the amounts and automatically assign them as line items. The form can also look up existing user information, require explicit sign-off, and more.

As expense reports are added to a form library, code can be attached to the library to look at the form data and adjust permissions as necessary. The code can extract specific data elements and apply that data to existing local or external applications based on accounting rules. Although this element isn’t required, it adds significant options to the overall application and is a good place to start writing some lightweight custom code as part of the application.

Approval. An out-of-the-box or custom workflow is created to assign forms to a user’s manager for approval, then finally to accounting for approval. A custom workflow can override existing task edit forms with custom InfoPath forms. Custom forms are valuable in their ability to collect and process custom data as users complete their approval tasks for expense reports. Data can be as simple as a check box asking for additional verification, as Figure 2 shows, or as complicated as querying additional systems to look up and apply that data to the local expense report.

For additional functionality, you can easily build a custom web part that lets users attach digital receipts and proof of expenses to their expense reports. These files can be attached to specific line items on an expense report, as Figure 3 shows. Accountants play a large role in managing data and can benefit from a custom dashboard that shows all expense data as individual line items, with appropriate attachments categorized into custom accounting codes for easier management and exporting.

Connecting to external applications. As an added bonus, custom code can be written from various components in the application to connect to external line-of-business systems. One of the more common requests is to export approved expense reports directly into an organization’s accounting system via web services or additional connectivity options.

Well Worth Trying

Combining InfoPath and SharePoint gives non–software developers many options for collecting and managing data, beyond simply using SharePoint lists and metadata to create standard list collection forms—and all without writing a single line of code. Developers might also want to check out InfoPath’s robust features and capabilities before they resort to using Visual Studio to build an ASP.NET web form or an entire new application. You can still add code to InfoPath behind partial classes to scale the application to fit your needs.

InfoPath’s learning curve isn’t very steep, and you can jump right into using it. The basic functionality is as easy to learn as for Word and Excel. Microsoft’s movement toward collaboration and server-based offerings in its Office suite, combined with SharePoint’s features, make InfoPath a useful tool for achieving your business goals. SharePoint 2010 and InfoPath 2010 will combine even more integration and allow for more granular flexibility in terms of converting existing list forms to custom InfoPath documents quickly and easily.

Hide comments


  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.