SharePoint lists may grow bigger with time, that’s no doubt. Depending on your user base, you might start to feel performance issues after a few thousands of items created. That’s why it should be good for you to looking after auto archive items in SharePoint using Power Automate.
Why Power Automate? It is a great tool with seamless integration with SharePoint which allows you to reduce the toil and generate value by automating tasks. If you haven’t checked Power Automate before, it is a good opportunity to start looking into the Low Code world.
Prerequisites before you archive in SharePoint
- Create the target list which will be the Archive. In our case the source list will be called ItemList and the target list will be ItemListArchive;
- Ensure you have an account with access in Source and Target lists;
- Add custom columns for Created By (Author) in the Target list. This is just to store the original creator of the item in the archive;
- Ensure the Content Types match between both libraries. It’s is a good practice to have Content Types configured in the Site Collection level.
Automating the archive using Power Automate
The automated flow can be scheduled in a recurring basis depending on your requeriments. Once you are good to go with that schedule, you can proceed to the main logic: Trigger + Compose Date, Get Items and Move Files.
Trigger + Compose Date command
This first part of the Flow will have the Trigger and a Compose command. The trigger is pretty straightforward and it’s up to you what to define. In our case, we are making it run every twelve hours. After that, we will have the Compose Date which is declaring a date equals to “Today minus 18 months”. This means we are going to archive anything older than one and a half year.
Compose inputs: @{addToTime(utcNow(),-18,’Month’)}
Get items command
In this part, the Get items command is going to get all the Items from one specific lists, ordering by Created column , taking the first 100 results and filtering anything Created before the date generated in Compose command, in other words, anything older than 18 months.
Get / Create / Delete item commands
At last, we will start archiving the items. Here you are going to get the item specifically, so you can load all fields. Once you got the data, you will proceed to create the item in the Target list. Then, we can delete the item in the source list.
In this step we will load all fields available for the current item in source list.
This second step creates a new item in the Archive list by copying the fields like Title and Description. Also, pay attention to the custom field CreatedBy Migration Claims! This field will store the original Author from source list for auditing purposes in any case.
This last step just deletes the item in the source list using the ID of the current item.
Conclusion
That’s it! The idea is pretty much recreating the item in the target list. You can also add more logic, for example, adding a new field to auto-tag specific version of the migration. Anyways, I hope you are able to Auto archive items in SharePoint using Power Automate after checking this post. Also you might find useful checking other posts related to Power Automate here.
References:
Power Automate documentation
Trying to get ID from Get Item and it is not showing from a SharePoint List that I am trying to archive (move to another cloned list) after 12 months.
All I see is value and body (List of items).
Thanks for any help, this Flow looks like what I want to do if I can get past this area.
Hi Denise,
How are you?
Could you confirm if the command you are referencing is Get Item or Get Items? Get Item should have the ID, Get Items should be an array of items that you need to find the ID.
Best,
Wiliam
Hi Wiliam,
I am working on the step to Get Item and add ID. That is where ID does not show up.
The next step is Create Item if that helps.
Thanks,
Denise
I see! Well, maybe depending on how you created the Apply to each, Flow is not being able to read the properties from it.
I suggest you move the Get Item to outside the Apply to Each. It can be even before Apply to Each. Once moved, delete anything in the Field Id. Try again adding the Id by looking for the column Id of the action “Get Items”. This should automatically create the Apply to each again with the Id column.
Another possibility is that you are not working with a List, but instead with a Library. If that is the case you can check this blog post: https://wiliammbr.com/archive-files-in-sharepoint-using-power-automate/
Best,
Wiliam
I got it by not using the Filter Query, Order By, Top Count and Limit Columns By View in Get Items. I tested each one of these one by one, if I use any of them I don’t get any properties.
The one I am working on now is how to get custom field “CreatedBy Migration Claims” in Create Item to show up.
I already added the Column “Created By” in the Archive List.
Yeah, depending on the View you selected on “Limit Columns By View”, the column ID might not show up. Good job!
After you added the column Created By, try to refresh your flow to see if the field shows up. If you get stuck, you can reach me on LinkedIn to share a print screen on what is missing there to show field.
Best,
Wiliam
Thanks! This is a good one to learn from.
I ran a test, it is copying to the Archive List, not moving, which is fine, I don’t want it to show my name in Created By column anyway.
I’ll keep working on this, thanks for your help.
If I get too frustrated I’ll reach out to you 🙂
Thanks,
Denise