InfoPath Dev

I have a scenario where I have a list of about ~8000 rows. I need to query that list to return a few values based upon a user input ID number. The query was working fine when the list size was around 2000 rows, but now that it is close to 8000 I receive an error that the number of items returned has exceeded the limit. I'm thinking that I need to split the large list into multiple smaller lists and sequentially query each list until the ID is found and values are returned. For example, query list A, if not found, query list B, etc. Does anybody know of a way to do this efficiently through rules?

Can you add an index to the list? I've found that on a list that exceeds the threshold, if there is an index, and if I use query fields to limit the number of rows returned, I can get past the list threshold.

Would I add an index to the ID field? I can do that, but then how do I limit the number of rows returned in the query?

I wasn't clear, sorry. Is there information in your form you can use to determine what rows you need from the list? Like - the user selects a department, and you know you only need rows that match that department? You aren't currently querying for all 8000 rows, are you?

Yep, I was querying all 8000 rows. Probably the problem? There is a category that I could have the user select from a dropdown. What column(s) would I need to index and then how do you limit the query results based upon the category? By filtering in the query action?

In your list connection, set it to not query on load (this is a checkbox on the last dialog of the list data connection wizard). In your form, when the user selects a category, set the category query field in your list data connection (the data connection has query fields and data fields) to the selected category. Then add a rule to query the data source. This should limit the returned rows. You may still get an error because of the list threshold, in which case, add an index to the category field in the list settings. Make sense?

That worked fantastic! I never knew what the purpose of the query fields were before, thank you.

They weren't added for SharePoint lists until InfoPath 2010, so I think even for long time form designers they are sometimes missed. They are great for equivalence - This Thing is equal to That Value - but for anything more complex, you can use REST, like This Thing is greater than That Value. :)