cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
beniwal
Level: Powered On

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
shailendra74
Level 8

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.

beniwal
Level: Powered On

Re: group by delegation

hi @shailendra74 

 

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.

 

shailendra74
Level 8

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.

Community Support Team
Community Support Team

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
seadude
Level 10

Re: group by delegation

Hi @shailendra74 ,

 

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!

shailendra74
Level 8

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.  

seadude
Level 10

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
firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

FirstImage

Power Platform World Tour

Coming to a city near you

thirdimage

PowerApps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

FourthImage

Join PowerApps User Group!!

Connect, share, and learn with your peers year-round

SecondImage

Power Platform Summit North America

Register by September 5 to save $200

Top Kudoed Authors
Users Online
Currently online: 124 members 4,572 guests
Please welcome our newest community members: