Section 7.5.Populate a Control from a Data Source

7.5. Populate a Control from a Data Source

The previous section shows you how to use a database as the primary data source for a form, but you had to know customer or employee codes for the query to work. It would be better if those codes were listed in drop-down list boxes so you could just choose the right criteria (Figure 7-23).

Figure 7-23. Modify the previous sample to populate listboxes from the database

7.5.1. How to do it

In order to populate a listbox from a database, you need to establish a secondary data source. To add a secondary data source to the sample form:

1. In Design mode, right-click the Customer ID text box and choose Change to Drop-Down List Box from the pop-up menu. InfoPath converts the control to the new type.

2. Properties or Alt+Enter) and select Look up in a data connection (Figure 7-24).

   Figure 7-24. Select Look up in a data connection to populate a list from a secondary data source

   
   
   

3. Click Add to select a data source as described in the previous section. In this case, you want to retrieve the Customers table from the Northwind database, as shown in Figure 7-25.

   Figure 7-25. Select the fields to retrieve from the Customers table

   
   
   

4. Click the Select XPath button beside the Entries box in Figure 7-24 and select the d:Customers node. Select :CustomerID for Value and :CompanyName for Display name, as shown in Figure 7-26.

5. Click OK and resize the control as needed. When you preview the form, Customer names are displayed in the control (Figure 7-27).

6. Repeat these steps for the Employee ID control using the Employees table from the database and the d:Employees, :EmployeeID, and :LastName XPath settings as shown in Figure 7-28.

7.5.2. How it works

You can have multiple secondary data sources for a form. Each data source may be queried when the form loads depending on the setting in the last step of the Data Connection Wizard (Figure 7-29).

If the data source is not queried at form load, or if it needs to be updated, you can refresh the secondary data source by adding a Button control to the form and selecting the Refresh action as shown in Figure 7-30.

Figure 7-26. Set the XPath for Entries, Value, and Display name fields

Figure 7-27. List control populated with customers

Figure 7-28. Settings to populate the Employee list

InfoPath creates a schema component file in the form template for each secondary data source you use. You can examine those schemas if you extract the form files from the template.