Microsoft Infopath Tutorial and Online Training Course

Microsoft InfoPath Tutorial: Section 8 Overview

In this section, you will learn about:

• Developing a form from a database
• Using InfoPath forms to add records to a database
• Using InfoPath forms to query a database

Working with a Database

Developing a Form from a Database

You can use InfoPath to design forms to query and update data contained in a Microsoft SQL Server or Access database. In this case, InfoPath won’t create a default XML data source for you; rather, the fields will correspond to those in the tables from your database. Normally, you’ll create several different forms for interacting with different parts of the database.

When you design a form based on a database, InfoPath will automatically create two views for you, one for searching the database, which contains fields and buttons for searching and adding new records, and the other, blank by default, for updating the data.

To get started:

1. From the File menu or the task pane menu, select Design a Form.
2. On the Design a Form task pane, click New from Data Source.

The Data Source Setup Wizard opens.

Note that you can also use the wizard to design a form from an existing XML Schema, XML data file, or Web service.

1. Select Database and click Next.
2. On the next screen, click Select Database.
3. In the Select Data Source dialog, navigate to the database file and click Open. Keep in mind that for others to use your forms to interact with the database, the database must be located on a shared drive.

If the connection succeeds, and you have multiple tables in your database, then the Select Table dialog should now open:

1. Select the table you want to use as the parent table for your form and click OK.

For this example, we’ll create a form that allows users to view and update projects and the tasks associated with them. For the primary table, we’ll select Projects.

It’s important to select as the primary, or parent, table a table that has a one-to-many relationship with the other (child) tables you’ll be updating. In a one-to-many relationship, each record in the parent table may be related to several records in the child table. In our example, each project includes multiple tasks, creating a one-to-many relationship between Projects and Tasks.

If you select as a primary table a table that has a many-to-one relationship with the other tables, InfoPath’s form submission features may not work properly.

The Data Source Setup Wizard should now look something like this:

1. To add child tables, click Add Table.
2. In the Add Table or Query dialog, select a table and click Next.

Keep in mind that you should only add tables that will be updated by the data collected from the form. If you’ll be populating some controls on the form with data from another table, but you don’t want that data updated (such as when displaying a fixed number of categories to choose from), then don’t add that table yet—you can do that when you add secondary data sources.

1. In the Edit Relationship dialog, confirm that the fields shown are the ones that connect the parent and child tables. If necessary, you can add connecting fields by clicking Add Relationship.

When you’re done, click Finish.

1. In the Data Source Setup Wizard, continue to add tables until all the ones you need appear in the data source structure. Below, another table, Risks, was added beneath Tasks. Together, these two tables form another one-to-many relationship, with several risks possibly being associated with each task in the project. If risks had been associated directly with projects, rather than tasks, then the Risks table would have been added directly beneath Projects.

1. You can now exclude any fields that you don’t need, or that InfoPath doesn’t support. For example, InfoPath doesn’t support long data types, which include memo, hyperlink, and picture fields; any of these fields that appear in your database need to be excluded:
   1. At the bottom of the wizard, check the Show table columns checkbox. This reveals all the fields in each table:

1. 1. Uncheck any field you don’t need for this form, as well as any field that uses a long data type.
2. Click Next to proceed to the final screen.
3. On the next screen, confirm the information, paying special attention to the Submit status. This should read Enabled; if it doesn’t, check the error message it provides and click the Back button on the wizard to fix the problem.

Tip:

Sometimes InfoPath will detect a many-to-one relationship between the tables you added, but the form will still work. If you’re sure you’ve set up the tables in your database properly, finish the wizard and, in the form, check the form submitting options by selecting Submitting Forms from the Tools menu. Submit to a database should be enabled. If it isn’t, you’ll need to create a new form and repeat the wizard.

At the bottom of the screen, select which view you want to design first—query view or data view. Query view is the view in which users enter information to query, or search, the database. Data view is the view in which users add new records and update existing records.

We’ll start with data view.

1. Click Finish.

InfoPath creates a form with two views (Query and Data Entry)—you can see these by selecting the Views task pane. The Data Source task pane displays the fields from the database, grouped together for the two views:

When you design each view, it’s important to use the fields in the appropriate data source group. The fields in the queryFields group should be used to design the query view, and the fields in the dataFields group should be used to design the data entry view.

Using InfoPath Forms to Add Records to a Database

The data entry view is used to add and update records. If necessary, switch to this view by selecting it from the list on the Views task pane:

The data entry view is blank by default. To add controls to the form, switch back to the Data Source task pane.

You can add controls one by one, laying out your form first by inserting tables and sections, or you can add all the fields at once, then delete the ones you don’t need:

• To add controls one at a time, right-click a field under the dataFields group and selecting a control from the shortcut menu:

The control is added inside a repeating section that corresponds to the group in the data source that contains the field:

• To add all the fields at once, right-click the dataFields group and select Section with controls.

This populates the form automatically, with all the fields contained in repeating sections or tables:

InfoPath chooses each control based on the data contained in the corresponding field. For that reason, InfoPath used the Date Picker control for the Date/Time fields in the database and the Drop-Down List Box control for Project Completed, a Boolean (Yes/No) field. You don’t need to do anything to either of these types of fields to make them function.

You may, however, want to change some of the fields. For example, in the form above, the Status ID field is going to be used to display several pre-defined statuses (Green, Yellow, Orange, and Red). We want the user to be able to select from these, so we’ll change the text box to a drop-down list box. To change a control, right-click it and, from the shortcut menu, select Change To, and then the type of control you want:

The control is now a drop-down list box:

The controls in the data entry view appear in repeating sections or tables so users can add as many records as they need. Each repeating section or table corresponds to a particular group. In our example, a repeating section bound to the Projects group contains the project fields, such as Project ID and Project Name. Inside that section, another section, bound to the Tasks group, contains the task fields. Inside this, a repeating table is bound to the Risks group. This mirrors the structure of the groups displayed on the Data Source task pane.

Practically speaking, what this means is that the user will be able to insert a new project record above or below the current one, which contains fields for entering task information and as many associated risks as necessary. Or, the user will be able to view the project record and insert a new task, or a new risk for one of the tasks. Preview the form to see how this works:

If you add another field to the form, you must add it to the appropriate section (or table)—that is, the section that corresponds to the group containing that field. For example, all the project fields in our example must be inserted into the repeating section that contains the other project fields, or into a new section of its own. The new section will be automatically bound to the Projects group. If you move a field (or control) into a different section, make sure it’s a section that’s bound to the group in the data source that contains that field. In other words, you can’t move the Project Name field into the repeating section that contains the task fields. If you do, you’ll see an exclamation point in the field:

If you see an exclamation point in a field, right-click it and, from the shortcut menu, select More Details.

This opens a message providing more information on the error:

InfoPath doesn’t always come up with the best layout on its own, but adding all the controls at once is the fastest way to build a form. You can add and delete fields and change the labels as appropriate. In our projects and tasks example, InfoPath has added the Project ID field twice, since it appears in both tables. We’ll delete the second instance:

We also don’t need the Task ID in the Tasks table, so we’ll delete that column:

1. Position the cursor inside the column.
2. From the Table menu, select Table, and then Select, and then Column from the submenu.
3. With the column selected, press the Delete key.

1. Drag the borders of the remaining columns to decrease the width of the Risk ID column and increase the width of the Risk column, where users will enter a brief description of the risk:

The form itself needs cleaning up, to make it easier to use. For example, some of the labels can be changed, like the Status ID label, since we intend this field to list statuses, rather than their IDs. We can also increase the spacing between each field, change the sizes of the fields as necessary to make them usable and consistent, and add some bold formatting and some subtitles using the Fonts task pane or the Formatting toolbar:

1. First, edit the existing labels as necessary and apply the formatting you want to them.
2. Apply any formatting filters you want to the controls. For example, we can set our Rate, Estimated Project Price, and Total Project Price fields to display currency:
   1. Double-click each control.
   2. In the Properties dialog, on the Data tab, click the Format button.

1. 1. In the Format dialog, select the Currency option.

1. 1. Select any other options, and then click OK.
   2. Preview the effect by selecting Sample Data from the View menu:

1. Edit the size of each of the controls by double-clicking on the control and entering values into the Size tab of the Properties dialog, or by dragging the edge of the control. Move the controls as necessary for a neat appearance.

You can also return to the Layout task pane and insert tables inside the sections, then move the controls into the table cells. Just be careful that the table and the fields remain inside the correct repeating sections:

Use the Alignment buttons on the Formatting toolbars to align the text within the table cells.

1. If you like, apply a color scheme to the form by selecting Color Schemes from the Formatting menu. In the Color Schemes task pane, click on the color scheme you want.
2. Insert subtitles for each of the repeating sections. You can also insert a table with a title at the top of the form. Use the Fonts task pane to apply styles to the subheadings:

1. Add borders and shading to the repeating sections, or the tables you inserted inside them:

a. Right-click the section or table and select Borders and Shading from the shortcut menu.

b. In the Borders and Shading dialog, make selections from the Borders tab to add a border.

