How to handle delegation in Power Apps


I'm always excited and enjoy working on projects that include Power Apps. I handle such projects and tasks by first preparing a proof of concept or demo of the final application, but you will agree with me that, when you are using lists to load or created data on, over the time this list will grow and at some point, you will reach the power apps limit of 2000 items, and this is if you've increased the default value from 500.

This 2000 items limit means that power apps will do the work on the first 2000 items only.

And then the problems start, as you won't be able to retrieve all items from the list or show all items in a gallery. Then you have to use all kinds of workarounds such as filtering the gallery, deleting items from the source lists, and whatever you make to keep up with the 2000 items limit.

I've also never liked the delegation warning when working with filter, lookup, and count functions. you get this annoying warning:

The <Search, Filter, Lookup> part of this formula might not work correctly on large data sets

I've been looking for any way to make this message disappear, but finally, I found how I can show much more than 2000 items in my gallery, without jeopardizing the app performance



So let's begin!


To make the scenario more real, for the purpose of this article, I will create a SharePoint Online list containing products. The list will have around 8000 products, so we can even exceed the maximum SharePoint list view items limit.

I mean, let's admit it, most of the time you have lists with 5000/10000/20000 items or more.


Let me make this clear, SharePoint has a 5000 view items limit, not that you cannot have more than 5000 rows in the list, in fact as per Microsoft documentation, you can have up to 30 million!

To guarantee the app performance, Power Apps also have such a limit, but with 2000 items instead.


The first thing you need to do is to create a new number column in your list, i will call mine ProductCount.


Next, I will create a new index column, and this is where I will use the newly created ProductCount column. Basically, the ProductCount column will become an index column.




What are SharePoint Index columns?


An indexed column in SharePoint is used to improve the performances of the list or library view loading. If your list contains thousand of records, and you often query (filter or sort) by a given column, you can index that column.

this is actually the key to resolving the 2000 items limit in Power Apps, by using the Index column in the source list.

This column ProductCount will store nothing but the list item ID, so when a new item is created the column will store the ID value.


Let's build an app

We've prepared our list, it is time to test this in Power Apps.

I've created a screen with a gallery control, text input (which I will use as a search bar), and a label (to show the item's count).


Item property of the gallery is set to my SharePoint list with Products.

The first you will notice is this annoying warning message, telling me that if the list has many items I might have issues with retrieving all items.

So, OnStart of the app I will create a new collection (ColMaterials) that will get all products from that SharePoint list (all 8000).


But wait, the collection also applies the 2000 items rows limit? Since I haven't changed the settings of the app to get 2000 instead of 500 rows, I can have up to 500 items in my collection, so I will actually collect all the items from the list in chunks of 500 (which is the maximum) by using the index column ProductCount.


ClearCollect(
    ColMaterials,
    Filter(
        Materials,
        ProductCount <= 500
    ),
    Filter(
        Materials,
        ProductCount > 500 && ProductCount <= 1000
    ),
    Filter(
        Materials,
        ProductCount > 1000 && ProductCount <= 1500
    )

By adding, I can collect a total of 1500 items, so I will keep adding rows until I get all 8000 items from my list.


Filter(
        Materials,
        ProductCount > 7500 && ProductCount <= 8000
    ) 
Note: If you decide to increase your app row count limit to 2000, then the you will change the Filter to <=2000, >2000 && 4000, 4000&&6000, and so on. However, i dont recommand doing so as increasing the row count limit from 500 to 2000 with further decrease the performace as you will call 2000 items at once, rather than 500.

Now, every time OnStart of the app, I'm calling the SharePoint list and collect all items.

I can apply the same formula OnVisible of the screen in case I have multiple screens that request data from this Collection. That way I use the collection to search or filter items, instead of calling the SharePoint list every time. This further improves the app performance as I make this call once only at the beginning, or on the opening of the screen, in case I have patched something to that list.


Returning back to our screen, I will change the Items property to ColMaterials, and you can see that I'm getting all items (or at least the maximum that I set to collect).


For comparison, I will add another Gallery and label so we can see what is the difference when connecting to the SharePoint list and to the collection.

Filter(
        Materials,
        ProductCount > 7500 && ProductCount <= 8000
    ) 

As you can see, on the left side is my internal collection, the gallery shows on delegation warning, and I can retrieve all items. On the right side is the gallery connected to the SharePoint list Materials, and I can search within the app limit of 500 items.


Having now my collection I can perform all actions against, count rows, lookup, and filter data, without causing delegation warnings or performance issues, as well as allowing more items to be collected from the source list.


Share and leave comments under the article.


Stay tuned for more interesting solutions, or reach us in the contact form if you need help with building a custom solution for your business.



30 views0 comments

Recent Posts

See All