I am using the below formula to get the data from my SQL server.
My data row limit is set to 2000.
So now the problem is that the above-mentioned formula is grouping only 2000 records and showing it. Moreover, it's not even giving any delegation warning.
Please guide me how to overcome this problem.
As you are using SQL server you should be writing stored procedure /views to get the data as required with further filter criteria. Also what are you expecting user to do by fetching 2000 records. Instead use pagination.
This way your app will also have better performance.
I am already using a view. The problem is that it has around 30,000 records.
Even after filtering, I expect more than 2000 records. But Group by function is getting applied only on first 2000.
You should use filter if possible, other option is to go with paging, where you need to define PageSize and PageNumber
CREATE PROCEDURE dbo.Pagination_Test @PageNumber INT = 1, @PageSize INT = 100 AS BEGIN SET NOCOUNT ON; SELECT CustomerID, FirstName, LastName, EMail, Active, Created, Updated FROM dbo.Customers_I ORDER BY CustomerID OFFSET @PageSize * (@PageNumber - 1) ROWS FETCH NEXT @PageSize ROWS ONLY OPTION (RECOMPILE); END GO
You could take a try to first collect the table into a collection with delegable functions, then working with the collection with the non-delegable function.
then use the Tmp to perform filters or calculations or group by in App.
Take a look at the article below for the Delegable functions for SQL Server and the other data sources:
Besides, you may follow the steps mentioned by mr.Dang through the following link:
Hi @Anonymous ,
Can you explain a bit more about using pagination? I'm familiar with SQL views to pare down large data sets, but this is my first exposure to pagination. What are the effects of using an SP in this way as opposed to a view?
Thanks for your expertise!
As you see the sample stored proc, you can pass current page and page size. Along with these, you can add more params and pass the same, so that the data which is fetched and show on PowerApps is minimal and overall performance is maintained. All the processes will be done on DB side is another advantage considering the app can be accessed on a tablet too.
We will having view with joins and basic filters like status. Other than these rest filters will be based on what is sent or applied through app, which is not same with stored proc.
Also showing too many records in your app might not help end users.
Stay up tp date on the latest blogs and activities in the community News & Announcements.
Mark your calendars and join us for the next Power Apps Community Call on January 20th, 8a PST
Dive into the Power Platform stack with hands-on sessions and labs, virtually delivered to you by experts and community leaders.
Watch Nick Doelman's session from the 2020 Power Platform Community Conference on demand!