InfoPath – Query Specific SharePoint List Data

129-ch5202_6928A654.png

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.

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

    @brock.travis

    Like

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

    Like

  • 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. 😉

    Like

    • 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!

      Like

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

  • 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!

    Like

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

    Like

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

    Like

  • 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

    Like

Leave a reply or question

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s