DevExpertise

InfoPath and SharePoint make a great team when it comes to creating simple electronic forms.  It’s relatively simple to create a robust data-entry form, and is easy to submit an InfoPath form to a form library.  It’s even simple to pull in data that is stored in a SharePoint list.  However, what you may not realize is if a data connection is configured to retrieve SharePoint list items, it only retrieves the items from the first page of the default view.  What does that mean?  Let’s say you have a SharePoint list that contains 500 items, and your default view (the view that is loaded whenever you visit the list) is set to only show 100 items at a time (like it is by default).  When InfoPath executes its query to retrieve the list items, it will only retrieve 100 items.  More specifically, it will only retrieve the first 100 items.  So, if you happen to change the default view’s criteria or sort order, then InfoPath will be pulling in a different set of data! Don’t believe me? Let’s take a look…

To begin, I created a custom list in SharePoint named Widgets, and added 100 items to it.  Next, I created a new view named Sample View in the list, and set that as the list’s default view:

Next, I set the Item Limit to only 7 items per page:

After clicking OK and saving the view, whenever I access my list I’m presenting with the first 7 items in the list:

Now that I have a data source, I create my InfoPath form and set up a data connection to retrieve data from the Widgets list.  I added the results of the data source to a repeating table, and whenever I run my form, I am only presented with the first 7 items (told ya!):

Just to make sure this is the actual behavior and I’m not just hallucinating after a long day, I modified the view and changed the sort order to sort by Title in descending order, and changed the item limit to display 12 items at a time:
 

When I open the form again and the data connection is executed, it retrieves the first 12 items after the new sort has been applied!:

One would think that there would be some type of configuration option in the data connection wizard that asks to retrieve all items, or the results from a specific view, but there isn’t.  If you do need to retrieve all list items, it’s imperative that your list’s default view displays them all, or consider a different data retrieval method such as the built-in or custom web services.

There’s even a KB article that says this behavior is by design: http://support.microsoft.com/kb/892954