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. In InfoPath, create a data connection to receive data from the regions list. Click this button on the Data tab:

For the SharePoint site details, use the URL of the SharePoint site where the regions and states lists are. Click Next.

From the list of available lists and libraries, select the Regions list. Click Next.

From the list of fields, put a check box next to Region and sort Ascending. Click Next.

Click next on the offline data screen, and click Finish.

Create another data connection to receive from the other list, States.

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.

Click Next on the offline query screen.

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.

Place the Region and State fields on the form as drop-down boxes.

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.

For the States drop-down box, set it up like this.

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.

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.

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.

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.)

For the Value, select the Main data source, and the Region field.

Click OK.

In the Rules pane, click the Add button to add another action. Pick the action called Query for Data.

Pick States and click OK.

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.

Like this:

Related

132 comments

How would this work if for example a State fell into two different Regions? I have a situation where this is a requirement, and I don’t want to have to duplicate all the work for each State that has multiple Regions.

(Technically I will be doing this 3 or 4 levels deep, and the number of duplicates will start to grow exponentially)

I am having the same issues as Greg Appelt; where a list involves more than 5k items. Is there a way to have the Data Connection or the data query just pull for the applicable choice instead of trying to load 5000+ items?

Sonia,
To get around situations like that, I usually try to avoid using any of those task actions in workflows because there’s no way to make the items related with a lookup field with most of them. I usually just use the “create list item” action to create tasks, that way you can set the value of a lookup field that you create.

Niky,
I’ve never been able to get the initiation form to get data from the current item. Frustrating.

Ginni,
That has to do with the display name versus the value of the item you’re picking in the drop-down. When it’s a lookup field, SharePoint stores that data as the unique ID of that item, and not the text. So when you look at it later, you just see the number. The answer to this is too complicated to try to type in this comment, I’m going to do it in Power Hour.

Christy,
Well, people will inherently only see data that they have permissions to, so if you want just particular items in the list to show depending on who’s looking at it, you may have to do item level permissions on the items in that list.

Keith,
Yes.

Ven…,
Yes? Sure.

Greg,
I think your method of using the separate lists is the best idea. I don’t know of a way around the threshold issue.

Kyle,
List or library? If it’s a list, you can double check that the column setting in the list settings is set to allow multiple selections. Then if you do change that setting, open the form back up in design mode and it will recognize the changes.

Mary,
It sounds like you can just use “set field value” to populate those other fields in the task list.

Hi,
I am using InfoPath 2010 and I ran into an issue while going through your steps.

I have two lists: Apps and Groups. My cascading drop downs would be select Apps –> populate the Groups available for that Apps.

The problem here is that the Groups list on SharePoint is +5000 items and when I select from the Apps drop down, it gives me the below error:

——————————————————————————————————————————————————–
The query cannot be run for the following DataObject: Access – Groups & SubGroups
The SOAP response indicates that an error occurred on the server:

Exception of type ‘Microsoft.SharePoint.SoapServer.SoapServerException’ was thrown.
The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator.Operations that exceed the list view threshold are allowed in the following time window defined by the administrator: <br/><b>Daily, from 10:00:00 PM to 1:00:00 AM . </b>0x80070024
——————————————————————————————————————————————————–

I thought that by setting value in the Group list query field would eliminate this issue because it would only send queried items to InfoPath and I am sure the query results are less than 5000 items

Hi Laura, I was hoping that your instructions would solve an issue I have related to auto populating fields. But I was not successful, and I realise my use case is slightly different.

Maybe you know how to solve this, or help to point me in the right direction:

I’m building a “Site Access Request form” for SharePoint. I have a SP list of sites, where each item has a site name (text), site description (text) and a site owner (person, group field).

The form in InfoPath should later be available in SP and its submitted data should be sent second custom list, where a workflow will take over and send an approve/decline email to the site owner along with the access request details.

What I have so far is a dropdown box getting the site name from each item in the list. What I want is to have a site owner field automatically populated based on the site name dropdown choice. This could be any type of field (control) as long as it is read only. I want the user to see which site owner that will receive the request. And if I can get this to work I would like to do the same for the site description.

