Programmatically updating SharePoint list properties in Power Automate without affecting the version history
There have been numerous instances in my flows where I have needed to update the properties of SharePoint files or list items, but I did not want to add to the version history. You can achieve this by calling the SharePoint REST API. However, if you did want to add to the version history, you can do this also by changing one boolean value.
Calling the REST API
First I add a Compose action, and write the JSON. See the end of this blog post for examples of how to write the JSON code for the different field types in SharePoint.
The FieldName is the internal column name and the FieldValue is the value you want to give that column. In my example, I have used variables, child flow outputs, strings, concatenations and dates. Almost anything goes in between the quotation marks, as long as it suits the information type of the column (i.e. date, number etc.).
[
{
"FieldName": "Title",
"FieldValue": "variables('DocTitle')"
},
{
"FieldName": "FileLeafRef",
"FieldValue": "outputs('Run_a_Child_Flow_-_Compose_filename')?['Body']?['filename']"
},
{
"FieldName": "DocumentReviewStatus",
"FieldValue": "Under Review"
},
{
"FieldName": "WrittenReviewedBy",
"FieldValue": "concat('[{''Key'':''i:0#.f|membership|', variables('UserEmail'), '''}]')"
},
{
"FieldName": "FirstCreatedDate",
"FieldValue": "formatDateTime(utcNow(),'M/d/yyyy')"
}
]
Next, we make the call to SharePoint. You need to get your list item and call validateUpdateListItem(). In the Body section, add the output of your JSON that you composed above.
If you don’t want your version history affected, write true for bNewDocumentUpdate.
If you do, false will create a minor version.
_api/web/lists/getbytitle('<List Title>')/items(<ID>)/validateUpdateListItem()
{
"formValues": outputs('Compose_-_Form_values_for_SharePoint_request'),
"bNewDocumentUpdate":true
}
Once this code runs, the SharePoint properties will be silently updated.
If you check your file or item and notice that the properties haven’t updated, you might have some issues with your JSON code. Even though your HTTP request will say it successfully ran, you will have to check each individual column to see if it has an exception. If you see “HasException”: true, then your properties will not have updated. The error message will give you a good indication of what to fix.
JSON code for different field types
Text fields (single line and multi)
[
{
"FieldName": "TextFieldInternalName",
"FieldValue": "Text string"
}
]
Number fields
[
{
"FieldName": "NumberFieldInternalName",
"FieldValue": "123"
}
]
Yes/No fields
Yes/No fields do not accept yes or no. Instead, 1 = yes, and 2 = no.
[
{
"FieldName": "YesNoFieldInternalName",
"FieldValue": "1"
}
]
People fields (single or multi)
For multiple people, put a comma between the {}. For example, [{‘Key’:’i:0#.f|membership|user1@email.com’},{‘Key’:’i:0#.f|membership|user2@email.com’},{‘Key’:’i:0#.f|membership|user3@email.com’}].
[
{
"FieldName": "PersonFieldInternalName",
"FieldValue": "concat('[{''Key'':''i:0#.f|membership|', user1@email.com, '''}]')"
}
]
Date fields
Dates can be dependent on your column set up. If you have chosen date/time format, you should put the time as M/d/yyyy hh:mm tt or ‘g‘. For example, if you want to get the current date and time, you can write formatDateTime(utcNow(), ‘g’). If is date only, the format should be M/d/yyyy or ‘d‘. At the time of this blog post, all dates need to be in US format.
[
{
"FieldName": "DateFieldInternalName",
"FieldValue": "4/27/2020 11:00 AM"
}
]
Choice fields (single or multi)
For multiple selected choices, put a semi colon and hash between the items. For example, Choice 1;#Choice 2;#Choice 3;#.
[
{
"FieldName": "ChoiceFieldInternalName",
"FieldValue": "Choice 1"
}
]
Lookup fields (single)
You will need the ID of the item you are looking up. For example, for a SharePoint list, you will need the ID of that item in the list (this method includes if you have configured the list to look up other columns in your lookup list).
[
{
"FieldName": "LookupFieldInternalName",
"FieldValue": "1"
}
]
Lookup fields (multi)
Similarly to the single lookup field, you will also need the IDs of the items you are looking up. However, this field follows this format: ID1;#;#ID2;#;#ID3;#. Note, the double use of ;# in between the items, and only single use at the end.
[
{
"FieldName": "MultiLookupFieldInternalName",
"FieldValue": "1;#;#2;#"
}
]
Managed Metadata fields (single and multi)
You will need the GUID of the managed metadata term. To find this, go to your Term Store and click the term. You will see a heading for Unique Identifier. For multiple selected terms, simply join them together. For example, Corporate|96e3b62f-1a00-49fa-bad2-518f747148d0; Human Resources|116fa9e9-32d2-4a81-80b3-9b63b3c41411;.
[
{
"FieldName": "ManagedMetadataFieldInternalName",
"FieldValue": "Corporate|96e3b62f-1a00-49fa-bad2-518f747148d0;"
}
]
Hyperlink fields
In the past, hyperlink fields were split up into the URL and description. However, it is treated just like a normal text field now.
[
{
"FieldName": "HyperlinkFieldInternalName",
"FieldValue": "http://url.com"
}
]
This is helpful!
Can you help clarify something on the lookup column(s) piece? Are you putting the Lookup Column ID n the “Value” space and if so, does that mean that you are just reading the current value of the lookup field? Is it like a calculated field you’re pointing back to itself?
For instance, I have a Lookup field in List 1 named “Current Assigned User” (this field looks up to another field in the same list but only available on a specific content type). In a second list, I have a field named two fields named “Full Name” and “Selected Device” (the latter of which is also a Lookup Field). What I would like to happen is that when “Selected Device” is changed, the “Current Assigned User” field updates with the corresponding value from “Full Name” in List 2.
I am struggling to figure out how to write both my uri and the body. I can’t tell if the uri should contain the Item# from List 1 where the “Current Assigned User” field is in a specific content type, or maybe from the from the place that lookup field pulls from (same list, different content type). I am also trying to figure out why it keeps not recognizing my body, although I haven’t added the Column ID yet since I’m just now finding this.
Also, assuming you were able to sort this out, any help sorting out WHICH column’s ID I should be using in the body.
Any help is appreciated, but you’ve already been helpful, so thank you!
Hello,
I am attempting this procedure; however, I cannot get it to work unless I check out the file. I have even tried disabling check out and it still requires a programmatic check out in Power Automate for this to work. The programmatic check-out then increments a version. Is there a way around this?
Thank you very much,
Jon
Any chance of sharing screenshots of the entire flow where\how this is used?
FYI – This now triggers modified triggers … they broke our lovely toy!! 😩
Hello just wondering if this works for fields with a SharePoint group? Rather than a single user?
Wondering if this could be used to update properties on documents that are currently locked by users.
Probably not, but that would certainly solve a few headaches on an approval solution we have in place with some very stringent/peculiar requirements.