SharePoint List Data Connections in InfoPath 2010

Hi, my name is Joey Wiggs and I’m a developer on the InfoPath team. In InfoPath 2010, one of the ways in which we've improved our integration with SharePoint Server 2010 is by providing richer capabilities for connecting to and getting data from SharePoint lists. In this post, I will compare SharePoint list data connections in InfoPath 2007 and InfoPath 2010, and discuss the benefits of using the new 2010 data connection type. I will also outline the steps required to upgrade your InfoPath 2007 SharePoint list data connections to the new and improved version.

About SharePoint List Data Connections

Let’s start by looking at a scenario when you would use a SharePoint list data connection in an InfoPath form.

In Microsoft, when employees run into technical issues, they log a help ticket by filling out an InfoPath form. They start filling out the form by selecting an Problem category from a dropdown list. We could store the category names inside the form but that means that whenever a category is added, deleted or renamed, the form will need to be updated.

Instead, we can store the Category names in a separate SharePoint list and pull this information into the form when users are filling it out. We can do this by adding a SharePoint list data connection to the form. The benefit of using a data connection is that the data can be maintained separately in a single location and the form will always pull in the most up to date information from that location.

Why use the 2010 SharePoint List Data Connection?

In InfoPath 2010, we have extended the functionality of the SharePoint list data connection.

• Query fields are now supported
• Additional field types are supported
• The data pulled from the SharePoint list is no longer tied to the default list view in SharePoint

Query Fields

Setting a query field value allows you to filter the data before it is pulled into the form. SharePoint list connections in InfoPath 2010 now have query fields, allowing you to filter your data and return more scoped results. You can query on a number of different field types, including single lines of text, numbers, and even people and lookups. By filtering your list connections, you can ensure only the data you want is brought into your form. This can also speed up your form connection, as it may pull in less data than it would otherwise.

For example: By setting the “Modified by” query field to the current user (using the username() function), the query will return only those list items that were modified by the current user.

Additional Field Types

In 2007 the list connection could only support simple field types, such as single lines of text, currency fields, and single choices. The new connection now also supports complex field types such as multiple choices, multiple lookups, attachments and person fields.

Sorting results

In previous releases, the number of items returned and the sorting order of said items were determined by the default view for the list in SharePoint. To work around this limitation, form designers had to go to the list settings page in SharePoint and modify the default list view to get the data they wanted into their forms. That’s no longer the case! The new 2010 list data connection will return all of the items in the list, regardless of the default view’s settings. You can also sort the incoming data by a particular field in the data connection wizard when creating a new connection, or modifying an existing one.

How can I get this in my forms?

If you’re designing a new InfoPath 2010 form, you just need to add a SharePoint list data connection and you’ll have this functionality available to you from the start.

The new list features are supported in InfoPath 2010 filler and browser forms only, so if you want to use the new connection in your existing InfoPath 2007 form you will need to upgrade your existing forms to InfoPath 2010. However, once upgraded, InfoPath 2007 clients will not be able to open the form.

To upgrade the data connection, form designers must complete the following steps:

1. Use the data connections dialog to upgrade the list connection
2. Rebind your controls, rules and code
3. Save the form as an InfoPath Web Browser Form Template or InfoPath Filler Form Template

Convert Data Connection to Current Version

For each list data connection that retrieves data in your form, you will need to select it in the data connections dialog and press the “Convert to Current Version” button. You will be prompted if you want to continue. After conversion, you will see an information bar when that connection is selected stating that the data connection is incompatible with the current version of your form. That’s okay; we’ll be upgrading the form to the required version later.

Rebind Controls and Fix up Rules and Code

After converting a data connection, you’ll need to rebind your controls and fix any field or XPath references inside rules and code. When rebinding, you’ll need to rebind the repeating sections to the d:SharePointListItem_RW group, then rebind the controls inside the repeating sections to the correct fields. You can rebind a control by selecting it, then right-clicking and choosing “Change Binding”. This brings up a dialog where you can choose what field or group to bind the control to.

Fixing your rules consists of finding the rule and updating any field references. Field references from the old adapter will look something like “@Title” after you convert your 2007 adapter to 2010. Select the reference, pick the field you want the rule to reference and away you go.

You can tell that the rule actually references the field by how it looks. If the field reference has a namespace, an @ symbol, or a full XPath then that rule won’t work and needs to be fixed.

Save the Form

Finally, you’ll want to save your form as an InfoPath form template (filler or web browser depending on your needs). Note that if you try to save it before converting the data connections, the design checker will prevent you from doing so. 2007 list data connections cannot be in a 2010 form, and 2010 list data connections cannot be in a 2007 form. There’s no mixing and matching, and the design checker will prevent you from saving the form unless they’re correct.

Once you’ve saved your form, you will be able to avail of all the capabilities of the new 2010 SharePoint list data connection. At any time, you can use the data connections dialog to modify the newly upgraded connection, to add new field types, or to use the sort by functionality. You can also set the value of query fields using rules in your form.

