Wednesday, February 1, 2017

Create a cascading list box

Create a cascading list box

Cascading list boxes help users fill out forms faster and help ensure that users enter data correctly. This article describes how to create and populate cascading list boxes with data. In Microsoft Office InfoPath, you can populate a list with data that uses values from the form, values from an external Extensible Markup Language (XML) document, or values from a Microsoft Office Access database.

In this article

What is a cascading list box?

Step 1: Insert a cascading list box

Step 2: Provide the data

What is a cascading list box?

A cascading list box is a list box with choices that change based on the value that a user selects in another list box. For example, if a user clicks Condiments in the Categories box shown in the following illustration, the Products box will display a list of condiments.

Cascading list box example

The following sections show you how to design a form template with two list boxes, where the second list box is filtered based on the value a user selects from the first list box. When the value in the first list box changes, the filter is automatically applied to the second list box, which changes its values.

Compatibility considerations

Filters can be used only in form templates that are designed to be filled out in InfoPath. Filters are not available in browser-compatible form templates.

Top of Page

Step 1: Insert a cascading list box

To create a cascading list box, you must insert two list boxes in your form template.

Note: When you insert a cascading list box in your form template, ensure that the two list boxes are not in a repeating section or repeating table.

  1. If the Controls task pane is not visible, click More Controls on the Insert menu, or press ALT+I, C.

  2. Under Insert controls in the Controls task pane, click Drop-Down List Box. When a user fills out a form that is based on your form template, the selection in this drop-down list box will determine the choices that are available in the second list box.

  3. Click List Box. Your form template should now contain a drop-down list box and a standard list box.

    Drop-down list box and list box in a form

  4. Double-click the drop-down list box that you inserted in your form template in step 2.

  5. In the Drop-Down List Box Properties dialog box, click the Data tab.

  6. In the Field name box, type listBox1, and then click OK.

  7. Double-click the list box that you inserted in your form template in step 3.

  8. In the List Box Properties dialog box, click the Data tab.

  9. In the Field name box, type listBox2Cascade, and then click OK.

Top of Page

Step 2: Provide the data

Now that you have inserted the list boxes in your form template, you must provide the data that they will display. Then you must apply a filter so that the choices in the second list box are based on the user's selection in the first list box.

This section explains how to connect the list boxes to data by using three common methods: adding the data to the form itself; connecting the form to an existing XML document that contains the data; or connecting the form to a database that contains the data. To finish creating your cascading list box, click the method that you want to use in the following list.

Connect to data in the form

Connect to an external XML document

Connect to a database

Connect to data in the form

In this section, you will learn how to populate the list boxes with data from the form. To do this, you must first configure fields in the form template so that you can enter the names of the categories and a set of products for each category.

Configure fields in the form template

  1. If the Data Source task pane is not visible, click Data Source on the View menu.

  2. In the Data Source task pane, right-click the myFields group, and then click Add.

  3. In the Name box, in the Add Field or Group dialog box, type CategoriesAndProducts.

  4. In the Type list, click Group.

  5. Select the Repeating check box, and then click OK.

  6. In the Data Source task pane, right-click the CategoriesAndProducts group.

  7. In the Name box, in the Add Field or Group dialog box, type Category, and then click OK. This field will contain the names of the categories for the first list box.

  8. Right-click the CategoriesAndProducts group, and then click Add.

  9. In the Name box, in the Add Field or Group dialog box, type Product.

  10. Select the Repeating check box, and then click OK. This field will contain the names of the products for the second list box.

Because this list box is populated with data from the form, the data must be added to the form. To do that, you will have to create default values for the Product and Category fields.

Tip

You can also create list boxes that display values that users type in the form themselves. To do this, associate the list box with fields that are bound to controls in the form. When a user enters values into the controls, the choices in the list box change.

Create default values

  1. On the Tools menu, click Default Values.

  2. In the Edit Default Values dialog box, in the CategoriesAndProducts group, select the Category field (do not clear the check box), and then in the Default value box, type Seafood.

  3. Select the Product field (do not clear the check box), and then in the Default value box, type Crab meat.

  4. Right-click the Product field, and then click Add another Product above. This lets you add a second product for Seafood as a default value.

  5. Right-click the second instance of the Product field (do not clear the check box), and then in the Default value box, type Salmon.

  6. Right-click the CategoriesAndProducts group, and then click Add another CategoriesAndProducts below. This lets you add a second category as a default value.

  7. Use the previous steps to specify the default values for the second instance of the Category and Product fields to Dairy, Cheddar, and Chocolate milk, respectively.

  8. Click OK.

Now that you have entered default values for the Product and Category fields, you can connect the first list box to the data that you just entered.

Connect the first list box to the data

  1. In the form template, double-click the drop-down list box that you inserted previously.

  2. Click the Data tab.

  3. In the Drop-Down List Box Properties dialog box, under List box entries, click Look up values in the form's data source.

  4. Click Select XPath Data Source button next to the Entries box.

  5. In the Select a Field or Group dialog box, in the CategoriesAndProducts group, click the Category field, and then click OK two times.

Now you must connect the second list box to the data. While making this data connection, you will want to apply a filter to the second list box. This filter will populate the second list box with the appropriate products, based on the category selected in the first list box. The filter compares the value selected in the first list box (listBox1 field) with the list of categories saved in the form (Category field).

Connect the second list box to the data

  1. In the form template, double-click the standard list box that you inserted previously.

  2. Click the Data tab.

  3. In the List Box Properties dialog box, under List box entries, click Look up values in the form's data source.

  4. Click Select XPath Data Source button next to the Entries box.

  5. In the Select a Field or Group dialog box, in the CategoriesAndProducts group, click the Product field, and then click Filter Data.

  6. In the Filter Data dialog box, click Add.

  7. In the Specify Filter Conditions dialog box, click Select a field or group in the first box.

  8. In the Select a Field or Group dialog box, click listBox1, and then click OK.

  9. In the Specify Filter Conditions dialog box, click Select a field or group in the third box.

  10. In the Select a Field or Group dialog box, click the Category field, and then click OK to close all open dialog boxes.

Now that you have connected the second list box to the data and applied the filter, you are ready to test the list boxes to make sure that they work correctly.

Test the list boxes

  1. To test your changes, click Preview on the Standard toolbar, or press CTRL+SHIFT+B.

  2. In the drop-down list box, click Seafood. The standard list box should show a blank line, Crab meat, and Salmon.

    Note: The blank line lets users clear their choice from the list box.

If the list boxes do not work correctly, review the filter for the second list box to make sure that it compares the listBox1 field with the Category field.

Top of section

Connect to an external XML document

In this section, you will learn how to create a data connection to an XML document that contains product and category values. The XML document will be your secondary data source. To create this data connection, you will need to create the XML document, associate the list boxes in the form template with the XML document, and then apply a filter to the second list box so that the list of products changes appropriately.

Before you can associate the form with a secondary data source, you must create the XML document.

Create the XML document

  1. Start Notepad or another text editor.

  2. Copy and paste the following XML data into the text editor:

    <?xml version="1.0" encoding="UTF-8"?>
    <ListBoxData>
         <CategoriesAndProducts>
              <Categories>Seafood</Categories>
              <Products>
                   <Product>Crab meat</Product>
              </Products>
              <Products>
                   <Product>Salmon</Product>
              </Products>
         </CategoriesAndProducts>
         <CategoriesAndProducts>
              <Categories>Dairy</Categories>
              <Products>
                   <Product>Chocolate milk</Product>
              </Products>
              <Products>
                   <Product>Cheddar</Product>
              </Products>
         </CategoriesAndProducts>
    </ListBoxData>

  3. Save the file on your computer as listBoxData.xml.

Now that you have created an XML document, you can connect it to the form template. This will create a secondary data source.

Connect the XML document to the form template

  1. On the Tools menu, click Data Connections.

  2. In the Data Connections dialog box, click Add.

  3. In the Data Connection Wizard, click Receive data, and then click Next.

  4. On the next page of the wizard, click XML document, and then click Next.

  5. On the next page of the wizard, click Browse, select the listBoxData.xml file that you saved to your computer, and then click Open.

  6. On the next page of the wizard, click Include the data as a resource file in the form template or template part, and then click Next.

  7. On the next page of the wizard, in the Enter a name for this data connection box, type listBoxDataXMLFile.

  8. Select the Automatically retrieve data when form is opened check box. This guarantees that the list box data will be available every time that the form is opened, instead of requiring a user to query for it.

  9. Click Finish.

  10. In the Data Connections dialog box, click Close.

