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]

I currently have over 10,000 items housed by folders in a document library. Is there a trigger or a way to copy those existing ideas to a new column within the same list? I understand when creating a new item, and it will be a flow I need to implement for future proofing. I'm more concerned about the existing documents within sharepoint.

[hnguy71]

@Anonymous 

There are a few ways I can think of to achieve what you need using a 1-time excercise:

1. Create a manual trigger flow to iterate through the entire list, in similar fashion as to the powerapps code, filtering for where your new column is blank and updating those items. I give this a moderate difficulty since you'll need to know a bit more advanced Flow. 

2.  Use SharePoint Online Management Shell to run a script to update entire list. I give this a hard difficulty because it takes some time and effort to set up, create the script, and finally wait for the script to finish running.

3.  Reverse collect in PowerApps any new column is blank and do a ForAll & Patch with ID. This should be straight-forward. I give this low difficulty but it's a very manual method since you can only collect 2000 items at a time without delegation limits.

4.  Use Microsoft Access, go to External Data, import your list or library, filter the new number column for any blanks, copy and paste ID into new column, send query to SharePoint. I give this a low difficulty since the application is most likely installed on your machine, no code necessary, and feels like Quick Edit. This is probably the recommended option for you.

I have built a solution for options 1 and 2 but sadly they're on my work machine and I'm out of the office until next week. If you can wait a week then I can share with you. 

[Anonymous]

Thank you so much! I look forward to seeing you solutions. I'll try to figure it out till then.

[hnguy71]

Hi @Anonymous 
I'm back at the office. Did you still require help?

[Anonymous]

@hnguy71 I would like to see your solution.

I came up with a brute force idea. I created a new document library added in the number column to copy the unique file ID and used "When a file is created" in flow, so now in SharePoint I copied all the folders from the original library to the new one. Flow gets triggered due to the files being created, and the ID gets copied to the number filed as expected. It's not an elegant solution, but it did what I needed it to do.

[hnguy71]

Okay great, that's good to hear! I'll send out a powershell script which is easier to follow than the Flow.

Connect-PnPOnline -Url https://MySite.Com

$myList = "My List name"
$ListItems = Get-PnPListItem -List $myList

foreach ($Items in $ListItems) 
    {
        Set-PnPListItem -List $myList -Identity $Items.Id -Values @{"ColumnName" = "UpdateColumnValue"}
        }

And here's a high level flow run:

Inside the condition for interations is quite complex:

At the end of the flow, update records

[Anonymous]

Wow thanks! I'll try it out. Thank you so much for the help and advice!

[Anonymous]

@hnguy71 

Hey,everything is working great! There is one issue I can't seem to figure out though. It involves the original content of the thread to get past the 2000 item/file limit.

In sharepoint I have 12,512 records. Doing a countrows in powerapps to verify I have all items only displays 12,350.
I will look deeper into the code to see if there's something I need to adjust. In the meantime if you can provide any thoughts to why not all the records are being collected would be great. Thanks!

[hnguy71]

@Anonymous,

On the top of my head I can only think of two things. I believe the original solution assumes the following:
1. Your list has never deleted any items.

2. Your FirstRecord is really the first record from the list

You may immediately fix point number two by changing the local variable FirstRecord to have it sorted by ascending:

UpdateContext({FirstRecord:First(Sort(YOUR_DATA_SOURCE,ID,Ascending))});

I have since changed my solution and I can share with you the updated (and more complex) version when I head back into work next week which hopefully fixes your issue.

[Anonymous]

I'll try that next week, and yeah I'll like to see the updated version maybe it can help with what I'm trying to do. Thanks!