Working with Large Dataset in SharePoint List help

Copper Contributor

My PowerApp is getting data for a gallery with a search bar and category dropdown from a Sharepoint Event List. The list is currently at around 350 items, which still falls under the default 500 rows limit for non-delegable tasks. But within a year or two, it will easily exceed 1000 or even 2000 rows, and I'd like to find a way to futureproof this issue.


One solution I can think of is mixing delegable and non-delegable functions. Currently I'm using Search(Filter(DataSource, Category.Value = Dropdown.Selected.Value), SearchBar.Text, "Title"), basically pre-filter the SP List by the selected category, thereby reducing the row count so that Search() doesn't have to look through everything, just the chosen category.


This works pretty well, since my list has about 20 categories, so the list would have to hit 10,000 items before a category hits 500 items, which is a pretty safe bet.


One feature I would like to implement though, is for the gallery to only show items from the current day onwards. The list has items dating back to 2021, but if today is Aug 31st 2022, then the gallery should only show items from Aug 31 onwards.


To do that, I've modified the code to be: 

Search(Filter(DataSource, Category.Value = Dropdown.Selected.Value, 'Start Time' >= Today()), SearchBar.Text, "Title")

This adds an extra condition to the pre-filter, to only read items from the current day onward. The problem is that the new bit is non-delegable, meaning that regardless of category, once it hits the 500th item, it stops reading the list. I've tested this by changing the limit from 500 to 200, and the gallery was completely empty, implying that it could not read the more recent events.


Is there a way to prefilter the Data source with the Start Time column with a delegable function?


0 Replies