InfoPath – Query Specific SharePoint List Data

With InfoPath and SharePoint 2010, one of the awesome new capabilities is that you can query specific SharePoint list data from data connections that receive from SharePoint lists and libraries.  This applies to all versions of SharePoint 2010 and Office 365, and is compatible with BOTH client based and browser-based forms.

Back in the old days in SharePoint 2007, when you created a data connection in InfoPath to receive SharePoint list data, you couldn’t specify what data to query.  Whichever list you were retrieving, by default, you would get whatever is shown in the default view of that list.  You could do filters on the information once it was queried, but there wasn’t much that could be done as far as a specific query.

10/17/2014 Update: Here, I recorded a video on how to do this in SharePoint 2013 or Office 365.

Here’s how you do it in InfoPath 2010 with SharePoint 2010.  the example will be a list of regions and a list of states per region, with a cascading drop-down.  This method is MUCH more efficient because you can query only what you need and not the whole list.  Setup:

• There are two lists: Regions and States, both custom lists.
• Regions has one field called Region, just a text field.
• States has two fields:  Region is a lookup to the region field in the list of regions, and State a text field.  The states list is much longer than the regions.

In my InfoPath form called “New Employees”, the goal will be to have a cascading drop-down so that when a region is selected, the list of states in that region will be shown in the next drop-down box.  This can be a SharePoint list (customized with InfoPath) or a form library form.

In the “new employee” example InfoPath form, create a region field and a state field, both as text.  I’ll just focus on these two fields in the steps.

1. 1.  In InfoPath, create a data connection to receive data from the regions list.  Click this button on the Data tab:
   
2. For the SharePoint site details, use the URL of the SharePoint site where the regions and states lists are.  Click Next.
3. From the list of available lists and libraries, select the Regions list.  Click Next.
   
4. From the list of fields, put a check box next to Region and sort Ascending.  Click Next.
   
5. Click next on the offline data screen, and click Finish.
6. Create another data connection to receive from the other list, States.
   
7. Repeat steps 1 through 3, selecting the States list this time.  From the list of fields, put checkboxes next to Region and State, and sort by state ascending.  Click Next.
   
8. Click Next on the offline query screen.
9. This is important, UNCHECK the box to automatically retrieve the data when the form is opened.  In general, it is a best practice to only query data from other lists when it is needed, and not every time the form is opened.  This step is one of the keys to the reason that this method is going to be more efficient.  Click Finish.
   
10. Place the Region and State fields on the form as drop-down boxes.
    
11. For the Regions drop-down box, set it up like this. Be sure to set the value as ID and the Display name as title.
    
12. For the States drop-down box, set it up like this.
    
13. In the Fields pane on the right, click Advanced View.  let’s take a look at the structure so you’ll understand it.  Change the Fields drop-down to States.  See, there are the query fields, and then there are the data fields.  The data fields will contain the list of states that is retrieved.
    
14. Now, the trick here will be to query the states list after a region is selected.  So, a rule will be created on the Regions drop-down, which gets triggered right when a value is selected.  Double-click the Regions box, and click the Manage Rules button in the ribbon.
15. In the rules pane, click the New button and choose Action.  For the first action, click the Add button and choose Set a Field’s Value.
16. For the FIELD, Set the Query value of the Region field in the States list.  (We want to query that list where Region = the region they selected in the Region drop-down box.)
    
17. For the Value, select the Main data source, and the Region field.
    
    
18. Click OK.
    
19. In the Rules pane, click the Add button to add another action.  Pick the action called Query for Data.
20. Pick States and click OK.
    
21. Preview the form.  Pick a Region from the drop-down and then choose the States drop-down and notice that it will only show the states in that region.

Again, this is much more efficient than querying the entirety of all data connections each time the form is opened.  The States data connection was set to NOT retrieve data each time the form is opened, and then when it does retrieve data, only a small subset is queried.