cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sparky13
Level: Powered On

Data Row Limit

In 2.0.750 a new experimental feature was added: https://powerapps.microsoft.com/en-us/blog/powerapps-data-row-limit-for-non-delegable-queries/

 

I have an SQL backend that is queried and records are added to.

 

When I turn the data row limit to 1, I have found that only 1 record is available to PowerApps, which points me in the direction that SQL queries are not being delegated.

 

Any suggestions on where I should start looking? Are there particular PowerApps functions that are non-delegatable?

6 REPLIES 6
Super User
Super User

Re: Data Row Limit

Hi @sparky13

 

I am not sure what function you used and what was your objective behind setting the limit to 1. However, you can refer to this link for more details - https://docs.microsoft.com/en-us/powerapps/delegation-list 

 

Also, just as an example in your case, say if you want to filter one specific record from say a 1000 records, you can use Filter function and it will delegate the filtering to the SQL database and return the specific record.

 

However, if you had 2 records matching the filter conditions, then it will return only one record out of the two as you have set the limit to 1 record only.


Vivek Bavishi aka That API Guy
PowerApps and Flow MVP
Blog | Twitter | YouTube | Community Profile | GitHub



If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

sparky13
Level: Powered On

Re: Data Row Limit

Hi @ThatAPIGuy

 

I am attempting to test where delegation is occurring or not. By setting the limit to 1, I believe it will become very apparent if delegation is not being used.

 

Eg. If limit is set to 1, and the records are returned (> 1), then the query is being delegated to SQL Server. If, on the other hand, only 1 record is returned (and more than 

Highlighted
sparky13
Level: Powered On

Re: Data Row Limit

... (submitted twice)

PowerApps Staff CarlosFigueira
PowerApps Staff

Re: Data Row Limit

What you'll get will depend on how you try to access the data. That experimental feature has a side effect of determining the buffer size that will be used when you try to load data into a local collection, which is probably what you are seeing.

 

For example, let's say you set the data row limit to 5, and add a data source to a SQL table (Products) that has 1000 items. If you have this expression:

Collect(collection1, '[dbo].[Products]')

Then you'll only get 5 items in the local collection - that's also controlled by that setting.

 

Even if you use some filter expression, you'll only get 5 items:

Collect(collection2, Filter('[dbo].[Products], ItemsInStock > 10))

Now if you insert a gallery (which is a control that knows how to deal with delegatable data sources, essentially requesting "new pages" when the user gets to the end of it), and set its Items property to the data source, then you'll be able to see the entire table (or filtered table, if you used a filter expression).

sparky13
Level: Powered On

Re: Data Row Limit

Thanks for that clarification @CarlosFigueira

 

My aim is to ensure that all SQL related queries are delegated to SQL Server.

 

Is there a best process to ensure that this is the case?

sajarac
Level 8

Re: Data Row Limit

Hello there,

 

Does any of you know if this is already solved? I started with my data in an excel sheet, once I reached 500 records I had to migrate to share point list, and I am already reaching 2000 records so soon my users will not have access to the new records, any advice?

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors
Users Online
Currently online: 138 members 5,465 guests
Please welcome our newest community members: