Unanswered

Get Items from SharePoint List is empty despite hundreds of thousands of rows

(0) Share

Report

Posted on by tomholb

Ignoring the awful reality that is storing almost 700,000 records in a SharePoint List, I'm trying to archive records by using a Power Automate Flow to move records into separate smaller list (as in records created in 2020, records created in 2021, etc).

The Flow was set to grab the top 5000 records where Created Date was between 01/01/2020 and 12/31/2020, sorted ascending. With that list I created them in a new list then deleted them from the old list.

The first time this ran it worked perfectly.

The second time it ran it found no records for 2020. I confirmed records still exist for 2020.

This is what my Get Items looks like

Is it possible that the Delete from List action does more of a soft delete in SharePoint and the Get Items still sees those first 5000 records but is unable to collect their data?

Categories:

General topics

I have the same question (0)

All responses (12)

Answers (2)

ScottShearer 25,270 Most Valuable Professional on at

Like (1)

Report

@tomholb

Are you saying that your Flow ran without error but the items did not get created in the new list and were not deleted from the original list?

If so, take a look at the output of the first time that you ran the Flow. Can you see in the run that new items were created and existing items deleted?

Was this reply helpful? Yes No
tomholb 13 on at

Like (0)

Report

@Scott Thanks for the reply.
I'm saying that the flow ran without error last week (I went and checked the raw input and output as you suggested to make sure), but now is collecting 0 items when it goes to run and execute the Get Items action despite there being several thousand items that it should retrieve.

Was this reply helpful? Yes No
ScottShearer 25,270 Most Valuable Professional on at

Like (0)

Report

@tomholb

Can you post a screen shot of the first run?

Was this reply helpful? Yes No
tomholb 13 on at

Like (0)

Report

Of course! I've had to block off some details since this is a semi-confidential dataset and I work for a government agency, but let me know if you require additional information/screenshots.

Was this reply helpful? Yes No
ScottShearer 25,270 Most Valuable Professional on at

Like (0)

Report
@tomholb

I'm not quite sure yet what the issue may be but I would like to try a couple of tests if you don't mind.

I am assuming that you have the standard license that comes with M365.

Create a new Flow that starts with a button

Add a Get items action on the original list

Turn on pagination and set the threshold to 5000

Sort by ID asc

Add a loop to loop through the items returned

Add a compose action inside of the loop - set it to the created date

Run the Flow

Please let me know what you see in the loop as far as the created date please.

Was this reply helpful? Yes No
tomholb 13 on at

Like (0)

Report

@ScottShearer
See below!

Was this reply helpful? Yes No
Verified answer

ScottShearer 25,270 Most Valuable Professional on at

Like (1)

Report

@tomholb

Great!

No add the filter query and run it again and post a new screen shot of the run please

Was this reply helpful? Yes No
tomholb 13 on at

Like (0)

Report

@ScottShearer
Adding in my filter query and running it worked. I incorporated it into my original flow and it appears to be doing just fine now.
Not quite sure I understand what caused it. Was Pagination Key? Using ID to sort instead of a Date?

Was this reply helpful? Yes No
Verified answer

ScottShearer 25,270 Most Valuable Professional on at

Like (0)

Report

@tomholb

I have seen some weirdness when I specify top count - that may have been the issue. Top count is not necessary in your circumstances.

Also, you could substantially speed up your Flow by having the delete item action immediately follow the create item action in the same loop. The second loop is likely adding a bunch of time.

Was this reply helpful? Yes No
tu22 44 on at

Like (0)

Report

can anyone help with this

Was this reply helpful? Yes No