Bending InfoPath and SharePoint to Your Will: Dynamic, Filtered Queries through Data Connections with Minimal Code
[migrated from http://edgedev.blogspot.com]:
It seemed simple enough at first. All I wanted was a way to dynamically pull data from Lists and Document Libaries into InfoPath. Oh, but did I forget to mentioned that I also wanted the data filtered BEFORE it hit InfoPath because the Lists and Libraries might be massive (go figure). Trying several things, it became obvious that I needed a faster/better/easier method than any of the following:
- Using a baked-in InfoPath data connection to a List is easy, but it retrieves ALL List items and leaves filtering to the controls. This is obviously a horrible method when working with large data sets.
- Using custom code-behind on the InfoPath form to query SharePoint via the object model is perhaps the cleanest method, but adds code-bloat, is not very modular, and requires extra maintenance.
- Using a web service data connection from InfoPath to SharePoint web services (like http://[site]/_vti_bin/lists.asmx?op=GetListItem) would be perfect, except that InfoPath does not form the xml in the SOAP request correctly (i.e. the way that SharePoint is expecting) and returns an error. Actually, this boggles my mind. Somebody please correct me if I’m wrong here and/or if there is a clever workaround that I couldn’t find / figure out. This seems like an incredible oversight on Microsoft’s part for 2 products that are supposedly designed to play nice together.
- Since I’m in a K2 blackpearl environment, I also thought about using a SmartObject, which rivals option #2 for cleanest method, but it also adds an additional maintenance tail outside of InfoPath and requires additional wiring inside the form.
Looking for a solution, I stumbled on this article – http://blogs.msdn.com/infopath/archive/2007/01/15/populating-form-data-from-sharepoint-list-views.aspx – at the InfoPath Team blog which formed the basis for a new idea:
To summarize – if you don’t want to have to read through the whole article yourself: This little known feature allows you to pass GUIDs for Lists and Views, along with filter parameters, in the URL querystring to owssvr.dll and dynamically retrieve an XML “results” file. Furthermore, the URL itself can be used as the file location for an “XML Document” data connection in InfoPath.
The main hurdle is that, once the data connection is created, it doesn’t seem on the surface like there’s a way to dynamically update this file location to retrieve a different XML Results Document from a List or Library based on related fields in a form that has been opened. Well, it turns out that the InfoPath object model exposes this property and lets us manually execute the query.
In my particular scenario I wanted to retrieve a group of documents (metadata) from a massive Document Library where a DocumentColumn matched a particular FieldValue in the form opened. Here’s what I did:
- [Optional] Create a View of the List or Doc Lib that contains the specific fields you want retrieved into the Form
- In the Form, add a new Receive > XML Document data connection
- For the “location of the XML data file…” in the wizard use:
http://[server]/[site]/[list]/_vti_bin/owssvr.dll?Cmd=Display&List={LIST_GUID}&XMLDATA=TRUE - [Optional] Include &View={VIEW_GUID} if you want to pull data and columns from a specific view that already exists or was created in step 1.
- Note: the GUIDs in the querystring parameters can be in either format:
a. “{xxxxx-xxxxx-xxxx-…}”
b. “%7BF498875E%2D5A645B%2D…” (as copied directly from SharePoint generated URLs) - Complete the data connection wizard – this will add the unfiltered data connection results schema to the Form (you do want the unfiltered schema). Make sure the following settings are set as you complete the wizard:
a. “Access the data from the specified location”
b. [UNcheck] “Automatically retrieve data when form is opened” - To add the repeating table that will hold the query results, find the xml/rs:data/z:row repeating group in the new data connection and drag it onto the form
- To complete the solution, a little code-behind is necessary (but it’s minimal and a LOT less code than method #2 mentioned at the top). There are probably several ways to do this (like wiring the code to a button or another event), but here’s my basic approach:
- Since the ID will already be stored in the form data by the time the user opens it (in the context of a K2 workflow), I use the form’s Loading event to modify the connection URL and execute the connection.
a. In InfoPath Designer: Tools > Programming > Loading Event
b. In Visual Studio Tools for Office: Insert > Loading Event - The following code will append – at run time – the filter parameter and value from a field in the form to the XML Data Connection location URL and execute the data connection to retrieve the hits:
// we need to instantiate a typed data connection object
// to modify the query and execute the results
FileQueryConnection dc = (FileQueryConnection)(this.DataConnections
[“NameOfXmlDataConnectionCreatedThroughWizard”]);
// next, we append the filtering values from the form field(s)
// to the existing URL already stored in the data connection
// –see notes for description of readNode()
dc.FileLocation += “&FilterField1=DocumentColumnName&FilterValue1=” +
readNode(“/my:myFields/my:FieldInForm”);
//finally, we execute the data connection to return the results
dc.Execute();
Notes:
- readNode is a little helper function I add to all my InfoPath forms so that I can quickly retrieve the value of a node without having to jump through navigator objects every time I want to get something. It looks like this:
public string readNode(string xpath)
{
return MainDataSource.CreateNavigator().SelectSingleNode(xpath,
NamespaceManager).InnerXml.ToString();
} - I think the SharePoint URL trick supports multiple FilterFieldX and FilterValueX parameters, so in theory, you could build pretty sufficient queries using the querystring alone. For example:
“&FilterField1=ColumnName1&FilterValue1=Value1&FilterField2=ColumnName2&FilterValue2=Value2”
This trick might also be leveraged throughout other events in K2 workflows since it returns standard XML and the query results are stored in /xml/rs:data/z:row.
Use this knowledge wisely. And have fun.
November 17th, 2008 at 11:30 am
Thanks for this article. Question though!
My xml data connection looks like this:
https://[my site]/owssvr.dll?Cmd=Display&List=%7B15613EAB%2D60FF%2D48B5%2DB0B8%2D786454738FED%7D&View=%7BD9F96B5F%2D1B6E%2D4AFF%2D82C2%2DC526B7FB5DDE%7&XMLDATA=TRUE
My sharepoint list get updated every day. Meaning a new employee might be added to the list or removed once they are fired!
What I noticed is that my dropdown list in infopath will not get updated based on the sharepoint list. Any idea please????
November 17th, 2008 at 3:17 pm
Interesting question, Nancy. I don’t have a setup in front of me to check these theories but here are some things to think about (if you haven’t already tried them):
– Depending on how you set up the data connection in the first place, there is a checkbox that says something like “retrieve data for this connection when the form launches.” Make sure this is checked.
– You can force the data to refresh by using an event that will happen frequently (a button or something else) and in the actions for the rule in that event, add the “query data connection” action and select the connection to the one for your list.
– If the form is running in offline mode it will not query the list, but only look to the data stored in the template if you selected “store a copy of the data in the form for offline use” when you created the connection.
– Also, check the number of items being returned by the view for that list. The default is 100. InfoPath will only retrieve the max number of items that the List View is set for.
– If you are leaving the form open it will NOT retrieve updates to the List dynamically. The form only updates the dropdown from the data connection when the connection is queried – usually when it first opens. To update the drop-down you have to explicitly query the connection again. Like I mentioned above, you might have to put a rule on a form event that happens predictably frequently to do this.
I hope those ideas help.
December 22nd, 2008 at 10:50 pm
Thanks for the post, it really helped me out. I’m new to working with the code behind forms so I got stuck for a little while because “NameSpaceManager” needs to be “NamespaceManager” (small s). Thanks again!
January 7th, 2009 at 9:25 am
Thanks, Jonathan – sorry about my fat-finger on the case for NamespaceManager. I corrected it in the original post.
January 21st, 2009 at 12:50 pm
I tried this on Infopath 2003 to work around the limitation to show only non-calculated columns. I had to change the code to this (JScript)
var xDoc = Application.ActiveWindow.XDocument;
var dc = xDoc.DataObjects("NameOfTheXMLsource");
dc.QueryAdapter.FileURL += "&FilterField1=myField&FilterValue1=" + xDoc.DOM.selectSingleNode("/my:Fields/my:ValueField").text;
dc.Query();
Unfortunately, the calculated column values start with "string;#" before displaying the desired values, so that the workaround is useless for me. Bad luck.
June 18th, 2009 at 12:36 pm
So I’ve published a form that has this brilliant piece of code 🙂 converted those data connections to udc files. Now running it from the sharepoint website – i am getting the “The remote server returned an error: (401) Unauthorized”. I suspect that it is giving this error when it hits the code that adds filters to the filelocation property and executes the data connection. Any idea on how to get around that? Thanks!
July 2nd, 2009 at 12:01 am
Sorry, I haven’t done much with using udc connections stored in a connection library. Are you running InfoPath in client mode or in web-mode off of form server? That might make a difference. InfoPath Forms running as web pages in form server have to be administratively installed and it’s a pain. But maybe you already considered this? If you’re just running InfoPath client… hmmm… I’ve only had success using the connections that I configured directly in the form. Probably not much help to you though. All the best!
October 13th, 2010 at 4:48 am
You are an absolute *LEGEND*!
Not only is this the perfect solution for my requirements, it’s also opened up a world of potential enhancements for all the other Infopath systems I maintain. THANKYOU!!
(And thanks to René for posting the Jscript variation!)
October 13th, 2010 at 5:03 am
Sweet! You’re totally WELCOME!
February 4th, 2016 at 12:33 pm
Thanks for tip edgelore. I had similar issue and list view limit did resolved the issue. “InfoPath will only retrieve the max number of items that the List View is set for”