Background
I generally think, in most cases, records should be separate from documents when we talk about management of content. Documents are normally created as part of a collaborative process whereas records are normally created as part of a compliance process. To try and support this principle, I tried to do a little mock up with SharePoint to send major versions of documents to another system, in this case e-mail, to mimic a record keeping process with minimal user interaction.
The problem, SharePoint doesn’t easily expose document version information. I tried searching around but didn’t particularly find much to help me out. That's where Microsoft Flow comes in.
Microsoft Flow
Microsoft Flow is a low code workflow tool that hooks into SharePoint Online and is fairly powerful. Using flow, I set about coming up with a workflow that would monitor a SharePoint Online document library and take some action when a major version was published. To do that, however I needed to be able to get the version information for the file. The solution to this, basically, was to use the SharePoint Online API to retrieve version information when a document changed and act on that information when the version is a major version change. Let’s crack into how the workflow is constructed.
Step 1 - File Change Trigger
The first step is to monitor SharePoint for file changes. Add this as your starting trigger and provide the Site and Document Library you want to manage.
Next, we add an action to get the file meta-data. Once again, supply the relevant site and add the dynamic “File Identifier” object passed from the trigger.
Step 3 - Get File Properties
Finally, we add the action to get the properties for the file. Here, we specify the site and document library again, and this time we pass through the ItemID of the file that has changed.
Step 4 - Send HTTP Request
Here comes the semi-tricky part. We use the gathered information from the previous steps in a Send HTTP Request action to retrieve some of the attributes SharePoint keeps about files. Once again, specify the site we are working with, set the request type to GET and use the following API end point to retrieve the document properties:
_api/web/GetFolderByServerRelativeUrl('$FolderPath')/Files('$FileName')
Make sure you replace $FolderPath with the dynamic “Folder Path” object and the $FileName with the dynamic “File name with extension” object.
Note: Previously, I was using the following endpoint to retrieve all the versions related to a file. Recently, it was pointed out that this endpoint does not contain the current version of the document. I’m not sure if this has changed recently or was an overlooked issue on my part:
_api/web/GetFolderByServerRelativeUrl('$FolderPath')/Files('$FileName')/Versions')
Step 5 - Parse JSON
The HTTP request action will return a JSON object that we now need to parse so add a Parse JSON action and specify “Body” object from the previous action. You have two options in supplying the schema. Firstly, you can run a test on the flow up to the step 4, copy the return result and click on the “Use sample payload to generate schema” option to define the schema. Or you can use the schema I generated below, which should be the standard, out of the box schema for SharePoint Online (at the time of this writing).
{
"type": "object",
"properties": {
"d": {
"type": "object",
"properties": {
"__metadata": {
"type": "object",
"properties": {
"id": {
"type": "string"
},
"uri": {
"type": "string"
},
"type": {
"type": "string"
}
}
},
"Author": {
"type": "object",
"properties": {
"__deferred": {
"type": "object",
"properties": {
"uri": {
"type": "string"
}
}
}
}
},
"CheckedOutByUser": {
"type": "object",
"properties": {
"__deferred": {
"type": "object",
"properties": {
"uri": {
"type": "string"
}
}
}
}
},
"EffectiveInformationRightsManagementSettings": {
"type": "object",
"properties": {
"__deferred": {
"type": "object",
"properties": {
"uri": {
"type": "string"
}
}
}
}
},
"InformationRightsManagementSettings": {
"type": "object",
"properties": {
"__deferred": {
"type": "object",
"properties": {
"uri": {
"type": "string"
}
}
}
}
},
"ListItemAllFields": {
"type": "object",
"properties": {
"__deferred": {
"type": "object",
"properties": {
"uri": {
"type": "string"
}
}
}
}
},
"LockedByUser": {
"type": "object",
"properties": {
"__deferred": {
"type": "object",
"properties": {
"uri": {
"type": "string"
}
}
}
}
},
"ModifiedBy": {
"type": "object",
"properties": {
"__deferred": {
"type": "object",
"properties": {
"uri": {
"type": "string"
}
}
}
}
},
"Properties": {
"type": "object",
"properties": {
"__deferred": {
"type": "object",
"properties": {
"uri": {
"type": "string"
}
}
}
}
},
"VersionEvents": {
"type": "object",
"properties": {
"__deferred": {
"type": "object",
"properties": {
"uri": {
"type": "string"
}
}
}
}
},
"Versions": {
"type": "object",
"properties": {
"__deferred": {
"type": "object",
"properties": {
"uri": {
"type": "string"
}
}
}
}
},
"CheckInComment": {
"type": "string"
},
"CheckOutType": {
"type": "integer"
},
"ContentTag": {
"type": "string"
},
"CustomizedPageStatus": {
"type": "integer"
},
"ETag": {
"type": "string"
},
"Exists": {
"type": "boolean"
},
"IrmEnabled": {
"type": "boolean"
},
"Length": {
"type": "string"
},
"Level": {
"type": "integer"
},
"LinkingUri": {
"type": "string"
},
"LinkingUrl": {
"type": "string"
},
"MajorVersion": {
"type": "integer"
},
"MinorVersion": {
"type": "integer"
},
"Name": {
"type": "string"
},
"ServerRelativeUrl": {
"type": "string"
},
"TimeCreated": {
"type": "string"
},
"TimeLastModified": {
"type": "string"
},
"Title": {
"type": "string"
},
"UIVersion": {
"type": "integer"
},
"UIVersionLabel": {
"type": "string"
},
"UniqueId": {
"type": "string"
}
}
}
}
}
This payload and JSON parse gives us a number of useful variables, including major and minor version numbers.
Taking It Further
Taking this a little further, here are some optional steps that I added to determine if the document was a major version (x.0), and if so mock out a “File as record” e-mail to prove the process.
Step 6 - Only Work With Major Version
I only want to work with Major versions as from this workflow’s perspective, I am saving major versions as the record instance. So let’s add a condition to check to see if the latest version is a major version by checking to see if the minor version variable from the JSON parse is set to 0.
Step 7 - Example Dynamic Properties
This last step of the Flow shows an e-mail action that has a range of dynamic content specified that you can work with at the end of the flow. In this example, I am sending an e-mail to the creator of the file with information about the version that was published, any comments associated with the publish command run in SharePoint Online, as well as the file attached to the e-mail.
Bonus! - Saving the Version Number to a Document Property
In the comments on this original post, Eyo Sama and Dave Albrecht were having some issues saving the version numbers against the document so they can use them within the document itself. Let’s expand on the flow above to create a status field that has the current version. In SharePoint, add a new column (I’ve called mine status) to hold the Version label. When you add a new column, it becomes available in Word’s document properties and can be used in functions like Quick Parts.
Bonus Step 1 - Locked Boolean
The first thing we need to do is create a variable to test against the locked in use problem. Because we will be updating the document, we need to make sure it isn’t being locked for editing. Set this variable to true initially.
Bonus Step 2 - Loop
Next, add a do until loop so that we can retry adding the version to the document property if the document is locked.
Bonus Step 3 - Scopes and Document Checkout
The first action within the loop is to try and do a document checkout so that we can update the document properties without creating a new version. We want to try and check this document out within a Scope. A Scope in Microsoft Flow allows us to implement Try/Catch logic, that is we can try and do a number of tasks and output the result of those tasks. In this case, we are doing a HTTP Post request to check out the document so once again, specify the site we are working with, set the request type to POST and use the following API end point to check the document out:
_api/web/GetFolderByServerRelativeUrl('$FolderPath')/Files('$FileName')/CheckOut()
Bonus Step 4 - Check the Result
Now let’s check the result of the scope statement. We do this by checking the result of the scope we created using an if statement and the following dynamic expression:
result('Scope')[0]['status']
Scope in the statement above refers to the name of the Scope statement we added in the previous step.
Bonus Step 5 - Keep Running Even if It Failed
By default, Microsoft Flow will stop processing if an item within the flow fails. Because we are planning for this, we need to set the if statement to run even if the Scope group fails. Click on the three dots (…) on the if statement title bar and select “Configure Run After” and ensure all exit states are ticked.
Bonus Step 6a - Success! Update the Version
If the scope is successful, then we have checked out the file. If we add the update file properties item, use the site and library we have been working with, and specify the itemId, you should also see any other document properties associated with the document library. As you can see, the status document property I have on my library shows up and I’ve added a version label to this property.
Bonus Step 6b - Success! Check It Back In
Now we have the version property populated, let’s check it back in with another HTTP command. Again, specify the site we are working with, set the request type to POST and use the following API end point to check the document in:
_api/web/GetFolderByServerRelativeUrl('$FolderPath')/Files('$FileName')/CheckIn(checkintype=2)
We are using the check in type 2 to check the document back in without increasing the version number. As an FYI, type 0 is increase minor version, type 1 is increase major version and type 2 is overwrite with no version change.
Bonus Step 6c - Success! Close the Loop
Finally, we close the loop by specifying our lock variable to false. This ensures we don’t keep looping and repeating the work.
Bonus Step 7 - Handling Failure
Let’s handle the failure state if someone has the document open for editing or checked out. Firstly, create an if statement to check how many times we have attempted to check the document out. We do this because we do not want Flow to loop indefinitely. I set my iteration count to 20. If the iteration count is equal to or greater than 20, set the Is Locked variable to false. This breaks our loop and basically closes the flow. You could also send a warning here if you wanted but I am working on the assumption that if we can’t update after 20 tries, someone is still changing the document and another save will come through. If we haven’t hit our iteration limit, let's add a delay so that we don’t keep trying every few seconds / minutes. In my case, I have added a delay of five minutes. Depending on your users, you could increase or decrease this as required.
Next Steps
So here is the completed (non-bonus) workflow from top to bottom.
If this was a production deliverable, we could now, for example, use this flow and add an action to call an API for a recording keeping system to HTTP Post the document and the gathered meta-data to save a record independent of a user's interaction. Or we could do any other similar actions.
History
This post has been updated to reflect some issues with using the versions endpoint and also to post Version data back into a document property.
