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

[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?

[ScottShearer]

@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?

[tomholb]

@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.

[ScottShearer]

@tomholb 

Can you post a screen shot of the first run?

[tomholb]

 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.

 

 

[ScottShearer]

@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.

1. Create a new Flow that starts with a button
2. Add a Get items action on the original list
   1. Turn on pagination and set the threshold to 5000
   2. Sort by ID asc
3. Add a loop to loop through the items returned
   1. Add a compose action inside of the loop - set it to the created date
4. Run the Flow

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

[tomholb]

@ScottShearer 

See below! 

 

[ScottShearer · Accepted solution]

@tomholb 

Great!

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

[tomholb]

@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?

[ScottShearer · Accepted solution]

@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.

[tu22]

can anyone help with this

[ScottShearer]

@tu22 

More info would be helpful, but I can say that you need to format your date as follows: yyyy-MM-dd

[tu22]

Hi,

the filter query was pulling the right filters as i wanted but always returned empty output. The date format matched the text field I was trying to match.

I am trying to update a another sharepoint list based on an updated column in another list. Hence the filter query 

even though it gets the filter query right, it returns empty and doesn't not update the column I need it to