Amal Nagm: Create an asset tracker solution using InfoPath and Excel

Create an asset tracker solution using InfoPath and Excel

Author: mety Labels:: Create an asset tracker solution using InfoPath and Excel

Microsoft Office InfoPath lets you create form templates for data entry and collection. Microsoft Office Excel 2007 enables users to calculate, chart, and analyze data. By using both of these programs and XML, you can easily create a simple but effective, no-code business solution for tracking assets in a company.

Introduction to creating an Asset Tracker solution

You can exchange XML data between InfoPath and Excel. Although the Infopath Export command is a simple, straightforward way to send form data to Excel, there's another approach you can take which gives you much more flexibility. By explicitly mapping the InfoPath XML schema file (.xsd) in Excel, and then importing InfoPath form files (.xml) into Excel, you can customize the layout of your data and better leverage the features in Excel to help you manage and analyze your data more efficiently.
In this article, we will use the InfoPath Asset Tracker sample form template to illustrate how you can make InfoPath, XML, and Excel work together. Imagine that each year, your organization inventories assets in each employee's office. Then, an Excel report is run to help decide how to allocate the equipment budget for next year. The following diagram is an overview of how you can gather, track, and report asset data in your department.

The Asset Tracker form template is created.

The schema file is mapped to an Excel table and the table layout is customized.

A blank PivotTable and PivotChart summary report is created.

The Asset Tracker form is used to collect data from users.

All form data is exported into the Excel table and the PivotTable and PivotChart reports are refreshed.

This solution involves the following user roles and products:

= Required No support icon

= Not required

Roles	Office InfoPath 2007	Office Outlook 2007	Office Excel 2007

	Software Requirements


Solution Designer

Decision-maker

Employees

Step 1: Customize the Asset Tracker form template

The Asset Tracker form template, which is included in InfoPath, enables you to gather information about each asset in an employee's office, the department that the employee belongs to, categories of assets, and details about each asset. In this solution, a form designer submits the form template to the decision-maker's e-mail account by using Office Outlook 2007.

Task 1: Open and customize the Asset Tracker form template

Because you cannot publish a form template to a list of e-mail recipients if it has a rich text box control containing linked images, you need to make a small change to the Asset Tracker sample template form.

On the File menu, click Design a Form Template.
In the Design a Form Template dialog box, under Open a form template, click Customize a sample.
In the Getting Started dialog box, click Sample - Asset Tracker, and then under Form Tasks, click Design this form.
Right click the assetNotes control, point to Rich Text Box properties, click the Display tab, and then under Available formatting, clear the Linked Images check box.

Task 2: Define the Submit options

The next task is to define the Submit options so that the employees can submit a uniquely named form file (.xml) to the decision-maker's e-mail account as an attached file.

On the Tools menu, click Submit Options.
In the Submit Options dialog box, select the Allow users to submit this form check box.
Click Send form data to a single destination, and then, in the list, click E-mail.
Click Add.
The Data Connection Wizard appears.
In the To box, type the alias of the decision-maker who will receive all the submitted forms.

To specify a unique subject line, in the Subject box:

Click Insert Formula .

Type the following formula:

concat(my:employee/my:employeeName, my:employee/my:employeeDepartment)

Click Verify Formula, and then click OK twice.

In the Introduction box, type an appropriate message or add important instructions for this solution.
Click Next.
On the next page of the wizard, click Send the form data as an attachment, and then select the Attach the form template to ensure that users can open the form check box.
This helps ensure that employees have access to the form template file (.xsn) in case they cannot access it from it's network location.

To specify a a unique file name for each form file (.xml) in the Attachment Name box:

Click Insert Formula .

Type the same formula as the one on the Subject line:

concat(my:employee/my:employeeName, my:employee/my:employeeDepartment)

Click Verify Formula, and then click OK twice.

Click Next.
On the next page of the wizard, in the Enter a name for this data connection box, type a descriptive name for this submit data connection.
Verify that the information is correct in the Summary section, and then click Finish.
Save the form it to a public network folder accessible to the decision-maker and to all employees.

Step 2: Map the schema file and customize the table layout in Excel

Setting up the connection between InfoPath and Excel requires three tasks: extracting the schema file for the Asset Tracker form template, mapping this schema file in Excel, and customizing the default layout of the Excel table that will contain all the imported XML form data files.

