cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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
Highlighted
Anonymous
Not applicable

Re: group by delegation

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.

Highlighted
Helper III
Helper III

Re: group by delegation

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.

 

Highlighted
Anonymous
Not applicable

Re: group by delegation

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.

Highlighted
Community Support
Community Support

Re: group by delegation

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

Highlighted
Super User
Super User

Re: group by delegation

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!

Highlighted
Anonymous
Not applicable

Re: group by delegation

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.  

Highlighted
Super User
Super User

Re: group by delegation

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

Helpful resources

Announcements
Check this Out

Helpful information

Featuring samples like Return to the Workplace and Emergency Response Applications

August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

secondImage

Return to Workplace

Reopen responsibly, monitor intelligently, and protect continuously with solutions for a safer work environment.

secondImage

Super Users Coming in August

We are excited for the next Super User season.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (6,960)