Now you are ready to connect the first list box in the form template to the data in the XML document.

Connect the first list box to the data

  1. In the form template, double-click the drop-down list box that you inserted previously.

  2. Click the Data tab.

  3. In the Drop-Down List Box Properties dialog box, under List box entries, click Look up values from an external data source.

  4. In the Data source box, click listBoxDataXMLFile.

  5. Click Select XPath Data Source button next to the Entries box.

  6. In the Select a Field or Group dialog box, in the CategoriesAndProducts group, click the Categories field.

    Note: The yellow background in the Select a Field or Group dialog box indicates that you're working with a secondary data source.

  7. Click OK two times.

Now you will have to connect the second list box to the XML document. While making this data connection, you will want to apply a filter to the second list box. This filter will populate the second list box with the appropriate products, based on the category selected in the first list box. The filter compares the value selected in the first list box (listBox1 field) with the list of categories saved in the form (Category field).

Connect the second list box to the data

  1. In the form template, double-click the standard list box that you inserted previously.

  2. Click the Data tab.

  3. In the List Box Properties dialog box, under List box entries, click Look up values from an external data source.

  4. In the Data source box, click listBoxDataXMLFile.

  5. Click Select XPath Data Source button next to the Entries box.

  6. In the Select a Field or Group dialog box, in the CategoriesAndProducts group, expand the Products group, and then click the Product field.

  7. Click Filter Data.

  8. In the Filter Data dialog box, click Add.

  9. In the Specify Filter Conditions dialog box, click Select a field or group in the first box.

  10. In the Data source box, in the Select a Field or Group dialog box, click Main.

  11. Click listBox1, and then click OK.

  12. In the Specify Filter Conditions dialog box, click Select a field or group in the third box.

  13. In the Data source box, in the Select a Field or Group dialog box, click listBoxDataXMLFile (Secondary), and then click Categories.

  14. Click OK to close all open dialog boxes.

Now that you have connected the second list box to the XML document and applied the filter, you are ready to test the list boxes to make sure that they work correctly.

Test the list boxes

  1. To test your changes, click Preview on the Standard toolbar, or press CTRL+SHIFT+B.

  2. In the drop-down list box, click Seafood. The standard list box should show only a blank line, Crab meat, and Salmon.

    Note: The blank line lets users clear their choice from the list box.

If the list boxes do not work correctly, review the filter for the second list box to make sure that it compares the listBox1 field with the Category field.

Top of section

Connect to a database

In this section, you will learn how to create a data connection to two tables in an Access database. To do this, you associate the list boxes in the form template with the database, and then apply a filter to the second list box so that the list displayed in the second list box changes appropriately based on the value selected in the first list box. For this example, we will assume that the Access database contains two tables called Categories and Products, and that the two tables are related to each other by the CategoryID field, which is used in each table.

Categories table    

CategoryID

CategoryName

1

Seafood

2

Dairy

Products table    

ProductID

CategoryID

ProductName

1

1

Crab meat

2

1

Salmon

3

2

Chocolate Milk

4

2

Cheddar

Connect the form template to the data source

First, you must create a data connection between the form template and the tables in the database. Because you're only displaying the data from the database, instead of modifying it directly, the Access database will be a secondary data source for the form template.

  1. On the Tools menu, click Data Connections.

  2. In the Data Connections dialog box, click Add.

  3. In the Data Connection Wizard, click Receive data, and then click Next.

  4. On the next page of the wizard, click Database (Microsoft SQL Server or Microsoft Office Access only), and then click Next.

  5. On the next page of the wizard, click Select Database, and then browse to and double-click the Access database on your hard disk.

  6. In the Select Table dialog box, click the table that contains the data that you want to show in the first list (in this example, the Categories table), and then click OK. This specifies the table as the primary table for the data connection.

  7. In the wizard, click Add Table.

  8. In the Add Table or Query dialog box, click the table that contains the data that you want to show in the second list (in this example, the Products table), and then click Next.

  9. In the Edit Relationship dialog box, make sure that the connecting fields are correct. In this example, the tables are related to each other by the CategoryID field.

    Tip: If the connecting fields are incorrect, select the fields, and then click Remove Relationship. Then, click Add Relationship, select the correct fields, and then click OK.

  10. Click Finish.

    Note: In this example, the categories table has a one-to-many relationship to the products table, which means that for every one category there can be many products. In a database, this kind of relationship is usually established by using key fields. In this case, the CategoryID key field enables Access to associate the correct set of products with the appropriate category.

  11. In the wizard, review the data source structure. You should see two tables connected to each other: The primary table and the secondary table. To see the individual fields in the table, select the Show table columns check box. Once you are finished reviewing the data structure, click Next.

  12. Clear the Store a copy of the data in the form template check box, and then click Next.

  13. In the Enter a name for this data connection box, on the next page of the wizard, type a name for the data connection.

  14. Select the Automatically retrieve data when form is opened check box.

  15. Click Finish, and then click Close in the Data Connections dialog box.

