If you are building Canvas Apps with screens that browse SharePoint Online Lists containing over 5000 items; you should be mindful of how you configure these Lists and the impact on Filter, Search, LookUp , Sort ( single columns only) and SortByColumns functions.
This saltatory lesson became apparent when a client asked me to troubleshoot a Power App with a Data Table control on a screen for listing items requiring management approval. When the managers navigate to the screen, it shows no items with status set to “Awaiting”. A review of the SharePoint list confirmed staff members had previously submitted requests for approval.
Initial findings
The total number of list items is now 5049 so just over the List view threshold (5000). There was only one delegation warning, but this was not the issue. Note, Fx expression for the Items property is determined by managers optionally selecting the values in the dropdowns for the Business Unit, Manager Name and employee name search text input.
When I ran the Power App through the Monitor Tool, I can see the requests made against the SharePoint list causing the error.
When you click on the Response Tab,
"status": 400,
"message": "The attempted operation is prohibited because it exceeds the list view threshold.\r\nclientRequestId: c6e06734-d9c9-40f1-b055-d5f0039868c9\r\nserviceRequestId: c6e06734-d9c9-40f1-b055-d5f0039868c9"
},
The List View Threshold
SharePoint list items and columns are ultimately stored in a single SQL Server table. The List View Threshold is a limitation, set in SQL Server and ensures a query operation does not return more the 5000 results, as this would lock the entire table. The query operation can be through a SharePoint List View or in the Power App a Filter or Sort expression, referencing one or more columns in the SharePoint List data source.
Indexing
As shown in the above Canvas App, the Sort and Filter functions are referencing one or more columns in the SharePoint list and this is causing the list view threshold error. To fix this error, you need to create an index on each of the SharePoint list columns, referenced in the Filter and Sort expression. Doing so allows you to process over 5000 items and, therefore bypassing the threshold. You can add an index manually, by navigating to the list settings, then indexed columns. See Add an index to a list or library column – Microsoft Support.
Info
Ensure your development and UAT SharePoint Lists have over 5000+ rows during the development and test cycles, to allow testing for List View Threshold, Non delegable and Pagination errors (Power Automate).
In the list settings, select Indexed Columns, then Create a new index . A shout out goes to Simon Doy, MVP (@simondoy) who suggested the issue is because of a missing index for any of the columns referenced in the Filter or Sort expressions.
Supported Columns
- Single line of text
- Choice (single value)
- Number
- Currency
- Person or group (single value) (Lookup)
- Managed Metadata (Lookup)
- Lookup Columns against a SharePoint list are supported. However, they can still cause List View Threshold issues if the lookup list exceeds 5000 rows.
- Yes/No
Unsupported Columns
- Multiple Lines of text
- Choice (multi-values)
- Calculated
- Hyperlink or Picture
- Custom Columns
- Person or Group (multi-valued)
- External Data
Alternatively, if you go to advanced settings, you can check and enable Automatic Index Management. The modern list experience enables automatic index creation for list and libraries with 20,000 items, when creating views with columns for filtering or ordering. Note, SharePoint List ID columns are indexed by default.
List View Threshold verses Delegation Errors
List View Threshold errors are not be confused with Non delegable warnings – I encountered both in different statements for the same DataTable items property. Non delegable warnings show: delegated. query in the Power App, will not be delegated to the backend data source, such as SharePoint or SQL Server (see Delegable Data Sources). Instead, this query is processed locally, by the Power App. Note, the number of rows the Power App will fetch in processing the query is limited to a default value of of 500 rows. You, can increase this limit to a maximum of 2000. See Power App Advanced settings: Data row limit for non-delegable queries. Queries written using Filter, Search, LookUp , Sort ( single columns only) and SortByColumns functions can be delegated . See, Filter Functions.
Filter, Search, LookUp , Sort ( single columns only) and SortByColumns functions can be delegated . See, Filter Functions
Examples of using Filtering and Sorting Functions
By creating a manual Index on both the Employee’s Name and Start Date, resolved the List View Threshold issues.
//
Sort(Filter('Holiday Approval', 'Approval Status'.Value = drpApprovalStatus.Selected.Name) ,EmployeeName, Ascending);
// or here
Sort(Filter('Holiday Approval', 'Approval Status'.Value = drpApprovalStatus.Selected.Name) ,StartDate, Ascending);
Oddly, sorting on both the name and start date still showed List View Threshold errors. Many thanks to Sancho Harker MVP,, (online alias: iAm_ManCat) who suggested that I also needed to create an index on the Approval Status choice column, even though this had previously not caused an error in any of the above statements.
// Causes a view threshold error becuse I didn't have the Approval status choice column indexed
Sort(Sort(Filter('Holiday Approval', 'Approval Status'.Value = drpApprovalStatus.Selected.Name) ,EmployeeName, Ascending),StartDate,Ascending)
// as does the statement below:
SortByColumns(Filter('Holiday Approval', 'Approval Status'.Value = drpApprovalStatus.Selected.Name) ,"EmployeeName", Ascending,"StartDate",Ascending)
By creating an index on the relevant columns, you resolve the List View Threshold issues in this Power App. If you found this post useful, please leave a comment.
