Collect All SharePoint List Items Using ForAll Loop

[hnguy71]

Good morning, All.

I'm playing around with the idea to grabbing all sharepoint list items into a collection. I'm taking Mr Dang's approach located here:

https://powerusers.microsoft.com/t5/General-Discussion/500-item-limit-in-CDM-entity-search-filter-ne...

The only difference between his and mine is that I have a table with a generated series vs his static table.  I believe everything works up until the last line of code which gives me the following error:
"Delegation warning. The lighlighted part of this formula might not work correctly with column "ID" on large data sets."

I seem to be able to grab up to 2000 records but my test list has 3004 items. Any help would be appreciated!

Here's a debug test that I'm performing to see if I'm retrieving the necessary information (I'm expecting 3004 inside collection amount):

[Anonymous]

@hnguy71 

Hey, so I attempted to use your idea, unforunately it doesn't work. When you have time if you can share your updated solution perhaps it can properly collect all files/items.

It's an on going pattern I noticed. Regardless of the collection size, it seems to not fully collected all items. It comes pretty close, but will consistently only collect a certain amount. I will keep adjusting and troubleshooting on my end to see if I can successfully collect all files/items to be displayed in powerapps.

[hnguy71]

Hi @Anonymous 
Sorry I haven't had the chance to reply. I have been swamped at work. If you look at my original solution post, you'll find the updated formula that hopefully resolves your issue. If you encounter any other issues, please let me know.

[Anonymous]

@hnguy71 

No problem! I tried the newer version. It's still missing content. In sharepoint I have 12,607 files. The collection is only counting 12, 463 rows.

The original solution, and new solution both have the same amount of items collected. 12, 463.
I'll see what I can come up with. Very strange. I know you mentioned in your newer version it can retrieve ~ 2 million records.

[hnguy71]

Okay, @Anonymous  let's try and help you debug why you're not retrieving all of the required items.

1. is your number field indexed? 

2. have you set in PowerApps delegation limit to 2000?

3. can you search your library if your workflow has been applied to all items? Filter by where your number field is blank. 

4. remove the last section where the collection gets unloaded, what are the numbers you see in colIterations?

If possible, can I get a screenshot of each numbered item?

[Anonymous]

@hnguy71

1.I don't think the number field is indexed as I am not sure how to do that  I'll look it up and see how to index,.

2. I have the 2000 limit on

3.I can search my library with all items view 

4.. I'm not sure I'll have to try it out next week since it's the weekend now  

It's a strange issue. The programming makes sense. I wonder if it's something in sharepoint that's causing the issue  I haven't indexed the number field  so if that is required  possibly that might be the issue. 

[hnguy71]

As a good practice if you're going to be retrieving records from a large list or library, an indexed column would increase performance and especially within a list you'll be able to bypass the 5000 item view limit. Thinking on it a bit further, I really think number 3 would likely be your culprit. Often times when you trigger a large amount of workflows consecutively a few tend to fail or get skipped. 

[Office365Master]

I uploaded a video today that, amongst numerous other features, of an app I have created that showcases how you can easily browse through Lists or Document Libraries containing 10k+ items in PowerApps using the native paging functionality using the SharePoint v2 Graph APIs. (Per the attached screenshots and the demo video on YouTube)

https://youtu.be/gKL92VU6QwA

I was even able to bypass the PowerApps 2000 row threshold limit and increase it to a maximum of 5000 records returned with one Flow run. In fact with a single Flow run with a “Do Until” action you possibly process an unlimited amount of records.

That said though, I can now equally attest wrt why PowerApps threshold is constrained to 2000 row limit.

In essence unless you carefully limit the amount of information returned per record returned, you will frequently experience your apps crashing because of the time limits enforced by your web browsers. Put another way, the 2000 row limit more likely than not there because of limitations of the web browser and not PowerApps.

The Flow run itself consistently takes approximately 1 minute to retrieve 5000 records (per attached screenshot). Occasionally my web browser though would however time out after about 2 minutes. My assumption is that would no doubt be directly dependent on the speed of your internet connection to download however much data the Flow is returning.

If you can implement all your business logic within the Flow and don't actually need to return any data back to your app however all you'd need to do is add to Do Until action to the Flow to process each batch of up to 5000 records in your List / Document Library.

It is practically impossible for the Flow I created to fail as long your schema definition for the final Response action in the Flow is 100% correct. Equally it is impossible for any Flow run to be skipped or for that matter to miss any records using this technique.

I have also attached a screenshot of my Flow definition. 

That is as simple as gets to implement, inclusive of automated paging, i.e. zero effort to implement.

I will be posting a blog within the next week (hopefully) on exactly how I made all the cool stuff I've demonstrated in the demo video possible within a single PowerApp I created for the corrosponding blog:

https://masteroffice365.com

[keihimekawa]

Hi. I tried this and it worked with 1 filtering. My app have 2-3 filterings (all via dropdown) and it didn't worked on the second one. Is this a limitation? Filtering is "column1data = dropdown1.selected.result" and "column2data = dropdown2.selected.result"