Programmatically updating SharePoint list properties in Power Automate without affecting the version history

Share the love

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"
  }
]


Share the love

One Reply to “Programmatically updating SharePoint list properties in Power Automate without affecting the version history”

Leave a Reply

Your email address will not be published. Required fields are marked *