Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

How to dynamically populate a drop-down list by calling a Web service in InfoPath


View products that this article applies to.

INTRODUCTION

This article describes how to call a Web service to dynamically populate a drop-down list that is based on the selection in a second drop-down list in Microsoft Office InfoPath 2003 and in Microsoft Office InfoPath 2007.


A new data filter feature is added to Microsoft Office InfoPath 2003 and Microsoft Office InfoPath 2007. This new feature lets you use the user interface to specify a value in an input field. Then, that value is passed to a Web service that in turn populates a drop-down list with the results that match the value in the input field. This article also describes how to call a Web service to dynamically populate a drop-down list that is based on the selection in a second drop-down list by using the new data filter feature.

↑ Back to the top


More Information

Use a script to filter data in InfoPath

Create a simple Web service

  1. Start Microsoft Visual Studio .NET.
  2. On the File menu, click New, and then click Project.
  3. In the Project Types list, select Visual Basic Projects.
  4. In the Templates list, select ASP.NET Web Service.
  5. In the Location box, type http://SERVER/PopulateCities, and then click OK.

    Note The placeholder SERVER represents the name of the Web server.
  6. Right-click Service1.asmx.vb, and then click View Code.
  7. Add the following Web service method to the Service1 class.
    <WebMethod()> _
    Public Function GetCities(ByVal sState As String) As String()
    If sState = "FL" Then
    GetCities = New String() {"Coral Gables", "Miami", "Orlando", "Tallahassee"}
    ElseIf sState = "MI" Then
    GetCities = New String() {"Detroit", "Flint", "Lansing", "Livonia"}
    Else
    GetCities = New String() {"Unknown State"}
    End If
    End Function
  8. On the Build menu, click Build Solution.

Create an InfoPath form

  1. Start InfoPath.
  2. On the File menu in InfoPath 2007, click Design a Form Template. On the File menu in InfoPath 2003, click Design a Form.
  3. Add controls to the new form. To do this, follow these steps:
    1. In the Design a Form Template task pane in InfoPath 2007, click Blank, and then click OK. In the Design a Form task pane in InfoPath 2003, click New Blank Form.
    2. In the Design Tasks task pane, click Controls.
    3. In the Insert Controls list, select Drop-Down List Box. A new drop-down list that is named field1 is added to the form.
    4. In Microsoft InfoPath Designer, right-click field1, and then click Drop-Down List Box Properties.
    5. In the Drop-Down List Properties dialog box, click Add.
    6. In the Value box of the Add Choice dialog box, type FL, and then click OK.
    7. In the Drop-Down List Properties dialog box, click Add.
    8. In the Value box of the Add Choice dialog box, type MI, and then click OK.
    9. In the Drop-Down List Box Properties dialog box, click OK.
    10. In the Insert Controls list, click Drop-Down List Box. A new drop-down list that is named field2 is added to the form.
  4. In InfoPath 2007, follow these steps:
    1. On the Tools menu, click Data Connection.
    2. In the Data Connection Wizard, click Create a new connection to, click Receive data, and then click Next.
    In InfoPath 2003, follow these steps:
    1. On the Tools menu, click Secondary Data Sources.
    2. In the Secondary Data Sources dialog box, click Add.
  5. Click Web Service, and then click Next.
  6. In the Location box, type http://<SERVER>/PopulateCities/Service1.asmx?wsdl, and then click Next.
  7. In the Select an operation list, select GetCities, and then click Next.
  8. In the Data Source Setup Wizard dialog box, click Set Value. In the Set Value dialog box, type FL, and then click OK.
  9. In the Data Source Setup Wizard dialog box, click Next, and then click Finish.
  10. In the Secondary Data Sources dialog box, click Close.
  11. In the Editor window, right-click field2, and then click Drop-Down List Box Properties.
  12. In the List box entries list, select Look up in a database, Web service, or file.
  13. Click Select XPath to the right of the Entries box.
  14. Expand all nodes, and then click string. Click OK.
  15. In the Drop-Down List Box Properties dialog box, click OK.