Enjoy!

Joey Wiggs

InfoPath Developer

Comments

• Anonymous
  May 07, 2010
  Hello,In 2010 data connection it is not bind to the default view but how can we achieve this in 2007..i have folders in my form library and whenever i make a data connection it just seems to get data when i am the root level of the library..when i go inside a folder the data connection does not work...any idea how can this can be achieved in 2007 infopath.

• Anonymous
  May 07, 2010
  Folders in document libraries are an interesting case, as the folder itself is just another item in the document library. If you try navigating to a document library folder directly in a browser (ie: http://contoso/MyLibrary/MyFolder), you’ll get a 404 Not Found, which is why making a data connection directly to the folder will not work: it doesn’t actually exist. Without the ability to override the default view, what you want is unfortunately not possible in InfoPath 2007. However, you can get all documents using the new SharePoint List connection in InfoPath 2010 thanks to the ability to override the default viewJoey

• Anonymous
  May 13, 2010
  This will open up a lot of opportunities for further integrating SharePoint and infopath.I am running SharePoint Server RTM 2010 and InfoPath 2010.  When I try to create a new ShaerPoint List Form I get the error "This feature requires SharePoint Server 2010 or greater with InfoPath Forms Services enabled.  I have configured InfoPath Forms services in the SP admin console.  Do you know where I can go to confirm that InfoPath Forms Services is indeed running.  

• Anonymous
  May 13, 2010
  I just used your article the other day building a prototype for a client.  A great addendum to this article would be going more in-depth on query fields for the SharePoint data source, including more screen shots, as it is a bit tricky setting these up the first time.Thanks for a great article.

• Anonymous
  May 13, 2010
  Thanks for your comments. We will look into creating another post soon about SharePoint data connection query fields.

• Anonymous
  July 12, 2010
  Joey, if i create a SP List connection using IP 2010, can my users with IP 2007 use the form?TIADean

• Anonymous
  July 13, 2010
  The new list features are supported in InfoPath 2010 filler and browser forms only, so if you want to use the new connection in your existing InfoPath 2007 form you will need to upgrade your existing forms to InfoPath 2010. However, once upgraded, InfoPath 2007 clients will not be able to open the form.

• Anonymous
  August 04, 2010
  Using InfoPath 2010 I want to create a web-based form (containing repeating sections) to submit to a SharePoint list.  The majority of the fields do not need to repeat.If I place the non-repeating fields outside the repeating section then have multiple entries in the repeating section I receive an error when trying to submit.Is there anyway to have a non-repeating section and repeating section and still be able to submit to the list?Thanks!

• Anonymous
  August 06, 2010
  SharePoint lists can’t actually store repeating data in a single item, so check out the Using repeating data in list forms section from our Comparing SharePoint List and Form Library Forms article for some workarounds.

• Anonymous
  March 24, 2011
  You started to talk about how to set up a form for Problem Tracking, so that the user would be able to enter a problem and provide updates, but did not explain how this is done.  I have been able to add items to my SharePoint list, but have not been able to update the list.  Is that possible?

• Anonymous
  March 24, 2011
  The comment has been removed

• Anonymous
  June 16, 2011
  How can i programaticaly show the quick parts in word document using infopath sahre point and ms word

• Anonymous
  July 12, 2011
  Hi,Is Filtering ability work for External COntent Type lists as well?

• Anonymous
  July 27, 2011
  Great article, I has a question about the data that is pulled from a SharePoint list. will that data stay in the older submitted forms if the listed is edited and updated with different values.

• Anonymous
  December 09, 2011
  Hi, if i wanna to get data from form library and save it in custom list ??How can i do that??

• Anonymous
  December 11, 2011
  Hi Ahmed,Can you provide more details around what you currently have and what you are trying to do? For instance, are you using an InfoPath form published to your library? If so, have you "promoted" any fields from that form to be columns in that library? If so, is this the data you are trying to get from your library to a separate list?We need more information around what you have and what you are trying to do before we can provide some guidance.Scott

• Anonymous
  December 20, 2011
  Hello,In 2010 data connection it is not bind to the default view but how can we achieve this in 2007..i have folders in my form library and whenever i make a data connection it just seems to get data when i am the root level of the library..when i go inside a folder the data connection does not work...any idea how can this can be achieved in 2007 infopath.

• Anonymous
  December 20, 2011
  Hi saidi reddy,Take a look at this blog post:Populating form data from SharePoint List Viewsblogs.msdn.com/.../populating-form-data-from-sharepoint-list-views.aspxIn short, you create a "Receive" data connection using the "XML Document" option and your path to the XML document will look like this:http://server/_vti_bin/owssvr.dll?Cmd=Display&List={2A70C9F4-7746-49E3-92DE-CCEB5AD7B3EE}&View={F237EA33-622C-4BC5-B2EC-13379CC8BA14}&XMLDATA=TRUE&noredirect=trueNOTES:1) You will obviously need to change the URL to point to your appropriate site or site/subsite.2) Notice the "View=" part of that connection? This is how you control what SharePoint view the connection uses to pull data.There are additional options you can use with this type of connection (i.e. filters) and this is documented in the above noted blog post.I hope this helps!Scott

• Anonymous
  January 26, 2012
  Hello,I am having a heck of a time setting the query value to a boolean value as I am trying to retrieve a count of items where a flag has been raised.I tried setting the field value to 1, 0, TRUE, FALSE and all kind of variations to no avail. The query is unable to properly evaluate. Could someone give me a hint on this?Thanks!

• Anonymous
  February 25, 2012
  Hi,I am maintaining Master data in SharePoint List and made data connection,I am able to submit form,  But When I rename / modify the SharePoint List data, this data is not updated in already submitted InfoPath form. Any solution for this?When I submit new form, updated master data is populating from list.

• Anonymous
  February 27, 2012
  Hi Swamy,When you setup the data connection to your list, did you leave the option "Automatically retrieve data when the form loads" enabled? If you did not, do you have some type of Rule setup to query for data when thne form loads or are you doing it elsewhere in your form?Keep in mind, once your form is open there is no way for the form to go get the data again (if it were changed) unless you use some type of action to re-query that connection.Scott

• Anonymous
  March 03, 2012
  hiI need help that how to link or create connection between tow list in SharePoint for e.g i have tow lists i want to link them as i could see the second list all columns .regards  

• Anonymous
  June 28, 2012
  Hi Joey,I am building forms with lots (20 plus) connections - is it possible to have the connection sorted in alphabetical order? The list seems to be in order of creation or some random order that slows down the form creation process.If this is not possible in the current release - what are the chances that a service pack could change the ListBox.Sorted property to True?Cheers.Bill.

• Anonymous
  October 11, 2012
  The comment has been removed

• Anonymous
  April 15, 2013
  Really am not sure what you are trying to explaing here..Are you explaning about DataConnection Updgrading ?? If am correct please change your post Title

• Anonymous
  April 23, 2013
  I guess that I am a dummy, but I just can't see how to set the value of a query field that I want to use for a filter. I have a simple parent/child pair of sharepoint lists. The main list of the form is the parent. The parent key is a lookup field in the child list. How do I get the parent key value into the query field of the child list???

• Anonymous
  January 08, 2014
  HiHow can I import data from Microsoft project to info path 2010 ?

• Anonymous
  January 16, 2014
  We are currently using SharePoint 2010 and Office 2010 for our Intranet. We have a InfoPath from a co-hospital and would like to use the same form. We changed the titles and some rules on the form and now we are starting to the following problem.“A view is set up with maybe 10-25 items to view.  The next day only the top 5 are still chosen.  This has happened each day since Monday after we made some changes to the form.”

• Anonymous
  February 05, 2014
  I am able to query data from my sharepoint 2010 list easily in my IP 2010 form.What I cannot do easily is modify the data in the IP form and put it back.Is there an easy way to do that without a lot of coding?.

• Anonymous
  June 05, 2014
  Good information. I have a senerio that I would like some help with. In site permissions I have groups that I want to only view work for their division. I have created forms in lists and within the forms I have a column named division which is populated when the employee completes the form.  How do I tie the group to only view their divisio?

• Anonymous
  November 07, 2014
  Currently we do not have our forms on sharepoint, the user is able to open a new form from their outlook folders and the data connection is email, i am interested in assigning a unique reference number to their request, does this mean i have to put it on sharepoint?  If so will they still be able to submit a new request from their outlook forms folder?

• Anonymous
  March 03, 2015
  Hello,i'm using 2010 SharePoint List Data Connection option to retrieve data from SharePoint List. Could you please tell me whether if i publish this updated form, will that affect the existing submitted form (they all are in SharePoint).thanks

• Anonymous
  April 22, 2015
  Can I set a query field to a range?  I want to bring back values in a list where the value of a column in the list is between 1 and 15

• Anonymous
  June 30, 2015
  Is there a way to look for the @parameters in the requesting url so that it populates input for the user somewhat like you are doing with the list connection?  I saw something in my search for a solution, but it only looked at one value in the url.  Any thoughts?

• Anonymous
  June 30, 2015
  David, I am not following your question - can you provide some additional detail on what you are asking? Thanks, Scott

• Anonymous
  July 01, 2015
  So if the form is a webform and the requesting url is somedomain.com/theform.xsn?x=1&y=2&z=3 and you want to pull x,y,z values into the input then use the second connection to save to a list, how would one go about this?  The last thing i found close was having to write custom code to pull them in, but I was wondering if this has been enhanced since the previous versions.

• Anonymous
  July 01, 2015
  Hi David, Thank you for the clarification - the only way to pull parameters from the URL like that is with code. And if this is a SharePoint list form, then code is not available. Scott