It has been difficult to understand the two together, the delegation and the data row limit (the default is 500), but it was easier to understand when I tried to figure it out again, so I made a note of it.
Understanding Delegation
First, delegation means letting the data source (in this case, SPO list) handle data filtering and sorting instead of Power Apps.
For instance, applying a delegable filter to the SPO list looks like this, with the filtering done by the SPO list.
On the other hand, applying a non-delegable filter (
except the ‘=’ operator, the SPO list’s ID column is non-delegable) results in Power Apps handling the filtering after retrieving all data.
The choice of who handles the data processing is delegation. This part is straightforward, but it gets complicated when the ‘data row limit’ setting comes into play.
Data Row Limit
The data row limit can be set in ‘App settings’, determining the amount of data that Power Apps can process locally.
My image of this setting is like a checkpoint that only allows through the amount of data set in the limit.
And, when the ‘data row limit’ and ‘delegation’ mix, it becomes quite complicated…
Scenarios Where Desired Results Are Not Achieved
So, this time, I’ve listed a few scenarios where the intermingling of delegation and data row limit prevents achieving the desired results.
Scenario 1: Delegable but Caught by Data Row Limit
For example, applying a delegable filter to the premise SPO list like this,
Filter(SPO list name, Num < 7)
↓ results in this figure, where delegation works but some data (ID 6 item in this case) is missing.
It looks like this when executed in Power Apps.
Scenario 2: Non-Delegable and Caught by Data Row Limit
Applying a non-delegable filter to the premise SPO list like this,
Filter(SPO list name, ID < 7)
↓ results in this figure, also failing to achieve the desired result (item with ID 6 not shown).
It looks like this when executed in Power Apps.
Scenario 3: Data Row Limit Okay, but Non-Delegable
Applying a non-delegable filter to the premise SPO list like this,
Filter(SPO list name, ID > 7)
Since the relevant data are only three items with IDs 8, 9, and 10, it seems doable, but because of non-delegation, it results in this,
The correct result is not achieved.
Scenario 4: Partial Delegation Possible - Case 1
Applying a partially delegable process (only SortBy is delegable) to the premise SPO list,
Filter(SortByColumns(SPO list,"Num",Descending), ID < 7)
↓ results in this figure, where all items that should appear (items with ID 5 or less) are missing.
It looks like this when executed in Power Apps.
In this case, even if the formula is changed to allow complete delegation, some data (item with ID 1) is missing due to the data row limit.
Scenario 5: Partial Delegation Possible - Case 2
Changing the order of the previous formula results in a different outcome.
SortByColumns(Filter(SPO list, ID < 7), "Num", Descending)
It results in this image,
But still, the desired result is not achieved.
So, when dealing with data from SPO lists or similar sources and you want to:
- Store it in variables
- Aggregate it
- Display it in list boxes
you need to be mindful of both 'delegation' and 'data row limits'.
Bonus: Galleries and Data Tables Are Special
However, using
galleries or
data tables is a bit special. If delegation is possible, you can display data exceeding the 'data row limit' (in this case, 6 items).
This is probably because gallery controls have a 'lazy loading' feature. By performing multiple data retrievals up to the 'data row limit', all data can be displayed (I think).
Note that if delegation is not possible, these controls still cannot display data exceeding the 'data row limit'.
Related Articles
コメント