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.
![ch5[147]](https://i0.wp.com/wonderlaura.com/wp-content/uploads/2016/03/129-ch5147_3b3b539c.png?resize=237%2C318 "ch5[147]")
![ch5[160]](https://i0.wp.com/wonderlaura.com/wp-content/uploads/2016/03/129-ch5160_3b3b539c.png?resize=503%2C437 "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. In InfoPath, create a data connection to receive data from the regions list. Click this button on the Data tab:
![ch5[161]](https://i0.wp.com/wonderlaura.com/wp-content/uploads/2016/03/129-ch5161_3b3b539c.png?resize=88%2C76 "ch5[161]")
- 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.
![ch5[177]](https://i0.wp.com/wonderlaura.com/wp-content/uploads/2016/03/129-ch5177_3b3b539c.png?resize=379%2C366 "ch5[177]")
- From the list of fields, put a check box next to Region and sort Ascending. Click Next.
![ch5[189]](https://i0.wp.com/wonderlaura.com/wp-content/uploads/2016/03/129-ch5189_3b3b539c.png?resize=320%2C374 "ch5[189]")
- Click next on the offline data screen, and click Finish.
- Create another data connection to receive from the other list, States.
![ch5[161]](https://i0.wp.com/wonderlaura.com/wp-content/uploads/2016/03/129-ch5161_1_3b3b539c.png?resize=88%2C76 "ch5[161]")
- 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]](https://i0.wp.com/wonderlaura.com/wp-content/uploads/2016/03/129-ch5191_3b3b539c.png?resize=320%2C374 "ch5[191]")
- 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.
![ch5[193]](https://i0.wp.com/wonderlaura.com/wp-content/uploads/2016/03/129-ch5193_3b3b539c.png?resize=468%2C186 "ch5[193]")
- Place the Region and State fields on the form as drop-down boxes.
![ch5[194]](https://i0.wp.com/wonderlaura.com/wp-content/uploads/2016/03/129-ch5194_3b3b539c.png?resize=508%2C51 "ch5[194]")
- 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]](https://i0.wp.com/wonderlaura.com/wp-content/uploads/2016/03/129-ch5195_6928a654.png?resize=366%2C238 "ch5[195]")
- For the States drop-down box, set it up like this.
![ch5[196]](https://i0.wp.com/wonderlaura.com/wp-content/uploads/2016/03/129-ch5196_6928a654.png?resize=366%2C238 "ch5[196]")
- 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]](https://i0.wp.com/wonderlaura.com/wp-content/uploads/2016/03/129-ch5197_6928a654.png?resize=236%2C292 "ch5[197]")
- 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.)
![ch5[198]](https://i0.wp.com/wonderlaura.com/wp-content/uploads/2016/03/129-ch5198_6928a654.png?resize=314%2C201 "ch5[198]")
- For the Value, select the Main data source, and the Region field.
![ch5[199]](https://i0.wp.com/wonderlaura.com/wp-content/uploads/2016/03/129-ch5199_6928a654.png?resize=309%2C75 "ch5[199]")
![ch5[200]](https://i0.wp.com/wonderlaura.com/wp-content/uploads/2016/03/129-ch5200_6928a654.png?resize=309%2C125 "ch5[200]")
- Click OK.
![ch5[201]](https://i0.wp.com/wonderlaura.com/wp-content/uploads/2016/03/129-ch5201_6928a654.png?resize=481%2C286 "ch5[201]")
- In the Rules pane, click the Add button to add another action. Pick the action called Query for Data.
- Pick States and click OK.
![ch5[202]](https://i0.wp.com/wonderlaura.com/wp-content/uploads/2016/03/129-ch5202_6928a654.png?resize=481%2C286 "ch5[202]")
- 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.
Laura- does this work if you have a multi-select for the second list?
Great article – Thanks! Could you cascade over multiple lists, like, say, Country, Region, City? Is there a limit to the number of data connections you can use in an InfoPath form? Thanks! P
PaulF, No, you’d have to just pull in the whole list and then do a filter rule (formatting rule to hide) on the list, for “contains”. Paul P, Yes. No, no limit that I’ve ever encountered. I’ve created over 100 before in one form.
Thanks for the reply Laura 🙂 One other thing: If I wanted to do this, but in reverse, i.e. selecting a state and have the region auto-pick, could that be done, without changing the lists? I have an equivalent to the Regions list lookup, but not everyone uses them, hence doesn’t always know which one to pick, but the States equivalent list is fully known, so they want to look “back” to the region after selecting the State. Or can I simply use the Region field in the States List in another field somehow? Thanks again! P
I have a list active projects with a project ID called APLookUp. I then have a list of all my projects called AllProjects. I have a form that I use to poll the active project list of which one gets selected by the user. I then want the balance of fields to use that ID to fill in the remaining fields for the user. I can’t seem to get your concept to work for me. Would/should the process you describe work exactly the same for auto fillin?
Paul, Sure, you can do it that way also. Create a rule on the state dropdown that will set the query value on the other list’s data connection, and then query the other data connection for just the items that match that state. Greg, For “auto fillin”, after you query to get the specific information, you’d have to use the “set field value” to set your fields to the values from the list you just queried.
Hi Laura, You were helping me on the msdn forums with autofilling some text boxes with the data the users inputted the month before. http://social.msdn.microsoft.com/Forums/en-US/sharepoint2010customization/thread/e5e656cc-8096-4ac3-b533-2eec0bf8e04c I’m stuck, I can’t seem to get just the data from the user inputted in the people picker. Could you clarify a couple things for me on the thread? I think I’m close, but missing something that’s causing it to not work.
I have a project in which I’m need to have users select from a column of IDs from a problems list. Once they select the ID they need to see the directions to fix vulnerabilities from another list. In the problems list the columns are ID, Category, problem description. The List containing the Fix contains Category field, and fix description field. How do I map the ID field and/or category field of the problem list to display the description field in the Fixes List?
George, Create a new field called Category, as a text field. Create an action rule on the drop-down box where they’re selecting the ID. Here are the actions in order: 1. Set a field’s value. Set the new Category field to the value of the category where the ID matches the ID that they just selected in the drop-down box. Create a new rule on the new category field (which doesn’t have to be showing on your form at all, by the way) 1. set a field’s value. Set the QUERY value for the Category in the Fix list to the currently selected value of the Category field. 2. Query the fix list. Your fix list can be showing just as a repeating table on the form if you’d like.
Laura I love your blog. I use it nearly every week for some great SP info. From this example, (using SP 2010) I am trying to get my States lookup List to autopopulate the Regions lookup column using a Workflow from the Regions List. I am using Create item in a list in the WF, but I get a coercian error. Any thoughts?
Michael, Usually a coercian error means that the value of a field is the wrong type of value than the field needs. Try this: Use the create item action, and only use one of the fields at a time. Try running the workflow once with each field one at a time, and then that should help you find the culprit.
Laura, your suggestion created an extra item in my list, but helped me to narrow down what was and what wasn’t working. I updated my create Item action (correctly) and viola! worked like a charm. Now both items are updated correctly in the same item. Thanks!
I’ve tried this and it works, but I noticed that after submitting this, the Region value in the list becomes a numeric number (1, 2, etc). I’m assuming this is the ID or the region. How do you get around this? I’ve tried to tweak the value in the field properties and it screws up the query connection.
Matt, You can create another field in that case. You’d have to have one field (the drop-down) that stores the value (ID) and another would have to store the Name of it. So, create another field, you can call it RegionName, click to select the Region drop-down box, and create an action rule: Set a field’s value: Field: RegionName Value: (select the data connection) select the name of Region, and do a filter where ID is equal to your field in the Main data source called Region. Then, the new RegionName field is the one you can promote to SharePoint as a column.
It works! But I noticed that if you leave Region dropdown as blank, the State dropdown has all the states returned from the list. Is there any way to prevent this?
Hello Laura, Thank You for the post, i am a regular visitor. I have a scenario if you can help me with that. I have a list name Inventory where i have following columns: Inventory ID: ID Device Category:dropdown(medical equipment, electronics, imaging equipment) Device Name: dropdown( ………) Device Category and Device Name are cascading dropdowns. Other columns are: Serial Number:number Pixel :number storage size:number zoom:number warranty info:date assigned to:people picker inventory status:dropdown Is there a way where if i select Imaging Device from Device Category dropdown will enable columns Pixel, Zoom. IF selected medical equipment or electronics disabled or greyed out.
How do you populate a dropdown list from a text box using infopath web forms.
Jason, A quick way to do that would be to create a formatting rule on the states list which disables the control if the region field is blank. Darren, Kind of a vague question. Not sure what you mean.
Hello, this was very helpful for me. I have created a drop-down that I have populating a text box based on the selection in the drop-down. The problem I am having is if I change the selection, the value in the text box is not updating.
Connie,
You’d need to put a rule on the actual drop-down control, so that it does the query and changes the value, being triggered by the value of the drop-down being changed.
I am not sure how to create a rule to do a query to change the text box value triggered by the value of the drop-down. I have a rule that sets the text box value when a selection is made, I am just not entirely sure how I get it to trigger based on the value of the selection made. The result I get is always the first value in the list.
I just figured it out, thanks for all your help.
Is there a way to query a one to many relationship? I want to query Request # and return all Defect IDs that match that Request #
Example
Request # | Defect ID
1 20
1 45
2 6
3 5
3 18
3 22
4 16
5 10
Is there a limitation to how many property promotions are created from an InfoPath 2010 form?
I have a form with 600 + fields that I need populated into a sharepoint list using property promotion. Things have been working great and now, when I publish my form, it says it cannot update list or library.
Thank you for a response in advance!
Hi Laura,
Thanks for the information so far.
But I am having the following issue.
I have created 4 lists (a,b,c and d)
List a is the starting point. Just one text column.
List b has 2 colums, text field and a lookup column (from list a text column)
With the information I got from you, I was able to get the correct 2 drop down controls values in my form.
But now I want to do the following.
List c has 2 columns, text field and a lookup column (from list b text column)
Now I want to have in my form a 3rd drop down control, depending on the values I have in drop down control 1 and 2.
But so far I did not succeed.
Can you tell me how I can manage this?
Thanks in advance.
Yeah Chris,
At step 16, you’d pick the request # query field.
Hi Patti,
Yeah wow that’s an insane amount of fields. I thought the limit was around 60 fields, but I guess it’s 600.
Henk,
Create an action rule on that second drop-down box that will set a query field value and run a query for the values that need to display in the 3rd box, and so on.
Laura:
PaulF, No, you’d have to just pull in the whole list and then do a filter rule (formatting rule to hide) on the list, for “contains”.
If you pull the whole list, the performance will be very slow to load (with thousands of items). Do you have any suggestion to resolve this problems. I meet the trouble with this.
Thank for your Great article.
Tom,
Are you saying you want to show the whole list, but you don’t want to show the whole list because of performance? If you’re worried about performance, then I guess just stick to the original solution and dont’ show the whole list? Am I missing something?
i need to fetch the data on particular time of the day from different sheet..right now it is fetching the data at 12:00 AM sinc the date changes i want the data to be fetched at 7:00AM
Hi Laura: my english not is perfect sorry..in the step 12 when configure the value y display to title.
When add new item show error why only accept number (ID) and when change in the value to ID is correct save but in the form edit y view form show number ID, i want show display value and not ID value
Diddi,
I recommend just doing that in a workflow. Dates in InfoPath with formulas are quite a beast.
Felix,
for the display can you just select the title, and for the value, select the ID? Maybe I don’t understand the problem.
Hi Laura –
I have an issue working with a large list (8000+ items). I’m using your cascading dropdown menus (populated by a different list) for the user to select 3 values. I then use these values to populate the query fields of my large list then run the query. The list results are filtered down to 3-10 rows using the query fields. I then display the results in a repeating table. All was working well until the list grew from 6000 to 8000 items this week, which now exceeds the list view threshold of 7200. I have partitioned the data into folders (based on Region) and indexed the columns used for filters. One of the Regions contains over 5000 items and InfoPath throws an error (“exceeds list view threshold”) when I query anything in that Region, even though I’ve used the filters. If I remove enough records to get the list under the threshold, all is good. I don’t understand why I get this error since I’m filtering the list using the query fields. Any suggestions how to fix this?
I have been able to resolve the problem by creating a new column that contains the 3 filters contatenated together, then indexed that column, essentially creating a compound index. But I still don’t understand why the query fields didn’t trim the list down, so any light you can shed on that would be great! (Sorry for the multiple posts above – not sure why that’s happening!)
Hi Cynthia,
The only time I’ve seen that not work properly is if a field was blank in the database, I think. I recommend taking a close look at the records returned and see which ones don’t match the query that was sent, then you’d be able to further troubleshoot
Laura,
I found the answer to my question in the comments in the answer I inserted below, but could you clarify something for me? Am I supposed to create the RegionName field in the InfoPath form itself so the end user will see two RegionName fields? The drop down and the field with the set value? Or is there a way to just do this in the list I’m sending it to?
Thank you!!!
“Matt, You can create another field in that case. You’d have to have one field (the drop-down) that stores the value (ID) and another would have to store the Name of it. So, create another field, you can call it RegionName, click to select the Region drop-down box, and create an action rule: Set a field’s value: Field: RegionName Value: (select the data connection) select the name of Region, and do a filter where ID is equal to your field in the Main data source called Region. Then, the new RegionName field is the one you can promote to SharePoint as a column.Laura Rogers on 11/11/2011 7:01 PM”
Hi Laura,
the sharepoint list I need to query contains a person type field. I need to find out whether the current user is a member of that list. How can I query such a list?
Thanks in advance for your answer,
Marcel
PS: I tried the following: I created a person field in my InfoPath form, populated it according to chapter 9 of your book and tried to use that person field to query the sharepoint list but that didn’t work. My next idea was to extract the AccountId string from the person field into a calculated field within the sharepoint list and query that but it seems one cannot use a person type field for calculated fields in a sharepoint list. Now I’m stuck.
I have been unable to find a solution to this scenario: I have a drop down control based on List A. When a user selects an item in that list I want it to then query List B and bring up an editable list of those matching items. It seems similar to cascading drop downs but not exactly. This is similar to a 1 to many db relationship. Anyone have any ideas or helpful sites.
Hi Laura,
I’ve enjoyed the book and love the blog.
I tried the concepts in this article, and they works in the form preview in the designer, however, the filtered drop-down does not show anything when I publish it on SharePoint Online.
The two lists and the forms library are on the same site.
Thanks
Hi Laura
I am following this but its not working for a data connection that queries a list over the 5000 item limit. Any tips on what I am doing wrong or is it just impossible to lookup to a large list.
Hi Laura
Great web, i’m writting because i have a proble, maybe you can help me a little, i’m traying to filter a repeating table by people /group (just people) withouth code, had you do this?
Bye bye.
Laura – I suspect that my problem is very simple – but I can’t seem to get my head around this problem. I have a SharePoint 2010 list – I have a product key field that I need to load as a drop-down, and have a user select a unique product key. When that key is selected, I need to have fields in the InfoPath form populated from columns from the SharePoint list that are part of the list item associated with the product key. I can’t get this to work. The best I’ve managed is to get the product keys into the drop down – but my rules (I added actions to kick off after the drop-down field changes) don’t seem to work. I only get columns from the very first record in the SharePoint list. No matter how many times I then change the selected product key, the other InfoPath fields never change. This has to be operator error – but I can’t figure it out. Thank you, in advance, for responding.
Beth,
There is one Region field in the InfoPath form, and it stores the value of whatever you select in the drop-down box. Then, the query action rule is triggered off of that field being changed.
Marcel,
The AccountId in a person field has the syntax domain\username. The userName() function in the form has the syntax of just username. So create a field called CurrentUser, and create a form load rule that will set that field’s value every time the form is opened. My domain name is CONTOSO, so I set this field’s value to concat(“CONTOSO\”,userName())
Then, after that on form load. set the query field (for your list with the people field in it) of the AccountId field to the value of your CurrentUser. Then perform the query. The results returned should be just any rows which contain the currently logged in user in that people field.
Steve,
You can’t have an editable list of matching items in here, unless you want to just embed them in the form.
Suzi,
That’s really frustrating, and I haven’t ever seen a good solution for that issue.
Hi Laura,
Would you be able to explain something for me. In my sharepoint list I have 2 custom columns. 1= Username using people selection and the 2nd is a number column. how can I get my form to select the user in the list to also populate the number field? so basically the user selects there name and it pre-populates the the number field?
Many thanks and happy new year
Ben
Does this concept work with queries from database versus a SharePoint List?
Laura, Thanks for the great article ! Can we enable the user to choose multiple states for any selected region ? In other words can we have Multi-select Checkboxes for States instead of a dropdown ?
Thanks for your help in advance.
I have used your approach several times now with great success, but I have run into a problem with my latest form.
Here’s the setup: a user enters in customer info in order to log a transaction. Once the info is entered, the user clicks a button to search an old list for previous transactions. They want to search by Last Name but the old list, which is huge, only has a Full Name field.
How do I set up the query to pull only those records where form:LastName is contained by OldList:FullName?
Thanks,
Alison
I have followed all the steps and i am getting the cascading dropdown working.But when i submit the form, IDs get stored in the SharePoint list instead of name(titles).
What could be done to store names instead of IDs in the list from cascaded dropdowns?
Kindly help..
Ben,
I’m going to write a blog post about that. Stay tuned.
Doreen,
For a database query, I usually get a developer to write a web service. In the web service, they’ll need to create whatever query fields you need.
Andy,
That sounds kinda tricky, and wouldn’t work with this particular type of solution.
Alison,
Queries will only take the exact query text, and you can’t do partial. You’d have to just bring in the whole list as a data connection, and then maybe do a filter on the data that’s already there.
Prachi,
In the drop-down box properties, it gives you the ability to set the value and the display as two different things. Just change what you want the value to be.
paulfaultner, Andy
Here is the link for cascading checkboxes
http://amarenderpeddamalku.blogspot.com/2013/07/sharepoint-infopath-2010-cascading_2.html
Thanks Laura here’s an article explaining how you can get filtered values from SharePoint list column in InfoPath text field
http://www.sharepointbreak.com/2012/12/06/get-filtered-values-from-sharepoint-list-column-in-infopath-text-field/
Hi Laura,
Do you know if it is possible to use the functionality of a SharePoint List form in a standard form? Somehow move the code, or get the list form to accept a query string parameter?
Thanks,
Sara
Hi Laura
I think this is similar to Sara’s query above. Is there anyway to use similar functionality but without using an InfoPath form?
I am having similar issue to other people. I have my form and drop downs working correctly, however, in the list view I am seeing the IDs of what would be your region field. The fields in the form view show correctly. I need the list view to show the title of the field and not the ID.
I do have the value of the region set to d:ID and the display name of the field set to d:Title.
Anyone have any ideas on what I am missing?
Sara and Ruth,
No, that is not possible. It has to be a form library form. You can use an ASPX page to do this and not use InfoPath at all. I’ve got a whole bunch of other blog posts that teach you how to pass parameters to aspx forms http://www.wonderlaura.com/_layouts/OSSSearchResults.aspx?k=query%20string&cs=This%20List&u=http%3A%2F%2Fwww.wonderlaura.com%2FLists%2FPosts
Hi Laura,
I have tried and failed to do nearly exactly what you have described here. I have a custom list ‘List A’ with two lookup fields in it. 1-‘Sub unit’ and, 2-‘Sub unit business area’. Lists 1 and 2 reside on the same site as the List A. List 2 is linked to list 1 by a lookup to the sub unit name column, so all the business areas reference one of the available business units.
In List A’s form I want a user to select their sub unit and then the sub unit business area dropdown only shows the relevent options.
Do i need to add two new fields to my main List A form and start from scratch or can i amend the two existing lookup fields to behave in this way?
Thanks
Hi
Is It possible to Redirect to media library for displaying particular Product video using info path
Hi Laura! Great blog!
I have a question, my state and region is working fine. But when I fill up another field (‘Approver’ field using the Person/Group Picker) in the same form as with the state and region and I click the ‘Check Names’ button, the value that I input for the State field is refreshed or changed. Please give me an idea for a work-around to solve this. Would really appreciate it. Thanks!
Hi Laura! Awesome blog!
The Region and State is working fine in SharePoint Online. But when an additional field “Approver” is filled up after the 2 above, and when I click the “Check Names” button of the Person/Group Picker of the Approver’s filled. The value that I have selected for the state is refreshed and changed.
Can you recommend some workaround for this. Would really help me a lot!
Hi Laura! I’m able to figure out the solution to my query above! Thanks anyway!
Robin,
Your case is very specific, so let me just show you how to test to make it work for you: Drag all of your data connection fields to the form (all of the query fields AND all of the data fields)
For each data connection you have, but a button on your form that queries that data connection.
This way, you can see how to set query field values and perform queries, and see what results come back. This way you’ll know what you want your rules to end up doing when you automate all of this.
Hi Laura, Thank you for this article.
I have implemented same kind for Area, Zone & Location in my form. However When a user select empty value in Area dropdown, all Zones appearing in Zones dropdown. Similarly if a user selects a empty value, all locations appearing in Locations dropdown. The empty value is coming by default when I bind data connection to a dropdown. Please give me a resolution for this problem.
Laura,
I’m attempting something like this, but for a scheduling system. So, there is a list of Events with the usual data (start/end times and a location). Note that this is a LIST, not a Calendar, because I need to use logic in the creation form which only InfoPath will allow (and you can’t customize a Calendar’s forms in InfoPath). The location field is a lookup to another “Locations” list.
What I need is a way to query the Events list for items occurring at the same times so that the location drop-down only shows locations that are not in use. I’ve used SP2010’s Group Calendars for this type of thing in the past, but, again, since I need to use InfoPath…
I know there’s got to be a way to do this, but I’m just not seeing it yet. Any help would be greatly appreciated.
Chuchendra,
If the query field is blank, and you run the query on a list, it will return everything, since you haven’t set it to return a set of data with a specific value.
Chad,
That’s a tricky one. I don’t think that the InfoPath query method is going to be a good way to accomplish it, because it’s going to look for exact values, and not a greater than or less than.
You’d have to do something with maybe a data view web part and a query parameter, with the parameter and filter on the web part existing inside the web part’s filter.
Hi Laura,
this is fantastic but i have a question if thats ok, i have a list with 4 columns and 23000 rows and i would like to do what you did, would i just need to do as you did but adding additional lists for the extra 2 columns?
Darren
Laura: There is a Connie in this comment section that has the exact same problem as me…a drop down that doesn’t “refresh” the other text boxes when a new value in the drop down is selected. The text boxes just keep displaying the first set of values in the queried list. If I remove the query data rule, the drop down and accompanying text boxes work just fine. Connie states that she has solved the problem, but doesn’t say how. If you are still responding to posts to this, could you give me some suggestions?
Hi Melissa,
The trick to do a refresh should just be to create a rule on the drop-down on which you are changing the value. Therefore, as soon as you select a different value, the rule should:
1. Set a query field value.
2. Do the query
Darren,
I’ve seen it not work correctly with lists that large, but yes your idea does sound correct in theory.
Hi Laura,
I have created a Infopath form(2010) using the Sharepoint List template and have published it. But when I open the form in the web page, form always open as a new record. I tried to put a filter as specified by you above to the form bit it dows not work. Due to this it always creates a new record in the list when I submit instead of updating the specific record whcih I want.
Can you please help me.
Hi Laura, thanks for taking the time to answer all the questions.
I have created a infopath 2010 Form, that populates a series of fields with 4 data connections. 3 xml Files and a list.
The field that starts everything gets is value from “username()” Than without rules, (the only rules I have is to query data, when a field change) I just use a formula to get the value I want Ex.: NIB[UX = Title].. all are field Names. My problem its that the form its taking many time to open… to try to solve that, I decided to create the rules above, its better but it takes same time, when a field change its value( 30 to 40 seconds) the list and xml files, all have 801 records only, all only have 2 columns… I´m lost, I really need your help.
Hi Laura,
My dropdowns work great, thank you.
My 1stdropdown has Company Names and 2nd dropdown has Departments. Some Companies only have 1 Dept. Is there a way that I can make the Department dropdown with a only single entry set as “selected” so the user doesn’t have to make the selection?
Thank you,
Hai laura this help alot in my form, but unfortunately when i try to view it in Sharepoint list it will not show in display view but only in edit view please help me..
email me at ladylove605@live.com
Laura, hi there,
apologies for posting on such an old thread but I’ve just tried to follow the steps above and I can get my form to retrieve data the first time I change my picklist. However if I try to change the picklist a second time I always get a correlation ID error.
If I completely refresh the page then I can select one item from the pick list and my form loads fine. But again if I try and change the picklist a second time I get the error.
I know this doesn’t give much to go on but would you have any idea why this query always fails when changing the picklist for a second time?
Thanks a lot,
Martin.
Martin,
Here, I recorded a new video on how to do this. Hopefully this will help: https://www.youtube.com/watch?v=gWP61g3HvR0&list=UU_h2DyAAptRfkSsyRUTfh9w
Vidhu,
Not sure what you mean by “open the form in the web page”. Are you dealing with a list form, or a form library, and is this question related to doing a query as described in this post?
Pedro, I don’t understand your question. See if my recorded video helps you?
Phyllis,
You can do a “count” function of the number of records returned in the data portion of that data connection. Then, you could do a rule that if count = 1, then set the department field’s value to the value of the department field in the one record that was returned.
Filah, if you set the form’s options to let you use 2 different views for edit and display, then you’d have to go put your list on both of those views.
I am using SharePoint 2010 and InfoPath 2010. I have created a list form in IP and I have 2 drop down boxes in the form that reference 2 different SharePoint lists. I have done the steps above to ‘relate’ the lists so they auto populate the corresponding text fields correctly but they both are exhibiting the following behaviors:
1.Before anything is selected from the drop down list, the corresponding text box is already pre-filled with the data for the first item in the drop down lists. So, the drop down list is blank (from the blank ‘default’ item in the drop down list). How do I fix that so the corresponding text fields are blank until an option is selected from the drop down box?
2.Once a selection is made in the drop down box, the correct information populates in the text box however, if I selected the wrong choice in the drop down list and I want to go back and change it to the right choice, the only options listed in the drop down box are the default blank option and the incorrect one that I chose. Now, if I select the blank option in the drop down box, the drop down list repopulates with all the options but the corresponding text field defaults back to the issue I described in #1 above.
What am I doing wrong? In this state, the form feels clunky and I don’t really want to publish it for use.
Thank you,
Tracy
Hi Laura,
Currently I have the following (XPath) InfoPath 2010 challenge:
I have a list (say Vegetables), which I use in a multiple lookup column (usedVegetables) in another list (say Recipies). Now I have a InfoPath form where I first select a vegetable (returning item ID), for which I want to get a filtered dropdown (usedVegatables contains itemID select vegatable) of recipies using this vegitable.
This works as long as I have less ten 10 vegatables, as usedVegetables seems to return a concatinated string of the selected ids (e.g. 235 for veg_2, veg_3 and veg_5). However the scenario of more than 10 vegetables in the multiselect poses the challenge. If I want to find recipies for veg_1 (ID=1) and I have a recipie with veg_2 & veg_10, it will show this one as well in the recipie dropdown because ‘210’ does contain ‘1’ as well. I’ve tried to resolve this by using a double Eval() to get a seperating ; in between the values, but without success. When putting the full dataconnection in a repeating section/table, it does show each multiselect item seperately.
So, how can I filter my recipie dropdown to only show relevant recipies for a selected vegetable?
As I expect this may involve some advanced XPath, my 2nd question would be if there is a good reference document on XPath (1.0) as used by SharePoint 2010, preferably with SharePoint related examples?
Thank you.
Hello Laura,
My form is querying correctly from a SP list and populating 3 fields, the problem is after I submit the form to the library, all of the fields are blank including the field where I entered the querying value.
Can you think of something that may be causing this?
Thanks and happy 2015!
Hi Laura,
Thanks so much! This was an awesome tutorial and helped me with a problem I was working on. Now, I have an additional field that need to be queried, but there are so many variables, I can’t even imagine how to do it.
I have two lists. First I have the site names and the second list is sampler types. There are over 200 sites. Each site uses a varying array of the samplers. Nothing is standard, unfortunately. I want to be able to get the filtered list of just the samplers that the particular site uses when I choose the site, however I can’t even imagine how since there are so many and I don’t think the lookup field used here would work?
If you could steer me in the right direction, I would be so grateful!
Hi Laura,
This is a wonderful article for all those who are perplexed in querying data from InfoPath forms. I was successful in querying the data within the SharePoint list. However, once I save the form the queried data is missing, when I go back to view/edit the form, the data is missing in all the query fields. Am I doing something wrong? Please suggest. Thanks!
thank you, managed to do a lookup and populate two text fields with data returned by the query by ID.
Tracy,
Use two different data connections for your two different drop-downs, that way you don’t have to worry about them showing data already or showing the wrong data.
Jean-Pierre, this isn’t going to work for a “contains” with a field with multiple selections in it, which explains why your result is a bit clunky. This is only for “equals”.
Aza,
Drag all of your fields and all of your query fields to a blank view of your form, to test. It sounds like you’re filling in the query field but not filling in an actual field in your form. Look at steps 14-20. I’m filling in a form field which then sets a query field. I’m not directly filling in the query field. Make sure you’re setting an actual value in a field in your main form after the query is being run, and you’re not just purely using data from the query source.
Bess, this solution is really only for “this field” equals “this value” and doesn’t work with wildcards or “contains”. So this may not be the solution for you.
Jay, see my answer for Aza.
I am trying to look up an employee name from an employee number but I am not getting it to work. Is there something obvious I am not doing?
It wasn’t working on my real form so I have built a highly simplified test set up.
I am using InfoPath2010.
One list, “GBEmployees”, 20,000 rows, three columns, GivenName (e.g. Guy), Surname (e.g. Boswell), EmployeeID (unique values E0000001 to E0020000), imported from MS-Excel.
My form has two fields, EmployeeID and EmployeeName.
I have created a receive data connection that doesn’t automatically retrieve data, “GBEmployeesReceive”.
I have two text boxes linked to the two fields.
I have set up a rule on the EmployeeID field to run when updated. Three actions. Set the query value of GBEmployeesReceive – EmployeeID to the value of the field EmployeeID. Query the data connection. Set the field EmployeeName to the data value of GBEmployeesReceive -GivenName.
Form published OK and opens OK. When I enter an EmployeeID I get the error message, “The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator.”
As far as I can see I am querying by a unique value so should only be returning one row in the query.
Please can you help?
@guyboswellrvs
Laura,
This is an excellent post. Thanks for it!!
I found answer to a question I have been tinkering with on your response to Matt. I am a little confused when you mentioned to apply filter. When you say ” Set a field’s value: Field: RegionName Value: (select the data connection) select the name of Region”. Did you mean to select the Field as “RegionName Value” or the name of the Region from “Regions” data connection list ??
I selected field as “RegionName value” because it is the field I need to set ( makes sense). Now, what about the ID?? Wher should I pick up this ID from, is the ID from the Regions(data connection) query field or data field? I am lost here.. Please explain. When I select the ID from the “Regions data connection query field”, it throws me an error I cannot apply filter to the currently selected field.
Your reponse to Matt:
“Matt, You can create another field in that case. You’d have to have one field (the drop-down) that stores the value (ID) and another would have to store the Name of it. So, create another field, you can call it RegionName, click to select the Region drop-down box, and create an action rule: Set a field’s value: Field: RegionName Value: (select the data connection) select the name of Region, and do a filter where ID is equal to your field in the Main data source called Region. Then, the new RegionName field is the one you can promote to SharePoint as a column.Laura Rogers on 11/11/2011 7:01 PM”
Laura,
I figured where I went wrong. I was able to promote the drop down list value as value instead of ID by pushing the value to another field.
Thanks again for the wonderful post!
Hi
I have the same issue that a person commented “I tried the concepts in this article, and they works in the form preview in the designer, however, the filtered drop-down does not show anything when I publish it on SharePoint Online.
The two lists and the forms library are on the same site. ”
I don’t know what is wrong
Is this possible with a datetime picker as the filter?
I’m customizing a form used in “Collect Data” action of a workflow. Is it possible to include columns from Sharepoint that only relate to the row the form is acting on?
Hello Laura.
I have been able to follow the video you have to get most of this to work, but not the last part that I am wanting which is the ability to change the Category.
I have two lists: System & Category
on my InfoPath from, I am using System as a drop down that then looks at Category and pulls in the Category assigned to the system. this works perfectly until I add the next step.
On the Category list, I have a column called AssignTo. What I want to be able to do is when a category is selected, look up who it should be assigned to (people picker). this all works fine until I want to change the category on the edit form. when I click on the category drop down I only see the original category selected and a blank space. if I click on the blank space the list will refresh and I can pick another category and the AssignTo will reassign. but how do I get the blank space to not appear and have the category list appear from the start.
Guy, I’ve heard of other people having this issue, but I do not think there is a workaround. I’ve never personally seen it happen.
Jay,
Whew, I’m glad you figured it out!
Elena,
I’m not sure why that would happen. You can test by creating an empty view that just has your query fields dragged on there, and your data fields dragged on there as a repeating table. Then, stick a button on there that does a query to the data connection. Try filling in the query fields (in the browser) and see what happens when you click to query. It should pull in the matching data.
Laura,
No, it’s not.
Dan,
No, and that is a very antiquated workflow action that I don’t recommend using because it is so problematic to work with or implement.
Christy,
You’d need to have a separate data connection for each drop-down for that, so that the list stays filtered to what you selected, and the filters after that don’t affect it.
Thank you for taking the time to answer Laura. I went on to work on other forms and when I came back to the issue it just worked!! I think it is something to do with running SharePoint as a cloud application where we don’t have control of settings – we are in the hands of the host company. Thanks anyhow 🙂
Hi Laura, I’ve used your blog from time to time, Thank you its been a great resource. I’ve got my lookup to another list working within SharePoint but I need to render the URL.
To be more precise, we set up a tasks list. On this task list we’d like the Assigned To person to be able to have a link back to the original submitted request because it has attachments they will have to review.
We are able to pull the original request with a second data connection but we can’t get the URL to come up. We’ve tried a few other methods we found by googling but essentially we are stuck.
Do you have any suggestions?
Thanks so much!
Help for beginner
Hi. I have started to work with InfoPath. I edit start form for Nintex Workflow 2013 in InfoPath. I need to see in start form data from current Item. If you now solution could you say me step by step?
Hi Laura,
Great article, It really my day.
I am struck in a problem after the form is submitted to Sharepoint List. I have two dropwdowns BU and Sub-BU. Sub-Bu will get the data when we have select some value from BU. This is perfectly done. I selected the value in Sub-BU and submit the form.
When I open the list item in Edit mode then Sub-BU dropdown shows only the saved records’s ID (no the Display value) and no other values in the dropdown even if that dropdown have more than that.
But, for BU dropdown it works fine. only in the case of cascading dropdown.
Regards,
Ginni.
Hello Laura. Is it possible to add an item to the drop down list/options that only a specific group can see? thanks
Hi Laura. Does this process work exactly the same way in Infopath 2013 and SP 2013 On Prem? I know you linked to a video of how to do it in 2013. I tried the blog post because it’s easier to follow and you used examples that more closely resemble what I’m trying to do. Thanks for all your great blogs and videos.
Laura,
Does the same solution work when I try to populate a text box on the InfoPath form with concatenated values from a Sharepoint list column, filtered on a seperate field from the InfoPath form?
Laura, I’m having a SharePoint list size limit issue when opening my form (IP form created from SP list). If my list has less than 5000 items, the data loads fine. My data connection is filtering based on another field’s value (say the user inputs a State, all related counties are available in a pull-down.) As soon as my list exceeds 5000 rows, “The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator.”
I’ve tried xml file instead, still get the error. I’ve tried BCS, but the syntax does not allow for the tag to be used for List connection types. And I can’t use SQL due to corporate restrictions.
Is there any other method to get the SP list data without preloading all 5000+ rows? My only option right now is to break my data down into multiple SP lists and using form logic to decide which data connection to open.
Greg
Hi Laura, I am having an issue. When I click on the State dropdown list is having a delay showing me the States of the previous Region selected. Click on any state, then click again on the Dropdownlist State, then it show me the correct States.
It is difficult to me to explain it correctly in words.
Do you have any idea to solve it?
Hi Laura,
So my question is related to SPO and InfoPath 13. I am trying to query a list where one field has country, and based on that selection will have a second field with a multi selection control. My data connections appear to be set up “correctly.”
I am able to look at the preview and make a selection and I do see my multi selection showing. However when published to SP, the multi selection field shows to be blank. Is there anything I am missing?
I can change that field to be a drop down field in info path and it seems to work correctly. Yet it doesn’t appear to be showing in SP with a multi selection field.
Thank you in advance.
I have built the cascading fields, but now I am curious how you populate other fields from one of the list. For instance, I have three lists: Team, Program and Task. When I select a Team it updates my options within the Program List. I then select one of the Programs which then updates my options in my task list. Within my Task list, I have two other fields that I would like to be automatically populated once the task is selected and that is my Duration and my Description. These fields should not be a dropdown and should simply have the data imputed into them based on task that is selected.
Hi Laura
I need your guidance for below.
I’ve an issue on calculating lookup value from other list.. I have List called “Product”. in that I have column name “ProductCode”(Single line text) and “Unit Price”(number with 2 decimal). I’ve created a new custom list called “Purchase Form”.
In that “Purchase Form” I want to have below:
– A auto sequence column “Purchase ID” which will auto generate as “PO-15-####”. The behind number will start from 2001.
– A lookup column of “ProductCode” which will be in Choice mode.
A column which have “Unit Price” from another list will automatically generated when we choose the “ProductCode”.
– The main important column is “Purchased Value” which will calculate [Purchased Qty*Unit Price] and results will be in currency or number. [I will another column called “Purchased Qty”(number).]
I’ve tried lookup but unable to do the calculate. I need to have a workaround without using any backend commands.
Please help me to guide how to do in SharePoint 2013 or InfoPath 2013 without having workflow involved.