Solved: Re: Delegation Limit as a label in my Power Apps

sasrsc1966 · ‎09-02-2021

I am experimenting with a new app and I'm adjusting the row limit for the non-delegable limit from 500 to 2 to 2000. Is there a way to show the current value as a label in a Power App. I seem to be experiencing some issues with caching (I guess) as some collections are being limited by this number even though they are delegable functions, so I'm wanting to see if I can display the value in a label to save me going to File, Settings.

timl · ‎09-02-2021

Hi @sasrsc1966

The issue here is that ClearCollect will collect no more than the number of rows that you specify in the "row limit" setting.

For instance, with the row limit set to 2, this will collect 2 rows:

ClearCollect(col,Filter('[dbo].[YourTable]',YourColumn="Test"));

However if you were to set the items property of the gallery control to the following formula, you would see all expected rows in the gallery (even with the row limit set to 2)

Filter('[dbo].[YourTable]',YourColumn="Test")

If you're developing an app and you want some visual indication as to row limit setting for debug purposes, you can add a label and call the CountRows function against a table that contains more than the maximum of 2000 rows:

CountRows('[dbo].[YourTable]')

Note however that this is not particularly efficient because it would return all the rows just to display the count, but at least it would give you an indication as to what your row limit setting is.

With regard to changing the limit, it's necessary to reopen the app in order for this to take effect. I get this is frustrating, so you might consider voting for my idea here:

https://powerusers.microsoft.com/t5/Power-Apps-Ideas/Apply-app-setting-changes-without-requiring-the...

View solution in original post

leyburn19 · ‎09-02-2021

All data in powerapps is restricted to the limit of the data you select up to 2000. Non Delegable just means it can extract outside the 2000 limit, but if the non delegable filter returns over 2000 it will still only be 2000

StalinPonnusamy · ‎09-02-2021

Hi @sasrsc1966

Users get delegation warning whether the data is within the limit. For example, you have app settings to 1000 but the data you are retrieving is only 500 but still, the user receives the warning.

We can avoid this delegation warning if we know data is within the limit.

One more thing, Data can be more than 2000 also, but query result should be less than 2000(max). I have my SQL DB has 60k records which connected to power apps.

So displaying the size is not going to help.

sasrsc1966 · ‎09-02-2021

I must have a total misunderstanding. I thought that if a function/expr is delegable then it will be passed and handled outside of power apps successfully (therefore it can easily handle large datasets). In my case an on-prem gateway. I'm using solely delegable functions. That row limit is meant to limit how many rows are returned where delegation is not supported. The function/expr I'm using is delegable as best I know. I set the row limit to 2 rows to test to make sure I know my functions/exprs are delegable. Yet only 2 rows are returned which means for some reason Power Apps thinks they are not delegable.

This all works fine when I increase the row limit to 2000 or 500 (as it's not a big dataset).

ClearCollect(LookupsValuesCol,SortByColumns(Filter(lookupValues,lkupCatID=19||lkupCatID=26||lkupCatID=27),"descrip",Ascending));
ClearCollect(EventCategories,SortByColumns(Filter(LookupsValuesCol,lkupCatID=19),"descrip",Ascending));
ClearCollect(EventParentCategories,SortByColumns(Filter(LookupsValuesCol,lkupCatID=26),"descrip",Ascending));
ClearCollect(EventChildCategories,SortByColumns(Filter(LookupsValuesCol,lkupCatID=27),"descrip",Ascending));

4 lines - I make a call to a on-prem gateway hitting lookupValues with a filter and an OR statement. These are delegable to SQL Server. I create a collection.
I then filter that collection and make 3 smaller collections.

I change the limit to 2000 rows. Nothing changes. I save. Nothing Changes. I publish. Nothing Changes. I close the app. Restart the app and now it works. I suppose changing something in settings requires you to save and restart. But that doesn't answer for me at least why I don't get all rows returned originally with the row limit set to 2. Those lines of code above are on the screen's onVisible setting. I'm sorry if I'm being dumb.

I also read you can create collections with more than 2000 rows if you follow a few workaround methods. That's not really my goal here. I'm just trying to understand why the row limit effects my code when everything is delegable.

timl · ‎09-02-2021

Hi @sasrsc1966

The issue here is that ClearCollect will collect no more than the number of rows that you specify in the "row limit" setting.

For instance, with the row limit set to 2, this will collect 2 rows:

ClearCollect(col,Filter('[dbo].[YourTable]',YourColumn="Test"));

However if you were to set the items property of the gallery control to the following formula, you would see all expected rows in the gallery (even with the row limit set to 2)

Filter('[dbo].[YourTable]',YourColumn="Test")

If you're developing an app and you want some visual indication as to row limit setting for debug purposes, you can add a label and call the CountRows function against a table that contains more than the maximum of 2000 rows:

CountRows('[dbo].[YourTable]')

Note however that this is not particularly efficient because it would return all the rows just to display the count, but at least it would give you an indication as to what your row limit setting is.

With regard to changing the limit, it's necessary to reopen the app in order for this to take effect. I get this is frustrating, so you might consider voting for my idea here:

https://powerusers.microsoft.com/t5/Power-Apps-Ideas/Apply-app-setting-changes-without-requiring-the...

sasrsc1966 · ‎09-02-2021

Thanks - that helps with some clarity.
The gallery shows all the rows if you scroll down

Gallery6.Items=lookupValues
CountRows(Gallery6.AllItems) = 100 initially, then 200 as it fetches the next 100 and then finally 278.

So lookupValues=278 rows. OK good. That proves your point - thank you as this is not effected by any non-delegable items.
The whole time the row limit=2 which is proved by a CountRows(lookupValues) = 2 because CountRows isn't delegable.

In my case, I want to provide a dropdown list of values and not a gallery. There will only be 8 options
So in the dropdown Items I do...
Filter(lookupValues,lkupCatID=19) and still only 2 rows show.
Filter and = are delegable. So what's my issue now?

Yes, I know I can change my row limit to 500 and I will have no problem, but the goal is to comprehend delegable issues.

I've read this workaround...
https://poszytek.eu/en/microsoft-en/office-365-en/powerapps-en/overcome-2000-items-limit-using-power...

timl · ‎09-03-2021

Hi @sasrsc1966

The behaviour with the dropdown control is similar to the behaviour of the ClearCollect function - the dropdown will display no more than the number of rows that you specify in the "row limit" setting.

It's also useful to note that the drop down control also has a hard limit of 500 rows. That is, if you set the "row limit" setting to 600 and set the items property to a delegable expression, the drop down control will show only the first 500 records from the result of that expression.

In general, the combo box control works better than the drop down control for larger amounts of data. With the "row limit" setting set to 2, the combo box control will still only display 2 rows. However, the end-user can enter a search term into the combo box control, and it will retrieve the two most relevant items.