When using InfoPath as a front end to a large database it is often convenient to be able to skim through the rows looking at only a few columns, such as a contact name, and then click a button to view the rest of the information.

You can use an InfoPath data filter to easily and quickly implement this functionality. However, this method might not be ideal for very large databases as its performance could suffer. If you are connecting to a very large database, and performance is important, please see Create a Multi-View Master/Detail using a Query Filter.

In this task we will create a new form connected to a sample Access database and use controls, rules, and filters to create a multi-view master/detail list.

Obtaining the sample Access database:

The sample Access database we will use is supplied as part of the Microsoft Office 2003 default installation. If installed, you can locate it at C:\Program Files\Microsoft Office\OFFICE11\SAMPLES\Nwind.mdb. If the database file does not exist in this directory, you can download it from the Microsoft Web site at http://www.infopathdev.com/cgi-bin/awredir.pl?url=http://www.microsoft.com/downloads/details.aspx?FamilyID=C6661372-8DBE-422B-8676-C632D66C529C&displaylang=en.

Create a new form from a database connection:

1. Launch InfoPath, and then choose Design A Form from the File menu.
2. In the Design A Form task pane, click New From Data Connection.
3. In the Data Connection Wizard, select Database, and then click Next.
4. Click Select Database, locate and select Nwind.mdb, and then click Open.
5. In the Select Table dialog box, select the Customers table, and then click OK.
6. Click Next, and then click Finish.
7. Delete everything that was automatically generated in the view except for the Run Query button (we will keep this at the top of the page).
8. Click below the Run Query button to set the insertion point.

Create the master view:

1. Open the Data Source task pane.
2. Right-click dataFields/d:Customers, and then choose Repeating Table.
3. Delete every column in the table except for Contact Name.
4. Right-click the Contact Name column label, and then choose Insert | Columns To The Right.
5. Click inside the new column to set the insertion point.
6. Open the Controls task pane, and then click Button.
7. Double-click the inserted button.
8. In the Button Properties dialog box, change the label to View Detail, change the ID to btnViewDetail, and then click OK (see Figure 1).

Figure 1. The completed master view.

Create the detail view:

1. Open the Views task pane.
2. Click Add A New View.
3. Name the view Detail, and then click OK.
4. Open the Data Source task pane.
5. Right-click dataFields/d:Customers, and then choose Repeating Section With Controls (see Figure 2).

Figure 2. The completed detail view.

We need to add a helper node to the schema to store the ID of the customer selected in the master view. This ID will be used to filter the display in the detail view to show only the selected customer. After setting this value, we will switch to the detail view.

Add the helper node:

1. In the Data Source task pane, right-click myFields, and then choose Add.
2. Name the field DetailId.
3. Select Whole Number from the Data Type drop-down list.
4. Click OK.

Add rules to the View Detail button:

1. Open the Views task pane, and then select View 1.
2. Double-click the View Detail button.
3. In the Button Properties dialog box, click Rules, and then click Add.
4. Name the rule Update DetailId and Change Views.
5. Click Add Action.
6. Choose Set A Field’s Value from the Action drop-down list.
7. Click the Data Source button to the right of the Field text box.
8. In the Select A Field Or Group dialog box, select my:DetailId, and then click OK.
9. Click the Insert Formula button to the right of the Value text box.
10. In the Insert Formula dialog box, click Insert Field Or Group.
11. Select dataFields/d:Customers/:CustomerID, and then click OK three times.
12. In the Rule dialog box, click Add Action.
13. Choose Switch Views from the Action drop-down list.
14. Choose Detail from the View drop-down list, and then click OK (see Figure 3).
15. Click OK three times to close all open dialog boxes.

Figure 3. Adding rules to the View Detail button.

The functionality has been added to the button, but the detail view still displays all of the rows. Let's add a filter to repeating section in the detail view to show only the selected row.

Add a filter to the detail section:

1. In the Views task pane, select Detail.
2. Double-click the repeating section label.
3. On the Display Tab, click Filter Data, and then click Add.
4. In the Specify Filter Conditions dialog box, select CustomerID from the first drop-down list.
5. In the second drop-down list, select Is Equal To.
6. In the third drop-down list, select Select A Field Or Group.
7. In the Select A Field Or Group dialog box, choose my:DetailId, and then click OK (see Figure 4).
8. Click OK three times to close all open dialog boxes.

Figure 4. Filter the detail view based on the selected Customer ID.

Try it:

1. Click Preview Form (or press Alt+P on the keyboard).
2. Click Run Query to populate the master table.
3. Click View Detail for one of the rows to view the selected contact in the detail view.