I’m trying to set a rule for the site name field to set the field value of the site owner feed but the field is not populated. Unlike your example this is just a single list, so I’m thinking it should be pretty straight forward?

Ps. I can not use the default SP Access request feature due to many reasons.

Thank you very much in advance for any small hint on how to solve this.

How can a copy of the slides be downloaded? Some of the video went by too quickly. Very pertinent and helpful but have yet to try it. One specific question: how would I query for a row in a list and determine it does not exist? Have a rule that is based on not present? As an example, users have to submit data at each day’s business close for their respective activities but don’t want it to be done more than once for any given day. List would have location, month/day/year and other data, so I want to check first to see if it is already there and if not be able to create it; if it is, display an error on the form.

I can get the query to work and generate on the project list like i want, but i keep getting the “only positive integers allowed” statement when i view. However when I change the value to “ID” and the Display Name to the “Project List” the error goes away, but then will only show the ID for the project, not the name in the project box. Thoughts???

Hi, Laura. How to do this query specific sharepoint list data programmatically? can you give some code snippets? this way if we can pass a filter to the query field then we can solve the thresholds, i just dont know how. 🙂

Thanks for the post. it is very helpful. I got it to work, but I have a situation that is maybe a little different, and I can’t quite figure it out. so rather than regions and states, I am working with review disciplines and reviewers. Each project can have up to 32 review disciplines, and in each discipline there is one reviewer. So I want to do 2things. 1) prepopulate the review disciplines field with the review categories, rather than having that be a pull down. 2) add a checkbox, where clicking on the checkbox would activate the query based on the prepopulated value of the review disciplines field. any help would be fantastic!

Thanks for the post. it is very helpful. I got it to work, but I have a situation that is maybe a little different, and I can’t quite figure it out. so rather than regions and states, I am working with review disciplines and reviewers. Each project can have up to 32 review disciplines, and in each discipline there is one reviewer. So I want to do 2things. 1) prepopulate the review disciplines field with the review categories, rather than having that be a pull down. 2) add a checkbox, where clicking on the checkbox would activate the query based on the prepopulated value of the review disciplines field. any help would be fantastic!

sorry for the duplication:
a new points/question: do I need a separate data connection for each review discipline/reviewer name pair?

Great post! Got me far into my project, but I have the same type of question than Natan. I need to be able to select more than one region and populate all states for all regions selected. Please help???

Hi Laura,
Great tutorial! I have a question, can we query into another list using contains() instead of assigning values into query fields?
what I am trying to achieve is, in the other SharePoint list, a string type field contains several names(comma separated). I just want to pull the records in which the string type field contains a particular name (along with other names of course). Clearly, I need a contains() kind of method in here. Do I have any options to achieve this?
Thank you

Thanks for the great post. I’m new-ish to Infopath and working with an old version of SP 2010 when I had most of my experience in ’16 and SP Online at my previous employer.

I’ve created a form that does everything I want, save for one thing:

As users type in data – in this case let’s say a patient’s first name and last name, it will auto populate the form with the data if we’ve ever seen this child before. Because my data isn’t static (like the 50 states in your example), I can’t possibly pre-populate a SP list/library with patients we don’t know exist yet.

Is there a way to have the Infopath form pull data from the list/library as it’s populated? Meaning, if the patient is new, the form just gets filled out and submitted. If we’ve seen Billy before, we filled out the form and know he has a specific diagnosis – the form will auto populate the information we have previously submitted based on his name/DOB, etc.?

You can fill in multiple query fields before running the query, so fill in the query fields with the name, DOB, etc, and then run the query and it will return record(s) if that patient is already in your list. Then, you’d have to “set field value” for each of the individual fields in your form, using the data that the query returns.

Hi Laura,
Great post, I have a slightly different situation:
I would like to have a secondary data source connection from a SharePoint list with the filter applied at the initial load (When opening the form or with a rule using Form Load.
My main reason is that my list contains 3,000 but the form can not load more than 1,600 items.
My list contains historic items which I don’t need in the form, so I don’t want to load them (I want to apply a filter on the column name “Historic Items” to load only where Historic Items = 0).
Is it possible to do this?
If so, how?
(Maybe with REST web service)
Thank you