In this article, we will discuss PowerApps delegation, delegable data sources, and functions, and how to overcome the delegation warning in PowerApps?
What is PowerApps Delegation?
Delegation is a very important concept that you have to understand, especially when you will be working with large data sets in PowerApps. First, let’s know that “delegate” means to assign tasks to someone else.
PowerApps Delegation means when you work with different data sources such as DataVerse, SharePoint, SQL Server, etc, the data processing is assigned to the data sources rather than done by PowerApps itself. This means Power Apps will retrieve a small amount of data, and this will speed up the app and improve the user experience.
PowerApps Delegation limit
The default delegation limit in PowerApps is 500 records. Delegation does not apply to data sets with less than 500 records, they will work correctly without any issue. Also you can increase this limit to be up to 2000 records.
- Open your app
- Go to File Tab >> App settings
- Click on Advanced Settings.
- You can set the value: “the Data row limit for non-delegable queries” to be up to 2000 records.
But if your data sets exceed the limit defined in your app, it will work correctly with the first 500 records. “Assume that the defined limit is 500.” but it will not work correctly with other records.
If you have 600 records in your data set and you want to retrieve the last 10 records, in this case, it will return the records from 490 to 500 instead of returning the correct records from 590 to 600. So it’s important to understand the delegation if you want your app to work correctly.
PowerApps Delegable data sources
As we know, there are many data sources that are supported to work with PowerApps, but not all of them are delegable. You will find a few data sources that support delegation in PowerApps. These data sources support the delegation process of the data, similar to what PowerApps do.
Some of the most popular delegable data sources are:
- Dataverse ,
- SharePoint,
- SQL Server,
- Dynamics 365 (CRM)
- etc.
Unfortunately, the Excel data source is not delegable, but it depends on whether your excel file is stored in the cloud or stored locally in the app.
If you stored your excel file in the OneDrive for Bussines for example, then you have to fetch the data source through the network traffic, in this case your Excel data source not supporting delegtaion.
But when you store your excel file locally in the app as a static data source, then you will not facing any PowerApps delegation problems, as there is no network traffic and your data is locally stored in the app itself.
When performing operations, like filtering or searching for specific records in PowerApps, they are performed at the data source instead of bringing the whole set of data to the app and then processing it locally, so PowerApps delegation assigns the work to the data source instead of the PowerApps itself.
Although you use delegable data sources, you get a delegation warning, That’s because the delegation not only depends on the data sources but also depends on the functions that you use in your formulas.
Delegable Functions
As there are delegable and non-delegable data sources, there are delegable and non-delegable functions.
For Example, Filter, Search, LookUp, Sort, SortByColumns, Sum, Average, Min, and Max functions can be delegated.
AddColumns, DropColumns, RenameColumns, and ShowColumns partially support delegation.
For more details, you can check Understand delegation in a canvas app.
How to overcome the PowerApps delegation warning?
You will receive a delegation warning if you use a function that is not yet supported by the data source.
You will see a blue line under some parts of the code, along with a warning triangle.
To Avoid the delegation warning,
- Be aware of which functions and operatives can be delegated back to the data source and how delegation works, so try to use all the possible Delegable functions.
- You can Change the 500 limit value up to 2000 as we discussed at the beginning of this article in the PowerApps Delegation limit section.
- If you have a large dataset, You can create some Static views instead of dealing with the whole data. For example, When using Dataverse table as a data source you can select a view instead of using the whole data source.
Overcome delegation when using Search & in
Search & in are non-delegable One common alternative is StartsWith (which is Delegable),
One “trick” with StartsWith is to make the Default of the text box where the user enters the text “”(empty string), so records would start to filter as soon as the user started typing in the box.
Filter(
MyList,
StartsWith(
FieldName,
TextBoxName.text
)
)If you must do a Search or In Filter looking for strings inside text or fields values in a list, then consider the Collection alternative.
Overcome delegation when using IsBlank
IsBlank is non-delegable so the bellow formula will give delegation warning
Filter(
MyList,
IsBlank(FieldName)
)Use Blank() to overcome the delegation
Filter(
MyList,
FieldName = Blank()
)Create Power Apps Collections Over 2000 items.
Getting more than 2000 records into our apps from a single data source has always been one of the most frequently asked questions by PowerApp developers.
In this case, we will deal with a collection that is created from a SharePoint list data source. As we know, the SharePoint list doesn’t exceed 4000 items, so how to create a Power Apps Collection? Over 2000 items, exactly the 4000 items?
Answer
- If you have between 2,000 and 4,000 records, you can do this,
- Sort the SharePoint list in an ascending order and then Collect the first 2000 items in collection1.
- Then sorting the SharePoint list descending and collecting the bottom 2000 items in collection2.
- Then merging the lists and then removing duplicates in NewCollection.
Concurrent(
ClearCollect(Collection1,Filter(SortByColumns(List,"ID",Ascending),ID>0)),
ClearCollect(Collection2,Filter(SortByColumns(List,"ID",Descending),ID>0))
);
ClearCollect(NewCollection,Collection1,Filter(Collection2, Not(ID in Collection1.ID)));Clear(Collection1);Clear(Collection2)
the two collections are merged, elimininating duplicates, into NewCollection and the two collections, Collection1 and Collection2 are emptied.
Now the NewCollection contains up to 4000 unique rows and can be used with all Powerapps functions without delegation warning.
You can work with the newest 2000 records in the list if it will fit your case and do the job
ClearCollect(
colList,
Sort(
MYList,
ID,
Descending
)
)Apply field categorization in which each option has fewer than 2,000 items.
Suppose on your Projects list you have a field for the project Status “New, Open, In progress, Completed” with less than 2,000 items in each group. You can then perform the following collection:
With(
{
ColNew:
Filter(
ProjectList,
Status = "New"
),
ColOpen:
Filter(
ProjectList,
Status = "Open"
),
ColInProgress:
Filter(
ProjectList,
Status = "In progress"
),
ColCompleted:
Filter(
ProjectList,
Status = "Completed"
)
},
ClearCollect(
colProjects,
ColNew,
ColOpen,
ColInProgress,
ColCompleted
)
)Collect Data In sets with For All Loops
A ForAll function can be used to collect several sets of rows from a data source that match a list of supplied values. The only limitation is each individual set cannot exceed 2,000 rows.
but the total size of the collection can exceed 2,000 rows.
Conclusion
To avoid the delegation warning in PowerApps, try wisely to use your data source, You can use SQL Server, Dataverse, or SharePoint, and you can increase the rows of your data by increasing the value above 500 records.
