Daily Archive SharePoint List

[StephenGW]

Hello,

I have a large SP list. I would like to move multiple items daily using a Flow but I'm unsure how to do this. I know how to start with the recurrence trigger set to daily, next would probably be to Get Items from the list but I'm lost from there.

Is this possible?

Thanks

[ScottShearer]

@StephenGW 

You'll want to include a filter query in you Get items action so that you only get back records that should be archived.  Here is a link to a blog post on how to construct your query.

After your Get items action, you will want to loop through all of the items, create a new entry in the archive list (create item) and delete the original item (delete item).

Also, be sure to go to settings for the Get items action and set Pagination to On and set a threshold of up to 5000 items (higher if you have a premium license.

If you need additional help after you give this a try, provide additional details and I'll create a Flow that shows you how you can meet your requirement.

[StephenGW]

@ScottShearer 

I read through that blog and searched for others but I'm not seeing how to limit the Get Items to only the oldest Items over the number of Items I set.

My list has 1822 items in it. I would like for the flow to look at the list every morning and see how many items there are over the 1800 item limit. So in this case there are 22, that number will be different everyday depending on how many new items were created the day before. Then I would like to get the oldest 22 items and move them to the Archive list.

I think I can figure out the Apply to Each and put the Create Item and Delete Item inside it to copy them and delete them if it's that simple. But I need help with the ODATA filter to only get the items over the limit. Can you help with that?

Thanks,

[StephenGW]

I imagine it working something like this but I can't figure it out. Sort by date Oldest to Newest. Then count the total items and subtract 1800. Then limit it to the top n (count - 1800) items and then move and delete?

Am I on the right track?

[ScottShearer]

@StephenGW 

Please see my example below.

Be sure to set Pagination on the Get items and Change limits on the loop.

The loop compares the value of the variable to the output of the ToBeArchived Compose action.

My assumption is that there will always be at least 1800 items in the list.  If not, a condition is required.

I am also assuming that there won't be more than 500 items to be deleted - if there will be set the change limit count as appropriate.  I am also assuming that the loop won't take more than 1 hour to complete.  If I am wrong, set the loop time out as appropriate.

[StephenGW]

@ScottShearer 

Couple things, when I get to the create item part it automatically puts in an Apply to each.

Should I be putting that outside the Do Until? I don't see the Apply to Each in your screenshots?

Next, I forgot to mention that the list items have attachments that need copied over as well. Sorry, is it as simple as adding in a get attachments and content somewhere?

Thanks

[ScottShearer · Accepted solution]

@StephenGW 

Sorry - my error.  See my example below.  There is a more elegant solution which would be much more complicated.  My solution works, but is not the most efficient.

The steps up to the Get Items 2 action in my new Flow are exactly the same as in my original solution.

[StephenGW]

@ScottShearer 

Ok I think I got that built but it's giving me an error from the TotalItems

length(outputs('Get items')?['body/value'])

Any ideas on this?

[StephenGW]

@ScottShearer 

You can disregard my typo in the TotalItems. I forgot the underscore in Get_items. I have this built but now I will have to wait until tomorrow or Monday to check and see if it works.

Question, if this runs on Saturday and nobody is here to add items on Saturday, the number of items should be 1800. You said it was important that the number was never below 1800 right? Will it be a problem if the number is right on 1800?

Thanks

[ScottShearer]

@StephenGW 

I would add the condition in the screen shot below immediately after the TotalItems Compose action:

[StephenGW]

@ScottShearer 

I have added the condition to stop it if the number is not over 1800. I will report back tomorrow or Monday and mark an answer if it worked.

Thanks for the consistent help

[StephenGW]

@ScottShearer 

Great news! The flow worked exactly as I needed. I have marked the solution.

Thank you for your consistent and great help!