Answered

Data row limit 2000 using SQL

(0) Share

Report

Posted on by Big_S

494

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

My SQL storage

Categories:

Building Power Apps

All responses (5)

Answers (1)

Sort by

v-bofeng-msft Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!
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
Big_S 494 on at

Like
a
(0)

Report
Copy link

Link copied!

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))
v-bofeng-msft Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

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 494 on at

Like
a
(0)

Report
Copy link

Link copied!

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?
Verified answer

v-bofeng-msft Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!
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