How to Query and Pull from Large SP List --> Gallery

[Anonymous]

Hi all,

I am very new to Power Apps but have some experiencing in Sharepoint and Power Automate.

I have built a SP list that will have anywhere between 2k and 5k records at any given time.

I have a canvas app with a horizontal (backend) gallery that pulls AllItems from that SP list.

I then have another visible gallery that will filter those items and display only mine (or the user who is using the app).

Note this list is meant to only filter "open assigned tasks" for that single user. Therefore they could have anywhere between 0 and 50 at any given moment. I mention this so no one thinks it's an absurd amount.

My issue is the following:

Whilst the app is open (being run) it will not pull in new data even if refreshed. The only way to obtain new data is to make the (backend) gallery visible and scroll all the way down, only then will my main visible gallery update and display new records.

How to go about always having access to that data, those items that I would otherwise have to scroll infinitely for? I need my users to essentially see their open tasks without having to access some backend gallery and scroll indefinitely till they populate?

For reference here is what I have:

Backend (invisible) gallery: This is called TicketData

Sort('NameOfSPList',Created,Descending)

Main (visible) gallery: 

Filter(TicketData.AllItems,AssignedTo.DisplayName=Office365Users.UserProfileV2(User().Email).displayName && Status = "New" Or "In Progress" And StartsWith(Title,searchQuery))

Thanks in advance,

[CNT]

@Anonymous The Maximum number of records you can pull in one call from any datasource is 2K. This is called Delegation. This is to keep the App performant. So you will never get all your 5k records unless you manually collect them in batches of 2K. Further, Galleries have their own limit and will not display large sets of data unless you start scrolling. That's how they are designed to work. Again, to keep the App performant.

From your requirement I can see that you need only about 50 records. So you can filter them at the source. You must let the backend do all the heavy lifting. This will keep your App Happy!

So, rather than having 2 Galleries you can do this in your Main Gallery,

Sort(Filter('NameOfSPList',AssignedTo.DisplayName=Office365Users.UserProfileV2(User().Email).displayName && Status = "New" Or "In Progress" And StartsWith(Title,searchQuery)),Created,Descending) 

Hope this helps!

[CNT]

@Anonymous If I've answered u'r question,

Please remember to give a 👍 and accept the solution as it will help others in the future.

[Anonymous]

@CNT Thank you for the prompt reply.

I have a question, is it, however possible, to: upon refresh and or opening the app for the first time, have that backend gallery scroll all the way down to the bottom automatically? Essentially mimicking if one were to manually scroll to the bottom? This would, with a few seconds of delay, populate my Main visible gallery with it's designated records. Or am I wrong? 

This is something I found which I think goes deeper into what I think I'm attempting to achieve: https://powerusers.microsoft.com/t5/Building-Power-Apps/Brainstorm-How-to-get-a-Gallery-to-Auto-Scro...

I would also like to note, the way I have it setup, as mentioned above, I currently have no delegation warnings.

thanks again!

[CNT]

@Anonymous Firstly, you can jump to a particular item in your gallery by setting the default property of the gallery to that item. But still this will scroll to that item only if that item is in the gallery's current view (meaning how much has been loaded and that's beyond our control). So, there is no guarantee that you will get to that item.

Secondly, in the link u've found, the same things is mentioned, "pull in another 2k and repeat as many times as needed to add to the collection."

Thirdly, u'r Formula is not showing any delegation warning b'coz you are not using any non-delegable function. So, no warnings doesn't mean, u'r immune to delegation.

Hope this helps!

[Anonymous]

@CNT 

Thanks again. One last question if you don't mind.

So I am covered and deemed safe if there are 2k or less items I'm pulling. The real question is, since my users will never have 2k items assigned to them at any given time, am I OK? However, the main SP list itself will hold anywhere between 2 and 5k items. The fact that I am filtering my main gallery now in Power Apps to only pull what I want to see, does that cover me?

Like for example, right now I am pulling 20 items. These 20 items are mine. But it's finding these with that filter you provided me there from a list with 3k items (right now). As long as what passes thru my filter is not more than 2k am I good? Or is the fact that my datasource itself holds more than 2k items currently going to be an issue?

Thanks!

Edit: I guess what I'm confused about is, 2k total items to query from or 2k total items I can "pull" thru a filter

[CNT]

@Anonymous Your datasource can have millions of records. But as long as you filter them at the datasource and get <2K records into your App, you are safe. Just a note about filtering: Depending on the datasource certain filters are non-delegable. Watch out for delegation warnings! For detailed info, please check the docs, https://docs.microsoft.com/en-gb/powerapps/maker/canvas-apps/delegation-overview

If you need more help just @ mention me. Hope u'r all clear now!

Please remember to give a 👍 and accept the solution as it will help others in the future.

[Anonymous]

I must be annoying you here,

Before I had two galleries and 0 delegation warnings. I guess it was pulling what I needed? With your filter and a single gallery I have multiple instances where it triggers a delegation warning.

And just to be clear, my Power Apps filter will do that hard work of sorting through thousands of items and only getting what I need to see in the App and as long as (in my case) a single user does not have more than 2k tickets assigned they are OK? Note my datasource holds all the tickets assigned for all my employees. This is why my datasource is big but what I want in the app isn't. I only need them to see what is open and have that information readily available to them.

[CNT]

@Anonymous The default limit in 500 If you want to put it up to 2K, u'll have to change it in the settings as follows,

Which part of the formula are you getting the delegation warning?