In my last blog post, A Modern “Transfer to another location” in Office 365, I talked about an alternative technique to the legacy Send to and Transfer to another location functionality in SharePoint using Microsoft Flow. If you haven’t read that post yet, go check it out. I’ll be here waiting for you when you get back. 😉
The steps in that post built a minimal viable product to copy files from one SharePoint library to another based on a piece of custom metadata on the document. There were some key items identified at the end of that post requiring more attention before the Flow should be used in a production environment. In this post, I’ll address the first one – ensuring the copied file’s name is unique in the destination document library.
Approach: The legacy Send to functionality in SharePoint detects if a filename is duplicate and only then does it append random characters to the end of the filename to make it unique. Instead of implementing a similar approach with Microsoft Flow, I’ve decided to append the current date and time stamp to the end of every file before it is copied to guarantee uniqueness. This makes the implementation significantly more straight-forward while still satisfying the requirement for a unique name.
This post walks thru the steps to do this by adjusting the Microsoft Flow built in the prior blog post.
Let’s get started!
We start by initializing a variable, FileExtension, to store the file extension prior to retrieving the list of documents we want to process:
Get the File Extension
To manipulate the filename property before copying the new file, there are 2 properties at play:
- the filename without the extension (e.g. somefilename)
- the filename with the extension (e.g. somefilename.docx)
We need to extract the file extension from the filename with the extension so we can append it to the filename once we make it unique. To do this, add a Set variable action for the FileExtension variable after you’ve retrieved the metadata from the source file:
This is the expression to set the value of FileExtension:
last(split(body(‘Get_file_metadata’)?[‘Name’],’.’))
This splits the filename with the extension (in the Name property) into an array based on the “.” character and then takes the last item in the array.
E.g. If the filename was somefilename.docx, the FileExtension value returned would be docx
Refer to this link for a handy reference on using expressions in flow actions.
Build the Unique Filename
Once you have the file extension, adjust the File Name property setting in the Create file action to build a unique filename.
Build an expression for any format you want the appended date to appear in. My current timezone is UTC-6 so I’ve adjusted the current time, utcnow(), by 6 hours and then applied my chosen date format. In this example, I’ve concatenated an underscore, the timezone-adjusted current date and time stamp in a specified date format, the character “.”, and the file extension:
concat(‘_’,formatDateTime(addhours(utcnow(),-6), ‘yyyyMMddTHHmmss’),’.’,variables(‘FileExtension’))
E.g. This would build a string like this: _20180106T190810.docx for a docx file.
Append this to the name of the file without the extension and voila, you have a unique filename! Use this in the Create file action:
This will guarantee a unique filename is created in the destination library when the file is copied.
Thanks for reading.
-JCK
Credit: Photo by Nick de Partee on Unsplash
Hi, anyway to check the filename contain certain char and take the latest one?, eg. I got a file save in sharepoint doc library called “ABC – Jan’2018.xlsx”, “ABC – Feb’2018.xlsx” and “ABC – Mar’2018.xlsx” (the folder also have other file like “XXX – Mar’2018.xlsx” & so on). I want to flow get the latest file name of ABC, this can be done?
Hi,
There will be no easy way to do this based on the name as you are using alphabetic descriptions for the date. If you were using numeric definitions and your filenames were in a consistent format you may be able to do it that way. Instead of that, could you leverage the ‘Created date’ or ‘Modified date’ of the document for the “latest” file name of ABC? In this way, you could easily apply a filter to your query and order it by that column descending.
-JCK
Hi Joanne, great post. Thanks a lot.
I tweaked the datetime stamp in the filename a little bit to use the current time supporting Daylight Saving. For that I added an action “Convert time zone” between “Set variable” and “Create file” and updated the concatenate function to use the output of that “Convert time zone” action. The function now looks like this “concat(‘_’,body(‘Convert_time_zone’),’.’,variables(‘FileExtension’))”
Hi Dirk, sweet! That’s awesome (and a needed addition)! I’ll update the post when I get a chance.
Thanks!! 🙂
-JCK