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

Power Apps Community Call

Monthly Power Apps Community Call

Did you miss the call?? Check out the Power Apps Community Call here!

secondImage

Experience what’s next for Power Apps

See the latest Power Apps innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Power Platform ISV STudio

Power Platform ISV Studio

ISV Studio is the go-to Power Platform destination for ISV’s to monitor & manage applications post-AppSource publish.

Top Solution Authors
Top Kudoed Authors
Users online (50,584)