Add a script to query the Web service when the first drop-down list changes

  1. For InfoPath 2007, follow these steps:
    1. In the Designer window, right-click field1.
    2. Click Programming, and then click On After Change Event. Microsoft Script Editor starts.
    For InfoPath 2003, follow these steps:
    1. In the Designer window, right-click field1, and then click Drop-Down List Box Properties.
    2. In the Validation list, click Data Validation.
    3. In the Events list, click OnAfterChange, and then click Edit. Microsoft Script Editor starts.
  2. Locate the following code comment.
    A field change has occurred...
  3. Add the following code sample after the code comment.

    VBScript
    UpdateCities
    XDocument.View.ForceUpdate
    JScript
    UpdateCities();
    XDocument.View.ForceUpdate();
  4. Add the UpdateCities procedure to the script file.

    VBScript
    Sub UpdateCities
    Dim fieldValue
    set fieldValue = XDocument.DOM.selectSingleNode( "/my:myFields/my:field1" )

    'Get a reference to the SDS bound to the Web Service.
    Dim theDataObject
    set theDataObject = XDocument.DataObjects.Item("GetCities")

    'Set the SelectionNamespaces so that you can find the correct field.
    'Note: If the Web service was created with Visual Studio .NET 2003, the xmlns:s0 namespace is
    'http://tempuri.org/PopulateCities/Service1
    theDataObject.DOM.setProperty"SelectionNamespaces", _
    "xmlns:dfs=""http://schemas.microsoft.com/office/infopath/2003/dataFormSolution"" " &_
    "xmlns:s0=""http://tempuri.org/"""

    Dim queryValue
    set queryValue = theDataObject.DOM.selectSingleNode( _
    "/dfs:myFields/dfs:queryFields/s0:GetCities/s0:sState" )

    queryValue.text = fieldValue.text
    theDataObject.Query
    End Sub
    JScript
    function UpdateCities()
    {

    var fieldValue = XDocument.DOM.selectSingleNode( "/my:myFields/my:field1");

    //Get a reference to the SDS bound to the Web Service.
    var theDataObject = XDocument.DataObjects.Item("GetCities");

    //Set the SelectionNamespaces so that you can find the correct field.
    // Note: If the Web service was created with Visual Studio .NET 2003, the xmlns:s0 namespace is
    // http://tempuri.org/PopulateCities/Service1
    theDataObject.DOM.setProperty("SelectionNamespaces",
    'xmlns:dfs="http://schemas.microsoft.com/office/infopath/2003/dataFormSolution" ' +
    'xmlns:s0="http://tempuri.org/"');

    var queryValue = theDataObject.DOM.selectSingleNode(
    "/dfs:myFields/dfs:queryFields/s0:GetCities/s0:sState" );

    queryValue.text = fieldValue.text;
    theDataObject.Query();
    }
  5. Switch back to InfoPath. In the Data Validation dialog box, click OK.
  6. In the Drop-Down List Box Properties dialog box, click OK.
  7. On the Tools menu in InfoPath 2007, click Programming, and then click On Load Event. On the Tools menu in InfoPath 2003, click Script, and then click On Load Event.

    Script Editor starts, and the XDocument_OnLoad event handler is added.
  8. Add the following code sample to the implementation of the XDocument_OnLoad event handler.

    VBScript
    UpdateCities
    JScript
    UpdateCities();
  9. On the File menu, click Save, and then exit Script Editor.

Test the form

  1. On the File menu in InfoPath 2007, point to Preview, and then click Form. On the File menu in InfoPath 2003, point to Preview Form, and then click Default.

    When the form appears, Select appears in the first drop-down list.
  2. Display the list in the second drop-down list. Notice that Unknown State appears as expected.
  3. Click FL, and then click MI in the first drop-down list. Notice that the second drop-down list updates as expected.

Use the Filter Data feature in InfoPath

