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.

To obtain optimal performance when using a very large database, you can use two data connections to the same database: one querying a small subset of columns, the other a single row of all columns. If the database is small to moderately large, or performance is not an issue, see Create a Multi-View Master/Detail using an InfoPath Data Filter.

In this task we will create a new form connected to a sample Access database, supplemented by a secondary connection to the same database, and use controls, rules, and queries 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.

The main data connection will be used for our detail view. We will query for details about the customer selected by the user. Now let's add a supplementary data connection which will be used to display our master view. It is optimized to retrieve only the columns we will display.

Add a supplementary data connection to the same database:

1. Choose Data Connections from the Tools menu.
2. In the Data Connections dialog box, click Add.
3. In the Data Connection Wizard, select Receive Data, and then click Next.
4. Select Database, and then click Next.
5. Click Select Database, locate and select Nwind.mdb, and then click Open.
6. In the Select Table dialog box, select the Customers table, and then click OK.
7. In the Data Source Structure list box, clear every check box except CustomerID and ContactName, and then click Next.
8. Name the data connection Master.
9. Ensure that Automatically Retrieve Data When Form is Opened is selected, click Finish, and then click Close.

Create the master view:

1. Delete everything that was automatically generated in the view.
2. Open the Data Source task pane, and then select Master from the Data Source drop-down list.
3. Right-click dataFields/d:Customers, and then choose Repeating Table.
4. Delete the Customer ID column.
5. Right-click the Contact Name column label, and then choose Insert | Columns To The Right.
6. Click inside the new column to set the insertion point.
7. Open the Controls task pane, and then click Button.
8. Double-click the inserted button.
9. 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, and then select Main from the Data Source drop-down list.
5. Right-click dataFields/d:Customers, and then choose Repeating Section With Controls (see Figure 2).

Figure 2. The completed detail view.

Add code behind the View Detail button:

The following code can also be expressed in rules. For this example, we chose to use code, but you may substitute rules if you prefer.

1. In the Views task pane, select View 1.
2. Double-click the View Detail button, and then click Edit Form Code.
3. In the Microsoft Script Editor, replace the contents of the OnClick event handler with the following code:

Try it:

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