Data row limit 2000 using SQL

[Big_S]

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

[v-bofeng-msft]

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]

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]

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]

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?

[v-bofeng-msft · Accepted solution]

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