For people that just needs to know how you get rid of it, just do this.
ClearCollect(yourCollectionName,SharepointListName)
Set this in your Screen's Onvisible property. And use the collection in your app, not the list. Better name your collection name similar to the Sharepoint list name. I like to put "coll" in front of the collection's name so I just type "coll" and the name shows up in the dropdown.
If you have ALOT (like more than 2000) of records in your list and you know which records you are going to use, filter the data and collect only the data you are going to use. Then your app will run quicker. BUT be careful you can use Filter, Lookup, Sort & SortByColumns, but you can't use Search. Also if the column is not numeric, you can only use "=". So you need to structure your Sharepoint list well. Too see more, look here.
If you want to properly understand about Delegation, I recommend watching Shane Young's video. He explains it very well! Recommend!
Understand delegation in a canvas app
Power Apps includes a powerful set of functions for filtering, sorting, and shaping tables of data in a canvas app: Filter, Sort, and AddColumns functions to name just a few. With these functions, you can provide your users with focused access to the information they need. For those with a database background, using these functions is the equivalent of writing a database query.
The key to building efficient apps is to minimize the amount of data that must be brought to your device. Perhaps you need only a handful of records from a sea of million, or a single aggregate value can represent thousands of records. Or perhaps only the first set of records can be retrieved, and the rest brought in as the user gestures that they want more. Being focused can dramatically reduce the processing power, memory, and network bandwidth that your app needs, resulting in snappier response times for your users, even on phones connected via a cellular network.
Delegation is where the expressiveness of Power Apps formulas meets the need to minimize data moving over the network. In short, Power Apps will delegate the processing of data to the data source, rather than moving the data to the app for processing locally.
Where this becomes complicated, and the reason this article exists, is because not everything that can be expressed in a Power Apps formula can be delegated to every data source. The Power Apps language mimics Excel's formula language, designed with complete and instant access to a full workbook in memory, with a wide variety of numerical and text manipulation functions. As a result, the Power Apps language is far richer than most data sources can support, including powerful database engines such as SQL Server.
Working with large data sets requires using data sources and formulas that can be delegated. It's the only way to keep your app performing well and ensure users can access all the information they need. Take heed of delegation warnings that identify places where delegation isn't possible. If you're working with small data sets (fewer than 500 records), you can use any data source and formula because the app can process data locally if the formula can't be delegated.
Note
Delegation warnings were previously flagged in Power Apps as "blue dot" suggestions, but delegation suggestions have since been re-classified as warnings. If the data in your data source exceeds 500 records and a function can't be delegated, Power Apps might not be able to retrieve all of the data, and your app may have wrong results. Delegation warnings help you manage your app so that it has correct results.
Delegable data sources
Delegation is supported for certain tabular data sources only. If a data source supports delegation, its connector documentation outlines that support. For example, these tabular data sources are the most popular, and they support delegation:
Imported Excel workbooks (using the Add static data to your app data source), collections, and tables stored in context variables don't require delegation. All of this data is already in memory, and the full Power Apps language can be applied.
Delegable functions
The next step is to use only those formulas that can be delegated. Included here are the formula elements that could be delegated. However, every data source is different, and not all of them support all of these elements. Check for delegation warnings in your particular formula.
These lists will change over time. We're working to support more functions and operators with delegation.
Filter functions
Filter, Search, and LookUp can be delegated.
Within the Filter and LookUp functions, you can use these with columns of the table to select the appropriate records:
- And (including &&), Or (including ||), Not (including !)
- In
- =, <>, >=, <=, >, <
- +, -
- TrimEnds
- IsBlank
- StartsWith, EndsWith
- Constant values that are the same across all records, such as control properties and global and context variables.
You can also use portions of your formula that evaluate to a constant value for all records. For example, Left( Language(), 2 ), Date( 2019, 3, 31 ), and Today() don't depend on any columns of the record and, therefore, return the same value for all records. These values can be sent to the data source as a constant and won't block delegation.
The previous list doesn't include these notable items:
- If
- *, /, Mod
- Concatenate (including &)
- ExactIn
- String manipulation functions: Lower, Upper, Left, Mid, Len, ...
- Signals: Location, Acceleration, Compass, ...
- Volatiles: Rand, ...
- Collections
Sorting functions
Sort and SortByColumns can be delegated.
In Sort, the formula can only be the name of a single column and can't include other operators or functions.
Aggregate functions
Sum, Average, Min, and Max can be delegated. Only a limited number of data sources support this delegation at this time; check the delegation list for details.
Counting functions such as CountRows, CountA, and Count can't be delegated.
Other aggregate functions such as StdevP and VarP can't be delegated.
Table shaping functions
AddColumns, DropColumns, RenameColumns, and ShowColumns partially support delegation. Formulas in their arguments can be delegated. However, the output of these functions are subject to the non-delegation record limit.
As in this example, makers often use AddColumns and LookUp to merge information from one table into another, commonly referred to as a Join in database parlance:
AddColumns( Products,
"Supplier Name",
LookUp( Suppliers, Suppliers.ID = Product.SupplierID ).Name
)
Even though Products and Suppliers may be delegable data sources and LookUp is a delegable function, the output of the AddColumns function isn't delegable. The result of the entire formula is limited to the first portion of the Products data source. Because the LookUp function and its data source are delegable, a match for Suppliers can be found anywhere in the data source, even if it's large.
If you use AddColumns in this manner, LookUp must make separate calls to the data source for each of those first records in Products, which causes a lot of network chatter. If Suppliers is small enough and doesn't change often, you could call the Collect function in OnStart to cache the data source in your app when it starts. As an alternative, you could restructure your app so that you pull in the related records only when the user asks for them.
Non-delegable functions
All other functions don't support delegation, including these notable functions:
- First, FirstN, Last, LastN
- Choices
- Concat
- Collect, ClearCollect
- CountIf, RemoveIf, UpdateIf
- GroupBy, Ungroup
Non-delegable limits
Formulas that can't be delegated will be processed locally. This allows for the full breadth of the Power Apps formula language to be used. But at a price: all the data must be brought to the device first, which could involve retrieving a large amount of data over the network. That can take time, giving the impression that your app is slow or possibly crashed.
To avoid this, Power Apps imposes a limit on the amount of data that can be processed locally: 500 records by default. We chose this number so that you would still have complete access to small data sets and you would be able to refine your use of large data sets by seeing partial results.
Obviously care must be taken when using this facility because it can confuse users. For example, consider a Filterfunction with a selection formula that can't be delegated, over a data source that contains a million records. Because the filtering is done locally, only the first 500 records are scanned. If the desired record is record 501 or 500,001, it isn't considered or returned by Filter.
Aggregate functions can also cause confusion. Take Average over a column of that same million-record data source. Average can't yet be delegated, so only the first 500 records are averaged. If you're not careful, a partial answer could be misconstrued as a complete answer by a user of your app.
Changing the limit
500 is the default number of records, but you can change this number for an entire app:
- On the File tab, select App settings.
- Under Advanced settings, change the Data row limit for non-delegable queries setting from 1 to 2000.
In some cases, you'll know that 2,000 (or 1,000 or 1,500) will satisfy the needs of your scenario. With care, you can increase this number to fit your scenario. As you increase this number, your app's performance may degrade, especially for wide tables with lots of columns. Still, the best answer is to delegate as much as you can.
To ensure that your app can scale to large data sets, reduce this setting down to 1. Anything that can't be delegated returns a single record, which should be easy to detect when testing your app. This can help avoid surprises when trying to take a proof-of-concept app to production.
Delegation warnings
To make it easier to know what is and isn't being delegated, Power Apps provides warning (yellow triangle) when you create a formula that contains something that can't be delegated.
Delegation warnings appear only on formulas that operate on delegable data sources. If you don't see a warning and you believe your formula isn't being properly delegated, check the type of data source against the list of delegable data sources earlier in this topic.
Examples
For this example, you'll automatically generate a three-screen app based on a SQL Server table named [dbo].[Fruit]. For information about how to generate the app, you can apply similar principles in the topic about Dataverse to SQL Server.
The gallery's Items property is set to a formula that contains SortByColumns and Search functions, both of which can be delegated.
In the search box, type "Apple".
Marching dots appear momentarily near the top of the screen as the app communicates with SQL Server to process the search request. All records that meet the search criteria appear, even if the data source contains millions of records.
The search results include "Apples", "Crab apples", and "Pineapple" because the Search function looks everywhere in a text column. If you wanted to find only records that contain the search term at the start of the fruit's name, you can use another delegable function, Filter, with a more complicated search term. (For simplicity, remove the SortByColumns call.)
The new results include "Apples" but not "Crab apples" or "Pineapple". However, a yellow triangle appears next to the gallery (and in the screen thumbnail if the left navigation bar shows thumbnails), and a blue, wavy line appears under a portion of the formula. Each of these elements indicate a warning. If you hover over the yellow triangle next to the gallery, this message appears:
SQL Server is a delegable data source, and Filter is a delegable function, However, Mid and Len can't be delegated to any data source.
But it worked, didn't it? Well, kind of. And that is why this is a warning and not a red, wavy squiggle.
- If the table contains fewer than 500 records, the formula worked perfectly. All records were brought to the device, and Filter was applied locally.
- If the table contains more than 500 records, the formula won't return record 501 or higher, even if it matches the criteria.