3

I have a powerapp that is connected to a SQL database, I know powerapps has a limitation of showing 500 sharepoint list items, and i want to know if this 500 record retrieval limitation also applies when pulling data from an SQL database.

BLang
  • 930
  • 3
  • 16
  • 35

2 Answers2

4

In PowerApps, when querying any data source, a maximum of 500 records are retrieved. If your data source is delegable and the functions you use in the query are also delegable for that data source, then all the records in your source table will be queried and the first 500 results returned. If your data source is not delegable, or the functions you use in the query are not delegable for that data source, then only results from the first 500 records of the source table will be returned. Details are here.

Meneghino
  • 971
  • 6
  • 13
3

500 records is the default number of records allowed to be returned to PowerApps. You can up this to 2000 records with File/App Settings/Advanced Settings.

  • Typically a user will not require > 2000 records in a mobile app.
  • Developers often need to figure out how to query large datasets and create Collections which stay under the delegation threshold.

Some options to consider:

  • Use multiple Collections to pull 2k records at a time, then combine all collections into one collection.

    • Doesn't scale well.

  • If the records will be largely static, load them all into an Excel file and "package" them with the app.

    • Challenging to keep "synced" if content is dynamic

  • Get good at AND / OR statements to guaruntee that results will always be < 2k records

    • Example:
ClearCollect(colMyCollection,
  Filter(dbo.giantSqlDb, 
      createdDate > varOneYearAgo &&
      customerName = customerDropdown.Selected.Value &&
      // ... etc. Always guaruntee the results will be less than 2k records
SeaDude
  • 3,725
  • 6
  • 31
  • 68