Connect the first list box to data

Now that you have created a data connection to the secondary data source, you can connect the first list box in the form template to the tables in the database.

  1. In the form template, double-click the drop-down list box that you inserted earlier.

  2. Click the Data tab.

  3. In the Drop-Down List Box Properties dialog box, under List box entries, click Look up values from an external data source.

  4. In the Data source box, make sure that the data connection you created is selected.

  5. Click Select XPath Data Source button next to the Entries box.

  6. In the Select a Field or Group dialog box, click the name of the table that contains the data that you want to show in the drop-down list (in this example, d:Categories ), and then click OK.

    Note: The yellow background in the Select a Field or Group dialog box indicates that you're working with a secondary data source.

  7. Click Select XPath Data Source button next to the Value box.

  8. In the Select a Field or Group dialog box, make sure that the connecting field (in this example, :CategoryID) is selected, and then click OK. This is the value that will be saved when the user makes a selection in the list box.

  9. Click Select XPath Data Source button next to the Display name box.

  10. In the Select a Field or Group dialog box, click the name of the field that you want to display in the drop-down list (in this example, :CategoryName).

  11. Click OK two times.

Connect the second list box to data

Now you will have to connect the second list box to the database. While making this data connection, you will want to apply a filter to the second list box. This filter will populate the second list box with the appropriate values, based on the value that are selected in the first list box.

  1. In the form template, double-click the standard list box.

  2. Click the Data tab.

  3. In the List Box Properties dialog box, under List box entries, click Look up values from an external data source.

  4. In the Data source box, make sure that the data connection you created is selected.

  5. Click Select XPath Data Source button next to the Entries box.

  6. In the Select a Field or Group dialog box, expand the primary group (in this example, d:Categories), and then click the secondary group that corresponds to the table that contains the data that you want displayed in the cascading list box (in this example, Products).

  7. Click Filter Data.

  8. In the Filter Data dialog box, click Add.

  9. In the Specify Filter Conditions dialog box, in the first box , make sure that the connecting field from the secondary table (in this example, Products), is selected.

    Important: If the connecting fields have the same name in both tables, make sure that you select the correct one. The fields from each table are sorted together in the list. Therefore, you can use the surrounding field names to determine which is the correct one. In this example, we select the CategoryID field that appears with the other fields from the Products table.

  10. In the third box, click Select a field or group.

  11. In the Data source box, in the Select a Field or Group dialog box, click Main, click the listBox1 field, and then click OK.

    You have now configured a filter that compares the value selected in the drop-down list box (listBox1 field) with the list of values in the standard list box.

  12. Click OK three times to return to the List Box Properties dialog box.

  13. Click Select XPath Data Source button next to the Value box.

  14. In the Select a Field or Group dialog box, select the value that you want to save when the user makes a selection in the list box. In this example, we want to save the ProductID field for later use. Therefore, we select the :ProductID group, and then click OK.

  15. Click Select XPath Data Source button next to the Display name box.

  16. In the Select a Field or Group dialog box, click the name of the field that you want to show in the list box (in this example, :ProductName).

  17. Click OK two times.

Test the list boxes

Now that you have connected the second list box to the database and applied the filter, you are ready to test the list boxes to make sure that they work correctly.

  1. To test your changes, click Preview on the Standard toolbar, or press CTRL+SHIFT+B.

  2. Select a value in the drop-down list box. The standard list box should show the appropriate values from the secondary table, depending on the connecting field that you chose.

Tip: If the list boxes do not work correctly, review the filter for the second list box to make sure that it compares the listBox1 field with the CategoryID field.

Top of section

Top of Page

at

No comments:

Post a Comment