cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Big_S
Post Patron
Post Patron

Data row limit 2000 using SQL

Hi Guys,
I'm having difficulty with this, how can I extend my data row limit more than 2000? I'm using SQL as my data storage new data starting 2001 won't display in the Gallery.

This is my Gallery

Big_S_0-1605594179597.png


My SQL storage

Big_S_1-1605594278497.png

 

 

1 ACCEPTED SOLUTION

Accepted Solutions

Hi @Big_S :

Yes.You need to replace'[dbo].[Request]' with ACollection.

In addition:

  • Id must be number type(bigint, decimal, int, money, numeric, smallint, smallmoney, tinyint, float)
  • The number of records returned by each query cannot be greater than 2000. (Because Collect is also non-delegable)

Best Regards,

Bof

View solution in original post

5 REPLIES 5
v-bofeng-msft
Community Support
Community Support

Hi @Big_S :

I noticed that there is a yellow triangle in your screenshot. This is "Delegation warning", which means that your formulas is not delegable.Could you show me the formula?

Could you tell me what the gallery's items property is?

If you want to process more than 2000 data, there are two ways:

1\Using delegable functions and operations.

2\Save the data in the data table to the collection in batches.For example:

I assume that your data source has an increasing int type field "TheNumber" starting from 1.

ClearCollect(ACollection,Filter(YourSQLTable,TheNumber<=2000));
Collect(ACollection,Filter(YourSQLTable,TheNumber>2000&&TheNumber<=4000));
Collect(ACollection,Filter(YourSQLTable,TheNumber>4000&&TheNumber<=6000))

With this fromula, you can load all records into a collection called ACollection, and then use this collection as your data source without losing data.

Best Regards,

Bof

 

 

 

 

 

Hi @v-bofeng-msft ,

Thank you for helping me, this is my gallery formula in the items property

Hi @v-bofeng-msft 

This is my formula in gallery, in the items property

//Filter values based on _requestTypeFilter, set by selecting item in GalleryFilters. Sort by StartDate, descending.
If(_search && !_requestTypeFilter="All",Sort(Filter('[dbo].[Request]', Status = 1 && search.Text in SequenceNo && If(_managerView, Approver =_myProfile.UserPrincipalName, Requester = _myProfile.UserPrincipalName) && If(_search,ApprovalStatus = _requestTypeFilter) && Text(RequestDates.SelectedDate) in TransactionDate),SequenceNo,Descending),

_search && _requestTypeFilter="All" ,Sort(Filter('[dbo].[Request]',search.Text in SequenceNo && If(_managerView, Approver =_myProfile.UserPrincipalName, Requester = _myProfile.UserPrincipalName) && Status =1),SequenceNo,Descending),

Sort(
If(_requestTypeFilter="Pending",
Filter('[dbo].[Request]',Status =1 && ApprovalStatus="Pending" && DateValue(Text(TransactionDate), "en") >= DateValue(Text(RequestDates.SelectedDate)) && DateValue(Text(TransactionDate),"en") <= DateValue(Text(RequestDates_1.SelectedDate)) && If(_managerView, Approver =_myProfile.UserPrincipalName, Requester = _myProfile.UserPrincipalName)),
Filter('[dbo].[Request]',Status =1 && ApprovalStatus=_requestTypeFilter && DateValue(Text(TransactionDate),"en") >= DateValue(Text(RequestDates.SelectedDate)) && DateValue(Text(TransactionDate),"en") <= DateValue(Text(RequestDates_1.SelectedDate)) && If(_managerView, Approver = _myProfile.UserPrincipalName, Requester = _myProfile.UserPrincipalName)) ), SequenceNo, Descending))




Hi @Big_S :

There are many functions and operations in your formula that are not delegable.

I marked them in red (because there are too many, I will not mark them one by one):

If(_search && !_requestTypeFilter="All",

Sort(

   Filter(

     '[dbo].[Request]',

       Status = 1 && search.Text in SequenceNo && If(_managerView, Approver =_myProfile.UserPrincipalName, Requester = _myProfile.UserPrincipalName) && If(_search,ApprovalStatus = _requestTypeFilter) && Text(RequestDates.SelectedDate) in TransactionDate),SequenceNo,Descending),

_search && _requestTypeFilter="All" ,Sort(Filter('[dbo].[Request]',search.Text in SequenceNo && If(_managerView, Approver =_myProfile.UserPrincipalName, Requester = _myProfile.UserPrincipalName) && Status =1),SequenceNo,Descending),

Sort(

If(_requestTypeFilter="Pending",

Filter('[dbo].[Request]',Status =1 && ApprovalStatus="Pending" && DateValue(Text(TransactionDate), "en") >= DateValue(Text(RequestDates.SelectedDate)) && DateValue(Text(TransactionDate),"en") <= DateValue(Text(RequestDates_1.SelectedDate)) && If(_managerView, Approver =_myProfile.UserPrincipalName, Requester = _myProfile.UserPrincipalName)),

Filter('[dbo].[Request]',Status =1 && ApprovalStatus=_requestTypeFilter && DateValue(Text(TransactionDate),"en") >= DateValue(Text(RequestDates.SelectedDate)) && DateValue(Text(TransactionDate),"en") <= DateValue(Text(RequestDates_1.SelectedDate)) && If(_managerView, Approver = _myProfile.UserPrincipalName, Requester = _myProfile.UserPrincipalName)) ), SequenceNo, Descending))

 

Because your formula is too complicated, some undelegable parts are difficult to replace with other functions.I suggest you consider adopting my proposed solution2.

Best Regards,

Bof

 

Big_S
Post Patron
Post Patron

hi @v-bofeng-msft 
I will use your proposed 2 I will put it in the AppStart

ClearCollect(ACollection,Filter('[dbo].[Request]', Id <= 2000));
Collect(ACollection,Filter('[dbo].[Request]', Id > 2000 && Id <= 4000));
Collect(ACollection,Filter('[dbo].[Request]', Id > 4000 && Id <= 6000));

 

Do I need to replace my formula in the item gallery after using your proposed 2?

Hi @Big_S :

Yes.You need to replace'[dbo].[Request]' with ACollection.

In addition:

  • Id must be number type(bigint, decimal, int, money, numeric, smallint, smallmoney, tinyint, float)
  • The number of records returned by each query cannot be greater than 2000. (Because Collect is also non-delegable)

Best Regards,

Bof

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

May UG Leader Call Carousel 768x460.png

June User Group Leader Call

Join us on June 28 for our monthly User Group leader call!

PA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.

PA.JPG

New Release Planning Portal (Preview)

Check out our new release planning portal, an interactive way to plan and prepare for upcoming features in Power Platform.

Top Solution Authors
Users online (3,475)