cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Big_S
Helper V
Helper V

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
Helper V
Helper V

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

View solution in original post

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are Your Ready?

Test your skills now with the Cloud Skill Challenge.

secondImage

Demo Extravaganza is Back!

We are excited to announce that Demo Extravaganza for 2021 has started!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Top Solution Authors
Top Kudoed Authors
Users online (33,720)