Do you have a need to automate the move or copy of documents from one SharePoint location to another based on document metadata rather than relying on end-users to do the move/copy? Do you also want these documents to be turned into records once they’re moved to their new location?
[Update January 4, 2018] An alternative Flow technique was added to ensure custom metadata would be copied for non-Office documents as well. Details are added in Step 6.
This is a common request I see and traditionally has been accomplished thru several means. You could build a SharePoint Designer workflow or you could set up a “Send To” connection in either Central Admin or the SharePoint Admin Center (depending on whether or not you are in an on-premises or online environment) and then use that connection in an Information Management Policy (IMP) to automatically Transfer to another location based on a date.
Although you can still continue to use these techniques in both SharePoint On-premises and Online environments, I wanted a more modern approach using Microsoft Flow to accomplish the same thing. With the current investments Microsoft is making in Microsoft Flow, I think this is a better way of providing this functionality, particularly if you’re faced with building a brand new solution today in SharePoint Online.
What’s the setup? There needs to be a piece of metadata to “trigger” the move. This could be any kind of metadata (a status, a person’s name, a date, etc.), but in this blog post we’ll set up a date column that, when it is more than 1 week past, will trigger a move to another library location.
This is a key improvement of Microsoft Flow over an Information Management Policy(IMP) to trigger the move. Unless you wanted to write custom code, you were previously limited to only a date column when configuring an IMP. With a Microsoft flow, you can trigger the move on any column data type Microsoft Flow supports!
We’ll also configure a Retention label and default the destination library location to the label, thereby automatically turning all documents added to it into records.
The remainder of this post will walk-thru the steps to build a minimal viable product using Microsoft Flow as a replacement solution for the legacy Transfer to another location feature and Send To connection functionality.
Setup
- Site Columns:
- A datetime site column, EventDate, to determine when documents are to be moved (1 week after the event date)
- A lookup column, EventName, to identify the event the document is for. This column is not required for this Flow, however may be leveraged in future blog posts for an automatically-generated folder name at the destination.
- Content Type: Although not required, I’ve created a content type called Event Document to be associated to both the source and destination document libraries. It includes the 2 site columns from step 1 above. Both columns are required.
- Source Documents: this is the document Library to move from. In this example, the source document library is called WIPDocuments. (WIP = work in progress)
- Destination Documents: this is the document library to move to. In this example, the destination document library is called FinalEventRecords.
- Retention Label: New label called Permanent to be set as the default on the Destination Documents library, FinalEventRecords. Retention labels are configured in the Security & Compliance Center. This label is configured to retain forever and will make the document a record when the label is applied to it. Once you define the label, it needs to be published to the Site Collection where these documents will live. It can take up to 1 day for the label to appear in the library. Once it does, edit the library settings for the FinalEventRecords library and set the default retention label as follows:
Building the Flow
Launch Microsoft Flow. The diagram below is the overall flow we’re going to build. I’ll walk thru each of the 7 steps in the details that follow.
Step 1 – Recurrence
I started with a “Recurrence” trigger because we want to run this flow every weekday to move all documents with an EventDate more than 1 week in the past. The below image shows we will run this Flow every day at 8AM local (Saskatchewan) time. Yes, my Province has its own timezone. 🙂
Step 2 – Get past time
For a quick post on some new additions for working with dates and times in a Microsoft Flow, refer to this recent post by Microsoft: Working with dates and times.
I want to trigger the move on the date field, EventDate. When that date is 1 week in the past, we want to move the document to the FinalEventRecords library. To get the date to compare to, I will use the new action Get past time to get 1 week in the past from the current time. It will automatically store it in a property, Past time.
Step 3 – Get files (properties only)
Once you have the above date set, you can easily filter the query against your source document library to only retrieve those documents with an EventDate more than 1 week past. This will give us a list of documents to move. To do this, use the Get files (properties only) action pointing to the source library and filter the query as follows:
EventDate lt datetime'<output from previous Get past time step>’
Important: There is no such thing as a ‘Move’ action in Microsoft Flow for a document in a SharePoint library however we can accomplish the same thing by doing a copy from the source to the destination library followed by a delete of the source document.
Steps 4 and 5: Get file metadata for each file
Start by adding an ‘Apply to each’ section to iterate thru the list of filtered documents; this will provide us with an array of files. As of the time of this writing, the filename extension is not included in the property array (the Name property is only the part of the filename before the extension). Since we require the filename extension to do the copy, we perform a ‘Get file metadata’ action on each file to get it:
Step 6 – Copy file
The previous step outputs 2 properties we will use in this copy step: Path and Name. These properties both include the filename extension and we will use them to build the Source and Destination File Paths.
This will successfully copy the document to the destination library. Also, since we’ve previously set the default retention label on the FinalEventRecords library to be Permanent, all documents added to it are now declared records. Sweet!
[Update January 4, 2018] I discovered that although the ‘Copy file’ action described above copies both content and metadata properties for Office client documents, it does not copy custom metadata properties for PDFs (likely any non-Office document). This is a common requirement in many organizations so to get around this, replace the ‘Copy file’ action with the 3 steps shown below and it will work for any type of document. I’ve included an image of the entire Flow for context:
Please refer to a great blog post written by Paul Culmsee on The (new current) best way to update SharePoint document metadata using Flow to explain the technique I’m using below for the 3 (new) steps:
Step 6A – Get file content
We need to get the file blob so we can create a new file from it. That is found by calling the Get file content action on each file.
Step 6B – Create file
Create the file in the destination document library, FinalEventRecords.
Step 6C – Update file properties
This step will take the ItemId output from the previous Create file action and copy any additional custom metadata configured on the source document library, WIPDocuments, to the newly created file in FinalEventRecords.
The 3 steps above accomplish the same thing as the Copy file action did in the original Flow, but with the added benefit that they work for both Office and non-Office documents!
Warning: this blog post is building a minimal viable product only.
Something to watch out for in this copy step is the possibility of duplicate files. If the filename already exists in the destination library, this step will fail if the Overwrite Flag is set to ‘No’. You would need to account for this in a production scenario.
Possible workarounds:
- ensure the filename is unique. You could do this by adding a datetime stamp at the end of each filename. With the native ‘Send to connection’ functionality built into SharePoint, unique filenames are ensured by appending random letters to the end of the filename if a duplicate is detected.
- overwrite it if it exists (however you won’t be able to do this if you’ve declared the destination documents as records)
Step 7 – Delete file
The last remaining step is to delete the document from the source library, WIPDocuments, with the action, Delete file. We will use the identifier from the Get files (properties only) array of properties to identify the correct document to delete:
Where do we go from here?
This blog post provides the framework for automatically moving documents from one library to another based on a piece of metadata on the source document using Microsoft Flow. This functionality is what the legacy Send to connection and Transfer to another location functionality was providing as well. Although this new solution works and I plan on implementing it as a modern alternative to the legacy methods, there are a few key areas requiring some attention:
Detection of duplicate file – we need to ensure filenames are always unique. There are ways to do this and I will cover that in a future post.[Update: January 6, 2018] Refer to this post for one method to ensure the filenames will be unique: Ensure Unique Filenames when Copying Files with Microsoft Flow- Depending on volume of data over time, you may want to build a foldering system into your library structure to mitigate the risk of exceeding the listview threshold error.
Although the content type and custom metadata will copy over to the destination library if you have the same content type or site columns on both the source and destination libraries, this does not work for PDFs.[January 4, 2018] I’ve updated this post to include a technique to allow custom metadata to be copied for all files (including Office and non-Office files like PDFs) by replacing step 6 with 3 new steps.
I’ll focus on the remaining areas on subsequent blog post(s).
Thanks for reading!
-JCK
Credit: Photo by James Pond on Unsplash
Great write up, Joanne! How do you manage the issue where the file in the new location is “owned” by the person who set up the Flow – and thus has the connection? See my UserVoice entry ‘Add the capability for tenant service accounts’ (https://office365.uservoice.com/forums/264636-general/suggestions/32808823-add-the-capability-for-tenant-service-accounts) for details on what I mean.
Hi Marc,
Thanks for the feedback! Our team has set up a fake user (service) account to initially create any Flows we build. Our security team hates it but there’s no better mechanism in place at the moment. I read your UserVoice entry and gave it some votes! Great idea. Thank you for posting it.
Joanne
In the case of what you outline here, you’d really rather have the ownership stay the same as you move the file, I’d assume? It would seem to be even more important given you’re making the files into records by applying a label in the destination. Even using the service account, that account takes over ownership.
These are the kinds of scenarios it’s important to get in front of the Flow team at Microsoft. I love where Flow is going, but these issues really concern me, as I think they will discourage uptake in many enterprises.
M.
Yes, it certainly would be better if it retained the original created by/modified by credentials. Version history wouldn’t be a bad option either. I might put an idea in for something like that as it has come up several times already for customers I work with.
JCK
Why did you decide to not simply declare the records in place?
M.
For this particular client, they have a work-in-progress library where they prepare for board-level meetings. Once the board meeting is complete, the final board meeting minutes and related material need to be retained as official records. They wanted all official Board Records retained in a separate library. (Possibility of different permissions as well)
Although it could have been done in-place, I think there was value in separating out the wheat from the chaff in this case.
JCK
I am not seeing a ‘Get past time’ as a schedule action? Am I missing something
You need to select ‘Recurrence’ as the Flow trigger. Once in the Flow, search for ‘Time’ as an action and it will be in there.
Dose this also get the files from a nested level floders
Hi Rajesh,
No, it does not. This was a minimal viable product and would need to be adjusted to accommodate folders and nested folders.
-JCK
Hi,
I am using your sample to build a mvp, however managed metadata columns are not copied the right way across site collections. Any ideas on this? Did you perhaps experience this as well? Or can you explain why this could happen?
Thanks!
Hi vdk, this is difficult to troubleshoot but the first thing is to ensure the managed term set is defined at the tenant level and not the site collection level. Other than that, I haven’t tried to do that yet so I can’t speak to that specific use-case.
-JCK
Great write-up Joanne. I’m having an issue getting past the query filter. I set up a test library with several documents, and a target test archive library. I added the EventDate column to both, and set up step 3 exactly as you have it, EventDate lt datetime ‘Past Time X’, where Past Time X is the dynamic content from the previous step. Every time I run the flow, I get the following error:
The expression “EventDate lt datetime ‘2018-10-11T20:04:22.7674615Z'” is not valid.
clientRequestId: b62cc776-fe17-4e55-820a-58c2d3e05765
serviceRequestId: 4166b49e-b0a7-7000-5bfd-c634fd871448
I’m new to MS Flow, so this may be a simple resolution, but I’m just not seeing anything.
Thanks for your help.
Hi Tom, it’s always tricky to debug someone else’s Flow particularly thru comments like this. One simple thing to verify is please ensure you do not have a space after the datetime function call and your single quote. In your example, it looks like there is one. Other than that, I don’t have anything else to suggest other than carefully verifying the syntax of your call as that is the error it’s throwing.
Hope that helps.
-JCK
Hi Joanne,
My apologies as I totally missed your response. Thanks for the input. I’m not sure what I did, but I did manage to get past that issue, so all good.
Tom
Hi Joanne,
thanks for this great post.
A senior manager wanted to send only the latest file from a SPO Document Library to his manager at regular intervals.
After using the “Get Files (Properties Only)” action, with an “Order By”: “Modified desc” and “Top Count” of 1, I was able to get the latest file information. This was then passed to an “Apply To Each” loop (in case more files are required later) in which I have a “Get file metadata” action followed by “Get file content”. Finally I send an email with the file content added as an attachment.
The explanation of how to get the file and contents from a Document Library is excellent and has helped me quickly fulfill the requirements.
*Of course I don’t have the original file being sent and I would have preferred to send a link to the file, but the preference was for the file contents
Sorry Joanne,
forgot to sign.
Thank you.
Paul 🙂
Hi Paul, thanks for the feedback, happy it helped!
-JCK
Hello Joanne,
Being new to Flow, your post has helped me understand it’s use better and saved me a few headaches.
For the Flow process I’m working on, I looking to move files from the source document library to 100+ destination document libraries. The source and destination libraries all live under the same site address. One of the source file metadata tags is ‘BranchID’ and the destination document libraries are all named after branch IDs.
If I added ‘BranchID’ from the Dynamic Content list to the Destination File Path field in the ‘Copy file’ section, would the file get copied to the destination document library that is the same as it’s metadata tag for ‘BranchID’? I hope I explained it well enough.
Thanks,
Josh
Hi Josh, I think I understand what you want to do. I just set this up really quick and it absolutely works. You just need to provide the ‘BranchId’ property in the ‘Destination folder’ setting. Works like a charm as long as the document library always exists of course.
-JCK
Joanne, thanks for the response. Updated the flow and it’s working great. If file metadata is already getting added/updated in the source library before moving to the destination libraries, could I exclude ‘Get file metadata’ from the Flow? The source library and destination libraries are using the same content type.
Also, during my testing today I noticed the Flow wasn’t moving all the files. It’s stopping at the 5 to 7 mins mark then marking the run as ‘Succeeded’. I’ve set the Recurrence trigger to run every 15 mins to make sure all the files get moved. Is this best practice or is there a way to get the Flow to run until all the files have been moved?
Thanks,
Josh
Very interesting article but is there anyway to do this without creating custom columns? I simply want to send an email if there have been new items added to a SharePoint document library within the past 24 hours.
I tried changing your EventDate lt datetime’’ to Created gt datetime’’ since ‘Created’ is one of the default columns in a SharePoint library. However, this did not work and I keep getting the error:
“status”: 400,
“message”: “Column ‘datetime’ does not exist. It may have been deleted by another user.\r\nclientRequestId: e96e3a2d-a0ce-4035-842d-7a7e4de6e3b1\r\nserviceRequestId: e96e3a2d-a0ce-4035-842d-7a7e4de6e3b1”
Hi David, it should work. The error looks like it doesn’t like your syntax. Have you created a variable for 24 hours ago and then referencing that in your condition? If I get a minute, tomorrow, I’ll give it a try.
-JCK
Hi Joanne,
Great Post.
This solution can be used to move documents from one library to another.
However if we want this to be implemented at site collection level which means have the flow running for all the documents within subsites and document libraries, it is practically not possible to create flows manually for each library.
Can we have flows associated to a content type so that its reflected for all the libraries where ever this content type is being used for documents.
Thanks
Prajakta
Hi Prajakta, no you unfortunately can’t.
-JCK
Great article! Thanks Joanne.
I created an archive flow with a couple of minor issues:
(i) As Joanne says, the “Get files (properties only)” filter query will error if there’s a space after datetime and the ‘ – – so it should be ArchiveDate lt datetime’PAST TIME’
(ii) Also, the Copy file action in Joanne’s tutorial is now deprecated (but still available in Power Automate).
There’s a newer ‘Copy file’ action. To use this, instead of “Destination File Path”, there’s “Destination Folder” – you can specify a target document library – eg /Archive
‘Source File Path’ is now ‘File to Copy’ – you can still use ‘Path’ as the value.
There are options what to do if another file is in the target location; for instance Replace, Copy with a new name, or Fail the action. Also possibilities of a custom value.
‘Copy with a new name’ will add a number at the end of the target (copied) file name – so ‘Test.docx’ would be renamed to ‘Test1.docx’. This may not be ideal if you want a more robust solution. The custom values may give you more flexibility – for instance to add a text string to the end of the filename.
Copying the file won’t retain any of the version history. If I find out a solution to this, I’ll post back here!
Jon
Thanks for the update!!
■ Success! ■ If you use the [Move file] action (replacing the Copy & Delete actions) – this does retain the version history on the file.
You do need the same columns/column types on the target document library as on the source – otherwise you’ll get “Missing or mismatched field definition on the destination list for source field ‘Archive’ type ‘Boolean’ (etc) ” errors.
Interestingly, this action also deletes the original file as well as moving it. You’ll find the file/s in the Recycle Bin, deleted the same time the Flow runs. This doesn’t happen if you move a file using the SharePoint UI.
Thanks for the update. This is a really old post I need to update with the new options!!
The UI certainly used to behave the same way where the moved file went into the recycle bin of the source site… I have received support calls in the past on that exact behaviour. Perhaps they’ve fixed that in the UI now. It truly is essentially a copy and delete.
Thanks again.
-JCK
Hi Joanne,
This was my first “go” with creating a flow and your instructions were superb! I was able to run this flow successfully with your great instruction. I just have a question about the file properties (column values) being copied from the old library to the new (archive library). I have both libraries set up with the same columns, input types, and values, but the column data was not transferred over to the new library. In the “Update File Properties” step, I noticed that it allowed me to select the values for each column, but I want them all to remain the same as they were in the old library. Is there a way to get that data to transfer with the document? Did I do something wrong? Any advice you can provide would be so helpful!
Thanks,
Kayla (Workflow newbie)
Hi Kayla, glad the post helped. Sounds like you’re almost there! It is absolutely possible… likely some small tweak you have to make. A few ideas:
– make sure you’re pointing to the correct source when copying your metadata properties. I say this only because in larger flows it’s easy to get mixed up on the data sources UI
– I don’t have time to check but since that post is not recent please check out the other action steps for copying files in SharePoint to make sure there’s not a better option now. I see I link to another post by Paul Culmsee for different ways of doing it as well
Sorry, that’s all I’ve got for now. When I have some spare cycles I’ll look at the post again and try to reproduce and enhance.
I’m tenacious with Microsoft Flow… I just keep throwing things at it until I get it to work. 🙂
-Joanne
Joanne, thanks for this post. I combined yours with Sudharsan K.’s post on moving files with Power Automate (https://spknowledge.com/2020/09/20/power-automate-how-to-auto-copy-or-move-files-in-sharepoint-library/), and it works very well for small numbers of files.
Another scenario that we’ve got, are departments wanting to move larger numbers of files (up to 100,000) once a year from the dynamic to the static library (archive). What would you suggest is the best way to approach a scenario like this?
Without testing and digging into your requirements, I would likely build an Azure Job to do this instead. It’s more robust and you can put in better error handling, etc.
Thanks for the answer. I was also thinking that this is better handled via Azure.
As for requirements: we want to move documents based on a date trigger field (or perhaps also the last modified date), to a separate library within the same site collection. By documents I mean any kind of file in the library. And we’re wanting to make this malleable -that is, that we can reproduce this in multiple site collections, where the library names and perhaps the trigger date field could change.
I had been looking at array’s and loops in Power Automate, but this seems really unwieldy to me.
At any rate, I hope you have a great holiday season!
This was a great read and got me pointed in the right direction. My flow seems to work correctly, however, if there are nested folders and I am pointing to the root folder, the destination site library (archive) does not create the folder, it dumps all of the files into the root. Anyway to maintain folder structure.
Hi Santo! I’m sure there’s a way for you to control that. I unfortunately don’t have the cycles to spend on trying it out and adding it to my post.
Perhaps ask this question on the Power Automate community on the tech community site:
https://techcommunity.microsoft.com/t5/communities/ct-p/communities#category-filter-button-all