Wednesday, March 10, 2004 9:17 PM Jan Tielens

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!

Filed under: ,

Comments

# re: Utilize the Secondary DataSource Data in InfoPath (SharePoint List Example)

Wednesday, June 23, 2004 9:30 AM by Nick Sophocleous

This seems like too much work!

I have a simple XML document with a single repeating section containing only two fields. I want to use both fields within a second form such that theres a drop down list populated by the entries in the first field and have the second fields related entries automatically inserted into a field in the second field.


The fields in the first form are 1. the common names of flowers, 2. Their Latin Names

How easy could it be!

Yours frustratedly
nicksoph

# re: Utilize the Secondary DataSource Data in InfoPath (SharePoint List Example)

Tuesday, August 29, 2006 5:29 PM by Chris Bywaters

I wonder about your solution above.  I can see that this would work if you are not using a browser-enabled form; but is there a way to do this with the browser-enabled form.  I have found one or two examples but they did not work correctly.  If you have any ideas I would be very interested.

# re: Utilize the Secondary DataSource Data in InfoPath (SharePoint List Example)

Tuesday, March 27, 2007 3:02 PM by Robin

Jan,

I would really like to test your example, however, the script editor automatically opens the event in JS.  Can you please send me the above code in JS?

robin.finerfrock@ngiusa.com

Thanks

# re: Utilize the Secondary DataSource Data in InfoPath (SharePoint List Example)

Thursday, July 26, 2007 6:55 PM by M. Lansdaal

Maybe I'm missing something here. It's nice to see the code but couldn't the same thing be done by using either:

1) An expression box that looks up into the secondary datasource using the value of the drop down list selected value.

2) rules on the drop down list which would set the value in the specified text box using the street (or city) data field as appropriate.

# re: Utilize the Secondary DataSource Data in InfoPath (SharePoint List Example)

Wednesday, January 9, 2008 10:39 PM by JCheadle

I am having trouble copying this code and editing.  If possible can you please e-mail the vbscript code?

janet.cheadle@suntrust.com

Thank you for this valuable information.