Power Apps Limit: By default , you can only pull 500 records into Power Apps at a time. Your data source can have thousands of records but you can only display/pull 500 records in one shot.

So I created app app for a car leasing company. Drivers are assigned cars to pick up. The start screen displays tasks for the day. Then the users selects start tasks and the first task for that day loads (they do not get to choose which task they do first). Now, I want to pull from a SharePoint list all items that are assigned to, for today and are not completed yet.

This should give us well under the 500 item limit but….

When your source has over 500 items, you can only filter with some filter properties but not all, see Microsoft documentation. The filters I needed did not work anymore once the list hit 500 items. So items 1-500 pulled but the 501st item and onward did not show up.

Well, you can change your Canvas app setting and increase the threshold to 2,000 (2,000 is the most it can pull) but that does not solve the problem. My data source has more than 2000 items so filters that I need don’t work.

Solution:

First, pull a subset of data to a collection using a simple text filter that should return less than 500 items. (If there is more, you may have to pull multiple collections and galleries and then use that to create the illusion of paging to show all items). Then pass that to the gallery and do the complex filtering on that.

On App Start, the app will collect from the SharePoint list called Tasks, all items where Completed (single line of text field) is equal to No.

Formula:
ClearCollect(colAllTasks, Filter(Tasks, Completed = “No”))

This is a simple text filter and returns less than 2000 items from a larger list.

Now I can pass this to my gallery and filter it further using more complex filtering. The gallery’s data source is now the collection that contains less than 500 items. The gallery is filtered further to only display records where the Username is found in the Driver field (its a multi select field so I need to use “contains” and not “equals”) and Start Time field is today. Both of these filters to do not work on data sources over 500 items.

Formula:
Sort(Filter(colAllTasks, User().FullName in Driver && IsToday(‘Start Time’)), ‘Start Time’, Ascending)

Now, we can pull the records we need!