Learn how to update columns for files in libraries that are locked/checked out/need approval without raising the version number
The challenge
If you are working with an IMS/DMS system, you probably have a library with approval, file that needs to be checked out, and an approval flow.
During your approval flow you might want to update some of the metadata, but SharePoint requires your flow to check out the document, change your columns, and then check in the document again.
It could also be that a document is open by the user, and your flow will stop, because the document is locked by that user.
Solution
Instead of using the good old Update file properties we are going to update the file properties using the Send an HTTP request to SharePoint
action
Step 1
We will start our flow from a custom button, so the first action should be For a selected file
Step 2
Insert an Send an HTTP request to SharePoint
action with the following options
Method
POST
Uri
_api/web/lists/getbytitle('NAMEOFYOURLIBRARY')/items(ID)/validateUpdateListItem()
Change NAMEOFYOURLIBRARY and ID to the ID from your trigger
Headers
accept
application/json; odata=verbose
Content-Type
application/json; odata=verbose
Body
{
"formValues": [
{
"FieldName": "Editor",
"FieldValue": "[{'Key':'i:0#.f|membership|admin@alexanderhenkel.dk'}]"
}
],
"bNewDocumentUpdate": true
}
It is important that you keep the Editor, as it should always be available. You can change i:0#.f|membership|admin@alexanderhenkel.dk
if you have the claims available in your flow.
bNewDocumentUpdate
must be true, to update your metadata without incrementing the version number.
Your HTTP request should now look like this
Step 3
Our flow does not update any columns yet. Let’s begin with a simple update. I have a text field with the internal name Comment let’s update that field with a text.
{
"formValues": [
{
"FieldName": "Editor",
"FieldValue": "[{'Key':'i:0#.f|membership|admin@alexanderhenkel.dk'}]"
},
{
"FieldName": "Comment",
"FieldValue": "We successfully updated the metadata"
}
],
"bNewDocumentUpdate": true
}
Now try to run your flow
(if you don’t know how to trigger a flow from a custom button, you can see how to in this blog post)
If we check the version history, you will se that the file metadata has been updated, without incrementing the version number.
Try to create a Word file, save the file, but don’t close it and run the flow. Your column should still be updated, even though the file is “locked”
Let’s update some other columns.
Hyberlink
{
"formValues": [
{
"FieldName": "Editor",
"FieldValue": "[{'Key':'i:0#.f|membership|admin@alexanderhenkel.dk'}]"
},
{
"FieldName": "HyberlinkField",
"FieldValue": "https://alexanderhenkel.dk, alexanderhenkel.dk"
}
],
"bNewDocumentUpdate": true
}
Managed metadata (single value)
{
"formValues": [
{
"FieldName": "Editor",
"FieldValue": "[{'Key':'i:0#.f|membership|admin@alexanderhenkel.dk'}]"
},
{
"FieldName": "ManagedMetadataField",
"FieldValue": "Managedmetadataname|ManagedmetadataID"
}
],
"bNewDocumentUpdate": true
}
Managed metadata (multiple values)
To update a multichoice metadata field, you must combine the values with ;
Managedmetadataname|ManagedmetadataID;Managedmetadataname2|Managedmetadata2ID
If you are getting your values from another field/file, you can Append to array variable and then use the Join action
Person field
The person field can be updated just like the editor field. If you have a multichoice person field, it must be combined with a ,
Note: You need to add the Claims value, not the Name/Displayname etc.
The below code use the Created by and Edited by so you can copy/paste it directly into your flow for editing.
{
"FieldName": "PersonField",
"FieldValue": "[{'Key':'@{outputs('Get_file_properties')?['body/Author/Claims']}'},{'Key':'@{outputs('Get_file_properties')?['body/Editor/Claims']}'}]"
}
File name
To update the filename we can use the FieldName FileLeafRef
{
"formValues": [
{
"FieldName": "Editor",
"FieldValue": "[{'Key':'i:0#.f|membership|admin@alexanderhenkel.dk'}]"
},
{
"FieldName": "FileLeafRef",
"FieldValue": "MyNewFileName.png"
}
],
"bNewDocumentUpdate": true
}
Here you might want to get the filetype instead of hardcoding it (replace .png with .expression). You can do that by using the following expression (this is taken from the trigger output)
last(split(triggerOutputs()?['body/entity/fileName'], '.'))
Approval status
Note that you can not update the approval status in the same HTTP request as you update all the other fields with. You need a second HTTP request, after you have updated your fields.
Note that editor has been removed
{
"formValues": [
{
"FieldName": "_ModerationStatus",
"FieldValue": "1"
}
],
"bNewDocumentUpdate": true
}
My Power Automate
Here is the entire Power Automate code I build, writing this post
{
"formValues": [
{
"FieldName": "Editor",
"FieldValue": "[{'Key':'i:0#.f|membership|@{triggerOutputs()['headers']['x-ms-user-email']}'}]"
},
{
"FieldName": "Comment",
"FieldValue": "We successfully updated the metadata"
},
{
"FieldName": "HyberlinkField",
"FieldValue": "https://alexanderhenkel.dk, alexanderhenkel.dk"
},
{
"FieldName": "ManagedMetadataField",
"FieldValue": "@{outputs('Get_file_properties')?['body/ManagedMetadataField/Value']}"
},
{
"FieldName": "PersonField",
"FieldValue": "[{'Key':'@{outputs('Get_file_properties')?['body/Author/Claims']}'},{'Key':'@{outputs('Get_file_properties')?['body/Editor/Claims']}'}]"
},
{
"FieldName": "FileLeafRef",
"FieldValue": "MyNewFileName.@{last(split(triggerOutputs()?['body/entity/fileName'], '.'))}"
}
],
"bNewDocumentUpdate": true
}
This blog post was inspired by
robertschouten.com
reshmeeauckloo.com
See also
- Change status for a list item with JSON (choice column)
- Create dynamic dates in Power BI with OData
- Make a download button for SharePoint Library files
- Disable the folder ID for SharePoint document integration
- Trigger a Power Automate Flow with modern ribbon, with a custom button in Dynamics 365 (not Ribbon Workbench)