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