c. On the Shading tab, select a fill color.

d. Click OK.

Preview your form now by clicking the Preview Form button on the Standard toolbar:

Insert new sections to see how the form looks:

Close the preview by clicking the Close Preview button.

If you’ve included any lists in your form, you’ll need to populate them. Lists that use Boolean values, like Yes/No in Access, may already be populated, like the Projected Completed field in our example. When InfoPath created the field, it included two values—True and False. You can see these when you double-click the field and open the Drop-Down List Box Properties dialog:

Instead of “True” and “False”, we’ll modify the entries to display “Yes” and “No”:

1. Highlight the first entry (“True”) and click Modify.
2. In the Modify Choice dialog, change the display name to “Yes”.

1. Click OK.
2. Repeat these steps to change what’s displayed for “False” to “No”.

When you click OK, you can see the changes in the Drop-Down List Box Properties dialog:

1. Change the default value that’s displayed in the list box by highlighting the last line (“False”) and clicking the Set Default button.
2. Click OK to close the dialog.

We still need to populate the Status list box, so the user can pick a status, rather than forcing the user to enter the correct status. Controlling what’s selected helps to maintain the integrity of the data, since three different people could enter three different things.

There are two ways to populate a list box. The first is by manually entering the values, as was done for the Project Completed field. The second, and easier way, is to populate the list using a secondary data source:

1. Double-click the control to open the Drop-Down List Box Properties dialog.
2. On the Data tab, under List box entries, select Look up in a database, Web service, or file.

1. Click the Secondary Data Source button.
2. In the Secondary Data Sources dialog, click the Add button.

This opens the Data Source Setup Wizard again.

1. Use the wizard to add the secondary data source, even if it’s the same database you added before. For the primary table, select the table containing the data you want to populate the list box with.
2. In the final screen of the wizard, confirm the settings and click Finish.

1. Click Close to close the Secondary Data Source dialog.

The secondary data source should now appear in the Data source field of the Drop-Down List Box Properties dialog.

1. Click the Select XPath button next to the Entries field.

This opens the Select a Field or Group dialog:

1. Select the table (d:Status in the example above) and click OK.

The table now appears in the Entries field of the Drop-Down List Box Properties dialog:

1. Right now, the value is bound to the ID field in the table, which is fine. But the ID field is also bound to the display text of the list box. To make the list box display the corresponding text value, rather than the ID, click the Select XPath button next to the Display name field.
2. In the Select a Field or Group dialog, select the table field that contains the text you want to display. For our example, we’ll select Status.

1. Click OK.
2. Click OK to close the Drop-Down List Box Properties dialog.
3. Preview the form by clicking the Preview Form button on the Standard toolbar.

If you check the binding of the control, by right-clicking the control and selecting Change Binding from the shortcut menu, you can see that the control is still bound to the Status ID field in the Tasks table:

This means that whatever value is selected from the list box will be entered into the Status ID field of the Tasks table, capturing the current status of the task.

We’ll repeat these steps for the Client ID. After changing the text box to a drop-down list box control, we’ll open the Properties dialog and add another secondary data source—the Clients table in the same database. In the Data Source Setup Wizard, we’ll uncheck all but the ClientID and ClientName fields:

With the data source added, we’ll set the display text of the drop-down list box to display the client’s name:

The Drop-Down List Box Properties dialog now looks like this:

As with the Status field, the Client ID field on the form is still bound to the Client ID field in the Projects table.

Finally, we need to set the ID fields on the form to read-only, so users can’t edit them. These numbers are given to the records automatically:

1. Double-click the control.
2. In the Properties dialog, select the Display tab.
3. Select the Read-only check box.

1. Click OK.

The final task for the data entry form is to add a submit button to allow users to submit data to the database. When you create a form from a database, InfoPath enables the Submit to a database option for the submit button, making form submission virtually automatic:

1. Add the button to the form by clicking Button on the Controls task pane.
2. Double-click the button to open the Button Properties dialog.
3. From the Actions menu, select Submit.

This opens the Submitting Forms dialog:

The Enable submit option and the Submit to a database menu item should be selected automatically.

1. Click the Submit Options button.
2. In the Submit Options dialog, select what you want to happen after the form is submitted. For example, we’ll select Close the form.

1. If you like, enter a custom message.
2. Click OK.
3. Click OK to close the Submitting Forms dialog.
4. Click OK to close the Button Properties dialog.
5. Preview the form. Enter some data and click the Submit button.

If everything works correctly, you should see the following message:

You can confirm that the data was entered correctly into the database by opening the database itself and checking the records.