none
Querying a large SharePoint List from an InfoPath Form

    Question

  • We need a way to use an InfoPath 2013 form to dynamically query an Online SharePoint list that will have around 100K items.  Result sets returned will have fewer than 5000 entries.
      
    We tried applying filter criteria in the InfoPath form but evidently the filter is applied client-side, so it fails due to the 5000 item limit. Doing the queries at off-hours (when the limit is disabled) does not meet business needs.
      
    I explored using the SharePoint REST API. While this showed promise of working in preview mode, when published to the site, the query fails with "XML data that is required for this form to function correctly cannot be found". According to an article on social.msds.microsoft.com, this is a known limitation, namely that you can't query an Online SharePoint list via the REST API because of security risks.
      
    I don’t think we can use predefined filtered views because the value of the query constraint will be chosen by the user at query time, and the user may want to combine multiple query constraints in a single query.
      
    I assume the way to use InfoPath and SharePoint for this application must entail applying query constraints (likely against indexed columns) on the server side so that the returned items don't get throttled by the 5000 item limit. Is that correct? What is the recommended way to achieve this?
      
    Many thanks!
    Sunday, February 12, 2017 6:47 PM

All replies

  • Hi bcompton32,

    In InfoPath, there is a limit of 5000 items when you query for data by design.

    So I suggest you using lists with less than 5000 items.

    As a workaround, I recommend you saving the information to an Excel file and add connection with this excel file.

    Here is a similar case for your reference:

    http://stackoverflow.com/questions/30901480/how-to-query-large-sharepoint-2013-lists-in-infopath-2010

    Follow the steps in the article below about how to import data from excel to InfoPath:

    http://www.bizsupportonline.net/infopath2010/import-data-excel-2010-infopath-2010.htm

    Best regards,

    Grace Wang


    Please remember to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com

    Monday, February 13, 2017 11:30 AM
  • Thanks for the response Grace. My guess is that trying to manage this much data via a spreadsheet will fail to meet business needs - long download times, risk of inconsistencies between a local spreadsheet and the SharePoint list, maybe(?) a requirement for locally installed InfoPath for all users, etc.

    To generalize, the core requirements of this project are to use a customized web UI to add / update records (eventually 100K), and dynamically query for small (< 5K) sets of records. Are we saying that a SharePoint / InfoPath solution is, by design, not a viable choice for this? Is there an alternative web based custom UI choice that can work with a large SharePoint list?

    Thanks again!

    Monday, February 13, 2017 1:30 PM
  • Hi bcompton- I think your only options are Grace's idea pre-filtered views or a lot of rules in InfoPath. What I'd do is create views for all the standard things/ways a user would filter the list. Then have those views as a choice the user will select. Not the prettiest solution, but you'd have to make due.

    Otherwise you could have fields where they could enter their parameters, and set rules on those fields to pull just that subset of information in the query. However, this would be tedious, because you'd have to create the rules on each field and have them check to see what the other fields contain to keep restricting the data returned.


    cameron rautmann

    Monday, February 13, 2017 4:55 PM
  • Thanks Cameron. A tedious solution is probably better than none at all, which is what I have so far. Could you please elaborate on how to "set rules on those fields to pull just that subset of information in the query"? When I apply a filter within the definition of the Data Connection (even with criteria that only returns one record), it still fails because (evidently) the filter is applied on the client side, so the server tries to return all 100K items, which of course gets blocked by the 5K item threshold limit.
    Tuesday, February 21, 2017 6:24 PM
  • Hi,

    I have the exact same issue and don't know how to solve this... can you share how you resolved your problem?

    Thank you so much!

    Jean

    Friday, April 27, 2018 9:52 PM