Utilize the Secondary DataSource Data in InfoPath (SharePoint List Example)
The InfoPath Service Pack1 adds some nice features to InfoPath, there a few that I really like. One of them is the ability to use a SharePoint list as a data source. For example if you have a contact list in SharePoint containing information about your customers, you can easily create a drop-down list box on an InfoPath form, that automatically gets filled with a list of your customers. That’s already nice, but in some cases you may want to fill other fields on your form, based on the selected item of your drop-down list box. For example: you want to display the address of the selected customer on your InfoPath form. This involves a little bit of plumbing code which can be done either in .NET code (by using the InfoPath 2003 Toolkit for Visual Studio.NET) or in Java/VB-script. So because not everyone lives in a managed world today, let’s do it in good old VB-script! (sometime I’m a little bit old fashioned…)
First of all create a new InfoPath Form. Then put a drop-down list box on your form, double-click on it so we can set the properties. In the “List box entries” section of the properties window, select “Look up values in a data connection to a database, Web Service, file or SharePoint library or list”. When you click the Add-button a wizard shows up which allows you to easily choose a data source. First select of course “SharePoint library or list”, then enter the URL of the SharePoint site that contains the list you want to use. The wizard will retrieve all the lists and libraries on your site, in our example we’ll choose the Contacts list. In the next window you can choose which fields of that list you want to use, make sure you’ve selected the Address and City fields and finish the wizard. Further on the properties window you can choose the XPath expression for the Entries property. By clicking the button right of the Entries textbox, you can easily navigate to the Contacts node. For the Value and Display name properties you can choose something meaningful, for example the Company field. Now if you preview this form, the drop-down list box already contains a list of your customers!
What we want to do is after the user has selected a customer from the drop-down list box, is display the address and city for that user. Add two text boxes to the form and name them Address and City for example. Double-click again on the drop-down list box (so the Properties shows up again) and click the “Data Validation…” button. On the button of the Data Validation window, select the OnAfterChange event and click the Edit button. Microsoft Script Editor shows up and there’s automatically an event handler generated for the OnAfterChange event of the drop-down list box. Now enter following code after the last remark lines:
' Get to the DOM
Dim doc
Set doc = XDocument.DataObjects("Contacts").DOM
' Add the namespaces for the SelectSingleNodes
doc.setProperty _
"SelectionNamespaces","xmlns:dfs=""http://schemas.microsoft.com/office/infopath/2003/dataFormSolution"" " & _
"xmlns:dsf=""http://schemas.microsoft.com/office/infopath/2003/dataFormSolution"" "
' Get the selected customer text
Dim selectedCustomer
selectedCustomer = XDocument.DOM.selectSingleNode("/my:myFields/my:Customer").text
' Get the selected node
Dim selectedNode
Set selectedNode = _
doc.selectSingleNode("/dfs:myFields/dfs:dataFields/dsf:Contacts[@Company='" & _
selectedCustomer & "']")
' Set the textbox texts
XDocument.DOM.selectSingleNode("/my:myFields/my:Address").text = _
selectedNode.attributes.getNamedItem("Address").text
XDocument.DOM.selectSingleNode("/my:myFields/my:City").text = _
selectedNode.attributes.getNamedItem("City").text
Close the Microsoft Script Editor or save your script manually and preview the InfoPath form. When you select a Customer from the list, automatically the corresponding values for the Address and the City fields will be showed!
