cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper III
Helper III

group by delegation

hi,

 

I am using the below formula to get the data from my SQL server.

 

GroupBy('[dbo].[Notes]',"email","grouped")

 

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.

 

Thanks,

Mohit

 

 

 

7 REPLIES 7
Anonymous
Not applicable

Hi @beniwal 

 

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.

 

Thanks.

hi @Anonymous 

 

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.

 

Anonymous
Not applicable

Hi @beniwal 

 

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

Thanks.

Community Support
Community Support

Hi @beniwal 

 

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.

For example:

ClearCollect(Tmp, SQLTableName)

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:

Delegable data sources

 

Besides, you may follow the steps mentioned by mr.Dang through the following link:

Overcoming the 500 record limit locally

 

Best Regards.

Yumia

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!

Anonymous
Not applicable

Hi @seadude 

 

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.  

Hm. I'm going to have to check more into that. Thank you for the sample code and explanation.

Helpful resources

Announcements
News & Announcements

Community Blog

Stay up tp date on the latest blogs and activities in the community News & Announcements.

Power Apps Community Call

Power Apps Community Call- January

Mark your calendars and join us for the next Power Apps Community Call on January 20th, 8a PST

PP Bootcamp Carousel

Global Power Platform Bootcamp

Dive into the Power Platform stack with hands-on sessions and labs, virtually delivered to you by experts and community leaders.

secondImage

Power Platform Community Conference On Demand

Watch Nick Doelman's session from the 2020 Power Platform Community Conference on demand!

Top Solution Authors
Top Kudoed Authors
Users online (11,301)