Counting Beyond your Delegation Limit in Powerapps
Most of the time a query (e.g. get me all active users) can be delegated to whatever is storing the data. This means that SharePoint or SQL will do the search and return data in pages of 100 at a time.
Where a query cannot be delegated, the filtering has to be done by PowerApps instead, meaning it has to go off to the data source and get EVERY* item, then look through them for the ones that are actually wanted.
*The delegation limit is that “Every”.
The thing is, if PowerApps was allowed to go off and actually get every item from the datasource to do it’s filtering, it could potentially be trying to load millions of records which would make it really slow. so a limit to that “Every” is introduced as the delegation limit.
This means, if a query can’t be delegated, then PowerApps actually only knows about the first 500 (although it can be increased to a maximum of 2000) items in a list. So, if you do a search (that can’t be delegated) of all users for a specific user, unless that user appears in the first 500 records, PowerApps won’t know that it exists.
How to work around the Delegation Limit
Having done some experiments with PowerApps and trying to count the number of rows that fit a criterion from a SharePoint list, the data row limit has posed several issues with regards to counting.
For my examples, I have a Data Row Limit of 2, to simplify my testing. We have a Client’s list, with 10 items, and the 10th item has 192 client contacts.
First Attempt: Countif
CountIf is a non-delegable function, this means that if you have 600 items in your list, and the items you are counting start at position 501, you will have a count of 0.
Results in: 0
This is because the non-delegable function can only see the first 500 items (or whatever your Data Row Limit is.
There is a delegation warning telling us that what we are doing is being restricted.
Second Attempt: Count Rows and Filter
This is better as Filter is delegable and will return items from anywhere within your data source. However, if you are trying to count more items than your Data Row Limit allows, you will see that limit as your maximum count, as CountRows is non-delegable.
Results in: 2
Again, there is a delegation warning telling us that what we are doing is being restricted.
Third Attempt: ForAll looping to Count
This feels like a more complicated approach, but removes the delegation warnings, so we feel like we are getting somewhere.
This approach uses the filter function to delegate our query to SharePoint, for all items returned returns 1 and sums all the numbers together.
We are no longer getting delegation warnings. However, the result we receive is: 2
The problem is although there is no issue with delegating our query to SharePoint for our filtering, the ForAll function’s return table is limited by the Data Row Limit as well so will only process the first 2 items in the list.
Fourth Attempt: Using a Gallery
Another option that has been investigated is to use a gallery, as that will iterate through items loading as many as possible.
To do this we added a gallery control to the page with the Items property set with the items filtered:
Again, there are no delegation warnings as PowerApps is happy loading a Gallery control with items from SharePoint and can delegate our filter.
Unfortunately, PowerApps will only load 100 items at a time so for our example with a delegation limit of 2. This counts more than anything else therefore it is still not a viable solution.
Fifth Attempt: Timers
This attempt was to keep looping through getting the top 2 (our data row limit) items until we’ve got everything, then add that count to a total, until everything has been counted.
This required a button to trigger the count (this could easily be done on page visible instead) and a timer to loop through all the items.
Our button / page load function starts by setting up the system to start counting:
Then our timer OnTimerEnd function is as follows:
The basics of this function are to retrieve the maximum number of items we can (FirstN where N is our data row limit), then if we have contacts, and are supposed to be running the count, and the top Id is not the one we have just counted then we add the sum to our current count and continue until we stop the timer.
There were some issues discovered during developing this solution that have ended up in additions to the If statement. Initially we were just checking that there were items in the TempContacts collection. However, there appeared to be some latency issues and the counter would carry on well beyond when the “RunCount” variable had been set to false, therefore the “RunCount” condition was added. Secondly, it appeared that when the last items had been loaded and maxId had been set to the Id of the last item for the query, the FirstN query was still returning the last items, so the final check to ensure the last item in the loaded TempContacts was not the previous maxId needed to be added.
The result of this was:
When working with a delegation limit of 2 this is a very slow function, even with the timer duration period set to 1. However, it does get the correct answer. The higher your Data Row Limit, the fewer iterations are required to complete the count and the faster it will be.
Of course, one issue with this approach is the required addition of the “LookUpId” which is a number field populated with the item’s ID. This is because, out of the box, the Filter function cannot delegate the greater than ID query.
Sixth Attempt: Store the count
A simpler solution is to have PowerApps maintain the count. This approach only works if the only way to add / delete items is through the PowerApp itself. This can be done by having a “ClientContactCount” property on the parent object and each time a contact is added or removed, increment or decrement the number using a patch statement.
This of course has the issue that it may get out of sync with the true number of items associated with the parent.
Seventh Attempt: Maintain the count outside of PowerApps
If a live value isn’t required, then using Flow to automatically count your items on a schedule is an alternative. To achieve this, a timer triggered flow was created to retrieve all clients, then for each client count the number of contacts, updating a client count value:
Here, an important thing to remember is to increase the “Top Count” to more than the default “100”, otherwise your maximum count will be 100. When doing this you will also have to update the settings of the connector to use pagination:
The count function can be generated as follows:
Many of these solutions may be appropriate for your PowerApp, depending on your requirements and the number of items you need to count.
If you have a data row limit of 500 and know you will only ever have a maximum of 500 items to count, then any option from “second attempt” onwards will solve your problem, with #2 likely being the most efficient solution.
If you are going to have a significant number of items to count beyond your delegation limit and you require real-time item counts, then either the 5th or 6th option would be your best solution depending on how able you are to maintain the correct count.
Of course, option 6 can be used along side 7, so you nightly ensure that your count is correct using a flow and increment / decrement the count within your PowerApp.
I believe Microsoft are currently changing the way count works in PowerApps and is improving the delegation available for the function. However, for now, it appears that using a timer might be the only way to get a count to work.