Task 1: Extract the schema file from InfoPath

In InfoPath, open the Asset Tracker form template file.
On the Tools menu, click Design this Form.
On the File menu, click Save As Source Files.
In the Browse for Folder dialog box, select a folder or create a new folder where you want to store the schema file.

Note the location of the folder where all of the source files are saved and the name of the schema file, in this case, myschema.xsd, which contains the schema definition of the form XML data.

Task 2: Map the InfoPath schema file in Excel

Create a new Excel file, and then save it to a location accessible to the decision-maker.
In Excel, if the Developer tab is not visible, do the following:
1. Click the Microsoft Office Button , and then click Excel Options.
2. In the Popular category, under Top options for working with Excel, select the Show Developer tab in the Ribbon check box, and then click OK.
  Note The Ribbon is a component of the Microsoft Office Fluent user interface.
On the Developer tab, in the XML group, click Source.
The XML Source task pane is displayed.
Click XML Maps, and then in the XML Maps dialog box, click Add.
On a computer that is running Windows Vista
- In the Address bar, click the drive or folder that contains the myschema.xsd file that you created in step 2 of the section, Task 1: Extract the schema file from InfoPath.
On a computer that is running Microsoft Windows XP
- In the Look in list, click the drive or folder that contains the myschema.xsd file that you created in step 2 of the section, Task 1: Extract the schema file from InfoPath.
Click the file, and then click Open.
When the Multiple Roots dialog box appears, click the assetTracking root node that is defined in the myschema.xsd file, and then click OK.
The XML map is displayed in the XML Source task pane.
Drag the ns1:asset node element, which is a repeating XML element, to the worksheet location where you want the Excel table to appear.

Task 3: Customize the layout of the Excel table

By default, Excel uses the XML element names as column headings for the table. However, you can change the column headings by editing the column header cells.

To modify the default column headers, keep the Excel table selected, find the following string prefix, ns1:asset, and remove it. For example, you would change ns1:assetID to ID.
To adjust the column width, click the Select All button, and then double-click any column heading boundary.

Step 3: Create a PivotTable and PivotChart summary report

Decision-makers use PivotTable and PivotChart reports to analyze, explore, and visualize summary data. By setting up the PivotTable and PivotChart reports you want to use in this scenario ahead of time, it will be a simple operation for a decision-maker to run the report with the completed data later.

Select a cell in the Excel table.
On the Insert tab, in the Tables group, click PivotTable, and then click PivotTable.
Excel displays the Create PivotTable dialog box.
To place the PivotTable report in a new worksheet starting at cell A1, click New Worksheet, and then click OK.
Excel adds an empty PivotTable report to the specified location and displays the PivotTable Field List so that you can add fields, create a layout, and customize the PivotTable report.
To create a cross-tabular report of the data, drag the ID field to the Values area, the Department field to the Column labels area, the Category field to the Row labels area, and the AssignedTo field to the Report Filter area.
Click the PivotTable report.
On the Insert tab, in the Charts group, click the Bar chart type.
Under 2-D Bar, select Clustered Bar.
Adjust the size and location of the PivotChart report to your preference.

The blank PivotTable and PivotChart reports

Step 4: Collect asset information from users

Now, that the form template is ready to be filled out by all of the employees, you need to publish the form and send it to each employee. When each employee submits the completed form, it is sent as an XML data file (.xml) attached in an e-mail message to the decision-maker's e-mail account. The decision-maker can then move all of the messages to a dedicated InfoPath folder in Outlook. In this scenario, it is recommended that you assign a specific time-period for all users to complete the forms so that the decision-maker can run the report with all the completed forms when the data-gathering deadline has been reached.

Entering data in the Asset Tracker forms

In InfoPath, open the form template (.xsn) that you saved in Step 1: Design the Asset Tracker sample form template
On the Tools menu, click Design this Form.
On the File menu, click Publish.
In the Publishing Wizard, click To a list of e-mail recipients, and then click Next.
On the next page of the wizard, in the Form template name box, type a name for the form template, and then click Next. The form template name will appear in the subject heading of the message that is sent to your e-mail message recipients.
Optionally, on the next page of the wizard, you can create columns that will appear in your Outlook folders to help organize your e-mail messages.
For more information, see Demo: Use InfoPath e-mail forms to view data in Outlook 2007.
Click Next.
Click Publish.
After the Publishing Wizard finishes publishing the form template, an e-mail message with the form will open.
Fill out the message, type in the employee names or use a distribution list, and then send the message.