Create a simple Web service XML data source

  1. Start Microsoft Visual Studio .NET.
  2. On the File menu, click New, and then click Project.
  3. In the Project Types list, select Visual Basic Projects.
  4. In the Templates list, select ASP.NET Web Service.
  5. In the Location box, type http://<SERVER>/PopulateCities/Service1.asmx?wsdl, and then click Next.
  6. In Solution Explorer, right-click PopulateCities, point to Add, and then click Add New Item. The Add New Item dialog box appears.
  7. In the Templates section, click XML File, type Allcities.xml in the Name box, and then click Open.
  8. Replace everything with the following code, and then click Save.
    <?xml version="1.0" encoding="utf-8" ?>
    <States>
    <State value="FL">
    <ArrayOfString>
    <string>Coral Gables</string>
    <string>Miami</string>
    <string>Orlando</string>
    <string>Tallahassee</string>
    </ArrayOfString>
    </State>
    <State value="MI">
    <ArrayOfString>
    <string>Detroit</string>
    <string>Flint</string>
    <string>Lansing</string>
    <string>Livonia</string>
    </ArrayOfString>
    </State>
    <State value="Unknown">
    <ArrayOfString>
    <string>Unknown State</string>
    </ArrayOfString>
    </State>
    </States>
  9. Right-click Service1.asmx, and then click View Code.
  10. Add the following code sample to the top of the code page.
    Imports System.Xml
    Imports System.IO
  11. Add the following Web service method to the Service1 class.
    <WebMethod()> _
    Public Function GetCities2() As System.Xml.XmlDocument
    Dim path As String
    path = Server.MapPath("AllCities.xml")

    Dim doc As New System.Xml.XmlDocument
    doc.Load(path)

    GetCities2 = doc
    End Function
  12. On the Build menu, click Build Solution.

Create an InfoPath form

  1. Start InfoPath.
  2. On the File menu in InfoPath 2007, click Design a Form Template. On the File menu in InfoPath 2003, click Design a Form.
  3. Add controls to the new form. To do this, follow these steps:
    1. In the Design a Form Template task pane in InfoPath 2007, click Blank, and then click OK. In the Design a Form task pane in InfoPath 2003, click New Blank Form.
    2. In the Design Tasks task pane, click Controls.
    3. In the Insert Controls list, select Drop-Down List Box. A new drop-down list that is named field1 is added to the form.
    4. In Microsoft InfoPath Designer, right-click field1, and then click Drop-Down List Box Properties.
    5. In the Drop-Down List Properties dialog box, click Add.
    6. In the Value box of the Add Choice dialog box, type FL, and then click OK.
    7. In the Drop-Down List Properties dialog box, click Add.
    8. In the Value box of the Add Choice dialog box, type MI, and then click OK.
    9. In the Drop-Down List Box Properties dialog box, click OK.
    10. In the Insert Controls list, select Drop-Down List Box. A new drop-down list that is named field2 is added to the form.
  4. On the Tools menu, click Data Connections.
  5. In the Data Connections dialog box, click Add.
  6. In the Data Connection Wizard dialog box, click Receive data, and then click Next.
  7. Click Web service, and then click Next.
  8. In the Location box, type http://<SERVER>/PopulateCities/Service1.asmx?wsdl, and then click Next.
  9. In the Select an operation list, click GetCities2, and then click Next.
  10. In the Data Connection Wizard dialog box, click Finish.
  11. In the Data Connections dialog box, click Close.
  12. In InfoPath Designer, right-click field2, and then click Drop-Down List Box Properties.
  13. In the List box entries list in InfoPath 2007, select Look up values in the form's data source. In the List box entries list in InfoPath 2003, select Look up values in a data connection to a database, Web service, file, or SharePoint library or list.
  14. Click Select XPath to the right of the Entries box. The Select a Field or Group dialog box appears.
  15. Add a filter to the return data. To do this, follow these steps:
    1. Expand All Notes, click String, and then click Filter Data. The Filter Data dialog box appears.
    2. Click Add. The Specify Filter Conditions box appears.
    3. In the first drop-down list, click Select a Field or Group.
    4. In the Data Source section, click GetCities (Secondary).
    5. In the dataFields node, Click Value, and then click OK.
    6. Click is equal to for the second drop-down list.
    7. In the third drop-down list, click Select a Field or Group.
    8. n the Data Source section, click Main.
    9. Click field1, and then click OK.
  16. Click OK to close all open dialog boxes.
  17. Click Save to save the form.

