Delegation warnings

[Naps]

Hi all,

This topic isn't so much about a problem I'm currently having but more me wondering what are the best ways to avoid delegation warnings.

As I understand it as soon as you have delegation warnings Powerapps will not search more than 2000 rows in a table. So not search them all and return 2000 results at most, but only search on the first 2000 rows. No doubt a lot of people are bound to need to search a lot more data than that.

So far I noticed the following

MS claims in and or can be delegated and while this is not untrue, it only applies to filters that only search a single data source. If for example you try to use in/or with a gallery.selected value from a different data source or try to filter on a lookup field you will get delegation warnings.

One way to work around that is to set the gallery.selected values you need as variables and use those in a filter. This surprised me a bit because I expected gallery.selected.myfield to basically act as a variable. The downside is that you cannot set the variable when clicking the gallery so depending on how your app is designed this can be problematic.

To work around the lookup issue I figure you can create a helper field in the CDS that contains the same value bit is a simple text field and populate that by using a hidden field in your form. But it might be problematic populating those fields outside of a Canvas app. Not sure about that though as I haven't really touched model driven apps yet. Also you need more fields so that doesn't improve DB cleanness and it could even become a cost factor.

So far it seems there are also case where you are simply out of luck and better hope you don't exceed a certain amount of documents. For example I have a Sharepoint data source where I simply want to look for files in a certain folder instead of the root but 'folder path' and comparable options always trigger delegation warnings. Though I'm not familiar with Sharepoint so it might simply be a case of changing how I store things in Sharepoint. That said you wouldn't expect something straight forward as wanting to search only set folder(s) as something that could create delegation warnings.

Finally there is as I understand it the option to create a collection inside your app. While I get the idea, I just wonder how that works if you have millions of records or, for example, a DB that is very big in size because it contains a lot of images/documents. Surely that must have potential to massively impact performance when the data is loaded in?

What are your tactics for avoiding delegations warnings when you simply cannot get around having multiple data sources, lookup fields etc?

[Drrickryp]

@Naps 

Use delegatable functions like StartsWith(), don't use lookup fields or switch to Dataverse if you have access to it.    https://powerusers.microsoft.com/t5/Power-Apps-Community-Blog/The-Ten-Commandments-of-SharePoint-in-... 

[BCLS776]

@Naps to add on, it sounds like you have already examined Microsoft's documentation related to delegation, which is a good start.

When I need to consider delegation warnings, I've found it useful to break up some queries into delegable and non-delegable portions, with the goal of using delegation to get the returning row count well below 2000. For example, using Filter() to shrink a table down so that I can use CountRows() reliably on the result.

Here's the thing many users get hung up on: not every delegation warning is a cause for concern. They don't always need to be stomped out of existence. Using CountRows again as an example, sometimes it is useful to use that function as part of a condition to verify whether a table has data within it (or not). A condition such as CountRows(myTable)>1 doesn't care if delegation issues limited the count to 2000 -- it's larger than 1, so the condition is satisfied.

A final thought on the topic is to remember why the 2000 row limit exists: to steer users towards efficient use of the service. It discourages sloppy code that could lead to complaints about the app's/service's performance and it makes it difficult for a small set of users to consume the service's resources in a way that leaves everyone else unsatisfied.

Bryan