Programmatically check whether a value exists in a drop-down list box bound to a SharePoint list using an XPath filter

Learn how to use an XPath filter expression on a SharePoint list to look up a value in a secondary data source to which a drop-down list box in InfoPath is bound.

ADVERTISEMENTS

Problem

You have a drop-down list box in InfoPath, which is bound to a SharePoint list that contains names and email addresses of contacts. The values of items in the drop-down list box are bound to the IDs of the contacts and the display names of items in the drop-down list box are bound to the Last Names of the contacts.

You want to be able to type the email address of a contact into a text box on the form template, use this email address to check whether the contact exists in the drop-down list box (so in the SharePoint list that the drop-down list box is bound to), and if the contact exists, have this contact automatically appear as the selected contact in the drop-down list box. If the email address does not exist, no contact must be selected.

Solution

Use an XPath filter expression on the SharePoint list to find the contact whose email address is equal to the given email address. If found, retrieve the ID of this contact and then set the value of the drop-down list box to the ID of the contact.

Discussion

You can accomplish this functionality as follows:

  1. In SharePoint, ensure that you have a Contacts SharePoint list that you can use. This list must contain IDs, names, and email addresses of contacts.
  2. In InfoPath, add a Data Connection to the Contacts SharePoint list. Ensure you select the ID, Last_Name, and E-mail_Address fields from the SharePoint list when creating the data connection.
  3. Add a Drop-Down List Box control to the InfoPath form template and bind it to the data connection for the SharePoint list. Set the @ID of the contact to be the Value of an item in the drop-down list box and set the @Last_Name of the contact to be the Display name of an item in the drop-down list box.
  4. Name the Drop-Down List Box control contact.
  5. Add a Text Box control to the InfoPath form template and name it email.
  6. Right-click the Text Box control and choose Programming and then Changed Event from the context menu.
  7. Add the following C# code to the Changed event handler of the Text Box control:

    // Retrieve the value of the email field
    XPathNavigator root = MainDataSource.CreateNavigator();
    string email = root.SelectSingleNode("//my:email", NamespaceManager).Value;

    // Retrieve a reference to the SharePoint list
    XPathNavigator secDS = DataSources["MyContacts"].CreateNavigator();

    // Search for the email address in the SharePoint list
    XPathNavigator contact = secDS.SelectSingleNode(
    "/dfs:myFields/dfs:dataFields/dfs:MyContacts[@E-mail_Address = '" + email + "']",
    NamespaceManager);

    // Check whether the search returned a result and
    // if it did, retrieve the ID of the contact
    string contactID = string.Empty;
    if (contact != null)
    {
    contactID = contact.SelectSingleNode("@ID", NamespaceManager).Value;
    }

    // Select the contact in the drop-down list box
    root.SelectSingleNode("//my:contact", NamespaceManager).SetValue(contactID);

    Or add the following Visual Basic code to the Changed event handler of the Text Box control:

    ' Retrieve the value of the email field
    Dim root As XPathNavigator = MainDataSource.CreateNavigator()
    Dim email As String = root.SelectSingleNode("//my:email", NamespaceManager).Value

    ' Retrieve a reference to the SharePoint list
    Dim secDS As XPathNavigator = DataSources("MyContacts").CreateNavigator()

    ' Search for the email address in the SharePoint list
    Dim contact As XPathNavigator = secDS.SelectSingleNode( _
    "/dfs:myFields/dfs:dataFields/dfs:MyContacts[@E-mail_Address = '" & _
    email & "']", NamespaceManager)

    ' Check whether the search returned a result and
    ' if it did, retrieve the ID of the contact
    Dim contactID As String = String.Empty
    If contact IsNot Nothing Then
    contactID = contact.SelectSingleNode("@ID", NamespaceManager).Value
    End If

    ' Select the contact in the drop-down list box
    root.SelectSingleNode("//my:contact", NamespaceManager).SetValue(contactID)

    Note: MyContacts in the example code above is the name of the data connection (secondary data source) to a Contacts SharePoint list.

  8. Save your work, build the project, and test the form.

Now whenever you type an email address into the text box and click or tab away from the text box, the email address is looked up in the secondary data source connected to the SharePoint list by filtering the list on that email address, and if it is found, the ID of the contact pertaining to the email address is retrieved and then used to set the selected item in the drop-down list box.

 
 Subscribe for updates via RSS or Email

Copyright: This article may not be used on web sites (whether personal or otherwise), copied, disseminated, altered, printed, published, broadcasted, or reproduced in any way without an expressed written consent of S.Y.M. Wong-A-Ton. The techniques demonstrated in this article may be used within any Microsoft InfoPath project. This article is provided without any warranties. Copyright for this article is non-transferrable and remains with the author, S.Y.M. Wong-A-Ton.

InfoPath 2013 Cookbook: 121 Codeless Recipes for Beginners
InfoPath 2013 Cookbook 2: 121 Codeless Recipes for SharePoint 2013
InfoPath 2010 Cookbook: 101 Codeless Recipes for Beginners
InfoPath 2010 Cookbook 2: 101 Codeless Recipes for SharePoint 2010
InfoPath 2010 Cookbook 3: 101 Code Recipes for C# Developers
InfoPath 2010 Cookbook 5: Integrating InfoPath with Excel and Excel Services


Related InfoPath Articles:

ADVERTISEMENTS

InfoPath 2013 Cookbook: 121 Codeless Recipes for Beginners

InfoPath 2013 Cookbook 2: 121 Codeless Recipes for SharePoint 2013

InfoPath 2010 Cookbook: 101 Codeless Recipes for Beginners

InfoPath 2010 Cookbook 2: 101 Codeless Recipes for SharePoint 2010

InfoPath 2010 Cookbook 3: 101 Code Recipes for C# Developers

InfoPath 2010 Cookbook 4: 101 Code Recipes for VB Developers

InfoPath 2010 Cookbook 5: Integrating InfoPath with Excel and Excel Services