Test the form

  1. On the File menu in InfoPath 2007, point to Preview, and then click Form. On the File menu in InfoPath 2003, point to Preview Form, and then click Default.
  2. Click FL, click MI, and then click Unknown from the first drop-down list. Notice that the second drop-down list updates as expected.

Use the Rules feature in InfoPath

Create a simple Web service

Use the same Web service that is mentioned in the "Use a script to filter data in InfoPath" section.

Create an InfoPath form

  1. Start InfoPath.
  2. On the File menu in InfoPath 2007, click Design a Form Template. On the File menu in InfoPath 2003, click Design a Form.
  3. Add controls to the new form. To do this, follow these steps:
    1. In the Design a Form Template task pane in InfoPath 2007, click Blank and then click OK. In the Design a Form task pane in InfoPath 2003, click New Blank Form.
    2. In the Design Tasks task pane, click Controls.
    3. In the Insert Controls list, select Drop-Down List Box. A new drop-down list that is named field1 is added to the form.
    4. In Microsoft InfoPath Designer, right-click field1, and then click Drop-Down List Box Properties.
    5. In the Drop-Down List Properties dialog box, click Add.
    6. In the Value box of the Add Choice dialog box, type FL, and then click OK.
    7. In the Drop-Down List Properties dialog box, click Add.
    8. In the Value box of the Add Choice dialog box, type MI, and then click OK.
    9. In the Drop-Down List Box Properties dialog box, click OK.
    10. In the Insert Controls list, click Drop-Down List Box. A new drop-down list that is named field2 is added to the form.
  4. On the Tools menu, click Data Connections.
  5. In the Data Connections dialog box, click Add.
  6. In the Data Connection Wizard dialog box, click Receive data, and then click Next.
  7. Click Web service, and then click Next.
  8. In the Location box, type http://<SERVER>/PopulateCities/Service1.asmx?wsdl, and then click Next.
  9. In the Select an operation list, select GetCities, and then click Next.
  10. In the Data Connection Wizard dialog box, click Finish.
  11. In the Data Connections dialog box, click Close.
  12. In InfoPath Designer, right-click field2, and then click Drop-Down List Box Properties.
  13. In the List box entries list in InfoPath 2007, select Look up values in the form's data source. In the List box entries list in InfoPath 2003, select Look up values in a data connection to a database, Web service, file, or SharePoint library or list.
  14. Click Select XPath to the right of the Entries box. The Select a Field or Group dialog box appears.
  15. Expand all nodes, and then click string.
  16. Click OK.
  17. Add rules to filter the return data. To do this, follow these steps:
    1. In the Designer window, right-click the drop-down list that is named field1, and then click Drop-Down List Box Properties.
    2. In the Validation and Rules list, click Rules. The Rules dialog box appears.
    3. Click Add, and then click Add Action.
    4. In the drop-down list in the Action section, select Set a field’s value.
    5. Click Select XPath to the right of the Field box.
    6. Under queryFields in the GetCities (secondary) data source, click the State node, and then click OK.
    7. Click fx to the right of the Value box.
    8. Click Insert Field or Group.
    9. Under myFields in the Main data source, click the field1 node, and then click OK.
    10. Click OK to return to the Rules dialog box.
    11. Click Add, and then click Add Action.
    12. In the drop-down list of the Action section, click Query using a data connection.
    13. In the Data Connection section, click GetCities, and then click OK.
  18. Click OK to close all open dialog boxes.
  19. Click Save to save the form.

Test the form

  1. Start InfoPath.
  2. On the File menu in InfoPath 2007, click Design a Form Template. On the File menu in InfoPath 2003, click Design a Form.
  3. In the first drop-down list, click FL, click MI, and then click Unknown. Notice that the second drop-down list updates as expected.

↑ Back to the top


References

For more information about how to obtain the latest service pack for Microsoft Office, click the following article number to view the article in the Microsoft Knowledge Base:

870924 How to obtain the latest service pack for Office 2003

↑ Back to the top


Keywords: kboffice12yes, kbfreshness2006, kbsweptsoltax, kbhowto, kbwebservices, kb

↑ Back to the top

Article Info
Article ID : 826994
Revision : 3
Created on : 4/18/2018
Published on : 4/19/2018
Exists online : False
Views : 257