As a number of bloggers have noted, InfoPath does not read SharePoint lists quite as well as you would expect it to be able to do, at least in its 2007 incarnation.  The most obvious ways to query a SharePoint list have serious limitations

• Creating a data connection using the SharePoint list wizard only allows lookups on the ID column (great for master->detail lookups but not a lot else)
• Using the SharePoint web service GetListItems simply fails

The approach presented here builds on a Sharepoint Tips And Tricks article which explains these limitations and suggests an alternative approach.

Unlike in the article, in this situation we need to connect to SharePoint to look up a value in a list based on values on the InfoPath form, i.e using a respondent’s age, gender and body fat percentage the system returns a result ranging from “very lean” to “very fat” from a SharePoint list which has all the permutations for gender, body fat and ages (about 350 records).

By using an XML data source InfoPath is able to filter the SharePoint list data on arbitrary fields rather than just ID, in our case gender, age range and body fat.

Create an XML data connection

The process is the same as described in the article with one small exception.

• add data connection
• Choose XML
• paste in the address in the form http://server/infopath/_vti_bin/owssvr.dll?Cmd=Display&List={listGUID}&XMLDATA=TRUE
  • http://server/infopath is the full path to the web where the list is found
  • {listGUID} is the list GUID (which can be found from the List Setting URL)
• choose “Access the data from the specified location”
• Give the data connection a name
• Check “Automatically retrieve data when form is opened” (important!)
  • this is different from the article

The list data is now available to InfoPath, but with a few provisos.

Using the XML Data Connection

Opening http://server/infopath/_vti_bin/owssvr.dll?Cmd=Display&List={listGUID}&XMLDATA=TRUE in your browser will show you how SharePoint has “re-interpreted” the list data

• all the column names are prefixed with “ows_” and use the “internal name” (i.e. no spaces)
• number values are returned to thirteen decimal places
• calculated columns are included (unlike in a SharePoint-type data connection), but the formatting may be different
  • in my case concatenating gender, age range identifier and body fat percentage returned a value  ows_uniq=”string;#Female130″

Bearing this in mind values from the XML data source can be used in the same way as other Secondary Data sources, in the XPath formula editor choose the column to return from within rs:data, z:row and click on the filter button to construct a filter.

Considerations

This approach seems to be well suited to my situation where

• the form is lightly used by a small group of people
• the SharePoint list data is effectively static
• the SharePoint list contents are quite small

If this was not the case the option of writing a small webservice proxy might have been preferable.