Since the Service Pack 1 Preview of InfoPath it is really simple to fill for example a drop-down list box on a InfoPath form with data that is stored in a SharePoint list. But it’s not quite straight forward to fill that drop-down list box with all the users of a SharePoint site. Although this is quite easy to accomplish in a custom SharePoint list by creating a Lookup column and indicating that you want to get User Information. But (almost) nothing is impossible with InfoPath, so let’s give it a try!
First of all: how can we get a hold of a list with all the users of a specific SharePoint site? Well it happens to be that SharePoint exposes quite a lot of functionality through a web services layer; to be more specific: we can use the Users and Groups Service. In this service are several methods that you can use to retrieve all kinds of information about users and groups, we’ll use the GetUserCollectionFromSite method. So far so good, we know where to get our data, now let’s create the InfoPath form. The solution I’ll describe here will be using some custom code which can be written in script or in .NET code. This time, let’s make use of the InfoPath 2003 Toolkit for Visual Studio.NET so we can write .NET code.
Thus let’s fire up Visual Studio .NET and choose to create a new project based on the InfoPath Form Template. Drop a drop-down list box on your InfoPath form and open the Properties window. In the “List box entries” section choose to “Look up values in a data connection to a database, Web service, file or SharePoint library or list”. At this point the InfoPath form doesn’t have any data connections, hence click the Add button to create a new one. We want to get our data from the GetUserCollectionFromSite web method, so select the “Web service” option in the wizard. For the WSDL location use a URL formatted as follows: http://Server_Name/[Site_Name/]_vti_bin/UserGroup.asmx?wsdl (for example: http://myServer/SharePoint/_vti_bin/UserGroup.asmx?wsdl). Next the wizard will show you a list containing all the web methods available on the web service, select the GetUserCollectionFromSite method. Finally you can give the data connection a name, let’s call it “Users”. When you click the Select XPath button (right to the Entries text box), the troubles begin… As you can see on the screenshot, the web service return value does not contain a repeating group or field, which is necessary to be able to fill a list.
Why is that? Well if we take a look at the GetUserCollectionFromSite documentation we can see that the return value is of the System.Xml.XmlNode type. That means that you’ll receive XML of which the WSDL doesn’t describe how it will look like, so InfoPath can’t show your either. Although the documentation shows an example of how the XML will look like, it’s quite a bummer. The problem would be solved if you could type the XPath value in the Entries text box, but unfortunately this text box is read-only. From now on, there are several possible solutions:
- Manually edit the XSLT of the InfoPath form and fill in the correct XPath value.
- Create a wrapper web service that exposes the GetUserCollectionFromSite information strongly typed.
- Use the little trick I’ll show you in a moment. :-)
The third solution will make use of a dummy data connection that has the same structure as the return value of the web method. This will give you the advantage of using InfoPath functionality to select the correct repeating group. Afterwards the contents of that dummy data connection are replaced by return value of the web method. So let’s work further on our InfoPath form but first download this XML file to your harddisk; it contains a fictitious return value of the GetUserCollectionFromSite web method. Click the Add (data connection) button to add a new one data connection: receive data from an XML document (point to the file you’ve just downloaded) and name it “DummyUsers”. Un-check the “Automatically retrieve data when the form is opened” check box and choose No if InfoPath suggest to file to your form. Then open the Properties window of the drop-down list box control again, select the “Look up values in a data connection to a database, Web service, file or SharePoint library or list” option, and choose to DummyUsers data connection. Then if you choose the “Select XPath” button you’ll see that you can navigate to a repeating group! Also in the Properties window, you need to specify the Value and “Display name” field, for example choose the LoginName field for both ones.
We’re not finished yet, we need to replace the data of the DummyUsers data connection with the data of the Users data connection (which is automatically fetched through the web service). We must do this in the OnLoad event of the InfoPath form, so in the Tools/Programming menu click the “On Load Event” menu item. Visual Studio.NET will pop up, and a the OnLoad method is created for you. You need only to add one line:
// The following function handler is created by Microsoft Office InfoPath. Do not
// modify the type or number of arguments.
public void OnLoad(DocReturnEvent e)
// Write your code here.
That’s it! If you preview the InfoPath form, first the data of the Users data connection is fetched automatically. Then the OnLoad event is triggered, so we replace the data of the DummyUser data connection, and finally the form is showed and the drop-down list box contains all the login names of the users of the SharePoint site. You can use this little trick also for fetching other data through the SharePoint web services. If you want to easily create dummy XML documents you can use the WebServiceStudio 2.0 that can construct SOAP request messages.