In Outlook, the decision-maker can organize all of the submitted XML forms in one of two ways:

If the InfoPath Form folders message box appears the first time the message opens, click Create Form Folder.
Create a rule in Outlook to move all of the related InfoPath forms to an InfoPath folder.
For more information, see Manage messages by using rules.

Step 5: Import forms into Excel

Because you already created the Excel workbook steps 2 and 3, it's a simple matter for the decision maker to export and review the form data. The decision maker simply needs to export the xml files (.xml) from Outlook, import all of the form files (.xml) into the Excel workbook, and then refreshing the data.

Task 1: Export the forms from Outlook to a Windows folder

In Outlook, select the folder that contains all of the forms that you received from employees, and then press CTRL+A to select all of the form e-mail messages.
Right click the selection, point to InfoPath Actions, and then click Export Forms.
In the Export Forms dialog box, select a folder, and press OK.

Task 2: Import the forms into Excel

In the Excel workbook that you created in Step 2: Map the schema file and customize the table layout in Excel, select one of the mapped cells in the Excel table.
On the Developer tab, in the XML group, click Import.
The Import XML dialog box is displayed.
On a computer that is running Windows Vista
- In the Address bar, click the folder that contains the XML data files (.xml) created in Task 1: Export the forms from Outlook to a Windows folder.
On a computer that is running Microsoft Windows XP
- In the Look in list, click folder that contains the XML data files (.xml) created in Task 1: Export the forms from Outlook to a Windows folder.
Do one of the following:
- If the files are contiguous, press SHIFT, and then click the first and the last file in the list.
- If the files are not contiguous, press CTRL, and then click each file that you want to import in the list.
Click Import.

By default, Excel overwrites any existing data in the mapped cells, which is the desired final outcome for this business solution. However, a decision-maker could, for example, import the data several times to gauge progress before the scheduled deadline.

Note In Excel, you can also append the data by using the Append new data to existing XML tables option (on the Developer tab, in the XML group, click Map Properties), which may be appropriate for other solutions.

For more information, see the Excel help topics, Import XML data and Append or overwrite mapped XML data.

Task 3: Refresh the PivotTable and PivotChart reports

Click a cell in the PivotTable report.
On the Options tab, in the Data group, click the arrow next to Refresh All, and then click Refresh.

The Excel workbook now contains all of the data and summary reports that you need to help you allocate the equipment budget for next year. Of course, you can do further analysis in your Excel workbook if necessary, such as sorting, filtering, or conditionally formatting the data.

Final thoughts: Consider using a SharePoint list or Access database

As an alternative, consider using a SharePoint list or Access database, instead of XML, to transfer data between InfoPath and Excel.

Use a SharePoint List

From InfoPath, you can easily use a SharePoint list as a read-only data source. You can do one of two things:

From the SharePoint list, export the data to Excel, which automatically creates a data connection in Excel.
From Excel, explicitly create a data connection to the SharePoint list.

Once a data connection is created, you can refresh the data in Excel to retrieve up-to-date data.

You can use a SharePoint list to add and update the data, use an InfoPath form to display an item from the list (for example, a complex or long item best displayed vertically), and then use Excel to further analyze the data.

Combining InfoPath, a SharePoint List, and Excel

Display a single item for easy review in InfoPath.

Add and update data in a SharePoint List.

Refresh and report up-to-date with Excel.

For more information, see Add a data connection to a SharePoint document library or list and Import data from a SharePoint list.

Use an Access database

From InfoPath, you can create a read/write connection to an Access database. From Excel, you can explicitly create a data connection to the Access database that you can refresh to retrieve up-to-date data. You can even define the connection to automatically refresh when the workbook is opened, or periodically refresh, such as every 5 minutes.

You can use an Access database as an intermediary between InfoPath and Excel. When you submit an InfoPath form to Access, you update the Access database. When you refresh the data connection in Excel, you retrieve updated data from Access. In effect, you are submitting data indirectly from InfoPath to an Excel workbook.

Display and submit a form to an Access database.

Store data in an Access database.