Getting Power Apps to work with large Sharepoint lists can be tricky – but its doable! When you pay attention to indices you can even keep up a relatively good performance!
List view threshold seems to play a large role here. Currently for the Sharepoint Online it is 5000 items. That is the maximum number of items that you can manage or view through normal Sharepoint UI. Maximum number of items is totally a different thing. Currently you can have up to 30 million items in a list!
Since I’m writing about using Sharepoint lists as part of your Power Apps solution, we are not that interested about what Sharepoint UI can or cannot handle. Power Apps will access your lists trough API and that’s the case here.
The most important thing with using large Sharepoint lists is to remember that 5000 items limit. What ever you do, you should always think twice if that limitation is affecting you. If you’re complete new to this, I would recommend starting with reading the documentation about delegation in a canvas app. Using delegable functions you can access your items from a list as far as you won’t need more than 5000 items at a time.
I will concentrate here to using filter function. My remarks apply to both access from canvas app directly or from Flow. I had to think this for quite some while, so hopefully this helps you to get it a bit easier:
- First argument should contain a column that has been indexed
- Filter cannot return more than 5000 items when all arguments are combined, so I guess for most cases you want to design the query accordingly. You can have first argument that would return more items and then filter more with other arguments. This will however develop performance issue, and that is why most of the times:
- You want to make sure that the first argument limits items to less than 5000. This is a must to keep good performance!
Below you can find some examples which I observed investigating this:
Flow “Get Items” action returns only “@odata.nextLink”, when you expected to get some items. In this case your list contains more than 5000 items. You’re trying to fetch item that is found later than in the first 5000 items found by the first argument. Flow supposedly takes “nextLink” as that “top 1” and you end up not receiving item that never the less is on your list!
Power Apps monitor showing multiple pages of responses with “received 0 rows”. For my case this meant approximately 20 seconds added delay for the query, although the result remains the same.
Just because I/1 can 