InfoPath – Query Specific SharePoint List Data

Posted on by 140 comments

STOP USING INFOPATH

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.

ch5[147]ch5[160]

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:
    ch5[161]
  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.
    ch5[177]
  4. From the list of fields, put a check box next to Region and sort Ascending.  Click Next.
    ch5[189]
  5. Click next on the offline data screen, and click Finish.
  6. Create another data connection to receive from the other list, States.
    ch5[161]
  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.
    ch5[191]
  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.
    ch5[193]
  10. Place the Region and State fields on the form as drop-down boxes.
    ch5[194]
  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.
    ch5[195]
  12. For the States drop-down box, set it up like this.
    ch5[196]
  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.
    ch5[197]
  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.)
    ch5[198]
  17. For the Value, select the Main data source, and the Region field.
    ch5[199]
    ch5[200]
  18. Click OK.
    ch5[201]
  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.
    ch5[202]
  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.

tagged with , , ,

140 comments

« Older Comments
  • Travis
    February 16, 2016 9:02 pm

    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)

    @brock.travis

    Reply
  • Greg TnNC
    March 8, 2016 9:41 pm

    Laura,

    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?

    Reply
  • WonderLaura
    March 14, 2016 9:14 pm

    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.

    Yuges,
    Wow, that sounds like a whole other blog post. 😉

    Reply
    • Amber
      January 25, 2017 1:17 pm

      Hi Laura — in which power hour did you answer Ginni’s question? Is it the video above (episode 57)? I am having the same issue and would love some help. Thanks!

  • Pingback: Creating Open & Close Dates for a Browser-based InfoPath Form in SharePoint 2010 | Blah-de-blah-blog

  • Winnie
    September 29, 2016 6:25 pm

    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

    Can you please let me know what I’m doing wrong here?

    Thank you!

    Reply
  • Pontus
    November 21, 2016 6:15 am

    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.

    Reply
  • Ruchi
    December 7, 2016 5:22 am

    Hi all,

    My question is, I am giving name in one text box and I want value which is related to name in another text box automatically, whenever name will change that value will automatically change.

    How can I achieve this in Inopath 2013?

    Example: If name is Riya then value Should be : FR [Automatically value should come]
    If name is Araya then value should be : ND [Automatically value should come]

    Thanks In advance.

    Reply
  • Larry Heimendinger
    January 29, 2017 6:43 pm

    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.

    Geaux Tigers

    Reply
  • michael
    March 3, 2017 4:22 am

    Hi Laura,
    I want my repeating table to be submitted and update the list it came from, is it possible?

    Reply
  • Rachael Rushing
    July 20, 2017 9:04 am

    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???

    Reply
  • Richter Belmont
    September 21, 2017 10:50 am

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

    Reply
  • natan
    December 29, 2017 12:46 pm

    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!

    Reply
    • natan
      December 29, 2017 12:50 pm

      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?

  • Heste
    February 1, 2018 1:30 am

    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???

    Reply
  • Raj
    February 1, 2018 4:03 am

    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

    Reply
  • CB
    February 20, 2018 5:11 am

    This is not working for me in my view display of the form. The fields are returning empty?

    Reply
  • Richter Belmont
    April 27, 2018 5:22 am

    Hi, Laura. Is it possible to filter the secondary datasource using “contains”?

    Reply
  • Ashutosh Domle
    June 14, 2018 11:14 pm

    I wish I have seen this. It could have saved my 2 days as I was new to SP & Infopath.

    Reply
  • Sean
    August 9, 2018 1:02 pm

    Wanting to filter data using an xml file, for a Dropdown Field in a repeating table for Infopath 2010. Once an item has been selected in the dropdown list, trying to use rules and conditions to filter it and hide already selected results. Taken from this example.
    http://www.bizsupportonline.net/infopath2003/shrinking-dropdown-list-repeating-table-infopath.htm

    Reply
  • Kim
    August 23, 2018 11:13 am

    Is it possible to filter a dropdown based on the value of a textbox?

    Reply
  • Derek
    January 4, 2019 11:23 am

    Hi Laura,

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

    Best,
    Derek

    Reply
    • WonderLaura
      January 10, 2019 8:53 am

      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.

  • Shmayah
    June 18, 2019 2:29 am

    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

    Reply
  • Anonymous
    June 19, 2019 1:43 am

    Thank you
    I have implemented it under Form Load, in which I defined Historic Items = 0 in the Query fields of the secondary connection.
    It works great.

    Reply
  • Anonymous
    September 30, 2019 7:04 am

    Hi Laura,

    First of all big fan of your work .

    I have a doubt though, is it possible to retrieve all views of an infopath list item . I have an infopath form with 3 views and I want to retrieve each view using Microsoft Flow , is this possible ?

    If yes, can you guide me to the right direction?

    Reply
  • Mike Daniel
    October 13, 2019 7:35 am

    I can duplicate your example with Drop down values. But I cannot figure out how to populate a Text box field with value from the Secondary data source based on the value in another field on the form

    Scenario
    1) Have a request form where user provides a Name (Person/group field).
    2) When value in this field changes, I want to query the secondary data source (List 2) to find a record that matches the Name.
    3) If a match, then I want to populate other fields on the form with some fields from the secondary data source

    I know that once I “query for data” I just need to Set the field values of the form fields…..Just cannot determine where to set the query info and execute it. Any insight would be very appreciated.

    Reply
    • Mike Daniel
      October 13, 2019 7:41 am

      One additional note…

      Since I want to query the Secondary Data Source with the value of a Person field, I assume I need to match using Account ID…but the query field does not break down the Person field any further like the data field does. So how would I do a query match using a Person Field?

  • Hubert "Woody" Wood
    November 21, 2019 2:46 pm

    I have a sharepoint list with several fields, two of them are management category and ID number. Each management category can have multiple ID numbers. I have a drop down for the management category, when selected several other single line text-boxes get populated, but what I also need is a multi-line text-box that can list all the ID number that fall under the selected management category. I’m new to Infopath and everything I’ve tried has gotten me know where. Any ideas?

    Reply
  • Sahira
    December 18, 2019 3:00 am

    Hi Laura,

    Can we edit the data that we query?

    For example, I put in the ID, and it will populate all fields for the item and I want to change the title.

    Can I change it in InfoPath and click a button (Edit) , and it will update the current item?

    Thank you for your help 🙂

    Reply
« Older Comments

Leave a Reply