PowerApps is a powerful platform for developing fast business applications, but sometimes excess manipulation and filtration of large datasets can cause PowerApps to lag, we can solve this issue by using the concept of Delegation.
1. What is a delegation?
In PowerApps, delegation is the process of assigning tasks to data sources instead of having PowerApps perform the tasks themselves. When working with large datasets, it is necessary to use Delegation, as it will limit the records that are sent between PowerApps and the data source.
As of now, only the mentioned data sources support Delegation.
- SharePoint
- SQL Server
- Dataverse
- SalesForce
2. Delegable functions
The following functions support delegations
- StartsWith
- Filter
- Lookup
- Sort
- Max/Min
3. Non-delegable functions
- First, FirstN, Last, LastN
- Choices
- CountRows
- Concat
- Collect, ClearCollect
- CountIf, RemoveIf, UpdateIf
- GroupBy, Ungroup
4. Delegates Examples
We have a SharePoint list with more than 5000 records. That SharePoint list is being shown in the PowerApps gallery.
4.1) Replacing “Search” With “StartsWith”
Search based on Title – We applied the search function to search the products with the product name and saw the delegation warning.
We saw the delegation warning when we used the following code.
Search(
Products_Catalogue, //SharePoint List
SearchTextInput.Text, //Input field
“Title” //Column Name
)
Workaround:
The alternative to this is the “StartsWith” function which is delegable.
The delegation warning was gone when we used the following code.
Filter(
Products_Catalogue, //SharePoint list
StartsWith(Title,SearchTextInput.Text)
)
4.2) Using a collection for dropdown search
Filter gallery based on Choice column– When we applied a “Status” column filter on the “Products Catalogue” list we saw the delegation warning.
Workaround:
Store the “Status” choices in a collection. We created a collection “colStatus” on the “OnStart” property of our application.
ClearCollect(colStatus,Products_Catalogue.Status)
Pass the “colStatus” collection in the “items” property of the “Status” combo box.
Then we used the following “Filter” code, and the delegation warning was removed.
Filter(
Products_Catalogue, //SharePoint list
Status.Value = StatusCombo.Selected.Status.Value
)
4.3) Sorting Gallery
Sort based on price and expiry data – We want to sort our gallery based on the “Price” and “Expiry date”.
There was a delegation warning when the following formula was used to sort the columns.
SortByColumns(
Filter(
Products_Catalogue, //SharePoint list
ExpiryDate >= DatePicker1_2.SelectedDate && ExpiryDate <= DatePicker1_3.SelectedDate
),
varSortColumn,
Ascending
)
The variable “varSortColumn” stores the name of the column that is set when the “Sort” icons are selected.
Workaround:
This delegation warning can be removed using a “Switch” function in the “Items” property of the gallery.
The delegation warning was removed when we used the following code.
Switch(
varSortColumn,
“ExpiryDate”,
SortByColumns(
Filter(
Products_Catalogue,
ExpiryDate >= DatePicker1_2.SelectedDate && ExpiryDate <= DatePicker1_3.SelectedDate
),
“ExpiryDate”,
Ascending
),
“Price”,
SortByColumns(
Filter(
Products_Catalogue,
ExpiryDate >= DatePicker1_2.SelectedDate && ExpiryDate <= DatePicker1_3.SelectedDate
),
“Price”,
Ascending
)
4.4) Finding Max and Min Values
Max- We want to show the maximum value from the “Price” column.
We added the following formula in the “Text” property of the “Label” and faced a delegation warning.
Max(Products_Catalogue,Price)
Workaround:
To resolve this delegation warning, we wrote the following formula on the “OnVisible” property of the screen.
This will sort the list in descending order of the price column and will store the “Price” of the first record in the “varMaxPrice” variable.
Set(
varMaxPrice,
First(
SortByColumns(
Products_Catalogue,
“Price”,
SortOrder.Descending
)
).Price
)
Then we used the “varMaxPrice” variable in the “Text” property of the “Label”.
The same above logic can be used for the “Min” function. The only difference will be sorting the “Products_Catalogue” list in the “Ascending” Order.
Sum– This function is not delegable. We want to show the sum of the “Price” column.
We added the following formula in the “Text” property of the “Label” and faced a delegation warning.
Sum(Products_Catalogue,Price)
Workaround:
You can use the collection to avoid delegation issues with the “Sum” function.
The other alternative that you could explore is using Power Automate and Rest API to calculate the sum and return it to PowerApps.
4.5) Finding Total Rows
CountRows– We want to count rows of the “Products_Catalogue” SharePoint list.
When we used the following formula, we got a delegation warning.
CountRows(Products_Catalogue)
Here we again used the collection because “CountRows” is not a delegable function.
We passed the collection in the “CountRows” function to remove this delegation.
