Get Some REST … Dr’s Orders

Ok, so I’m not a doctor, but this is good advice regardless.

In this case, the “rest” I’m talking about is leveraging the SharePoint REST API. It’s pretty powerful and can allow you to do things in Power Automate (or any other tool) which you may not have any prebuilt actions for, such as moving a list item into a folder or modifying the list item permissions.

Or it can make a task simpler, such as replacing the out-of-the-box Update List Item action. This will be our focus today.

Update List Item

If you’ve used the Update List Item action within Power Automate, you can immediately see some of the benefits of this action.

SharePoint Action – Update List Item

It provides a rich UI for updating your list items.

You get red asterisks for required fields, dropdown values for choice fields, leaving fields blank keeps their current values*, etc.

Nice!

*Unfortunately, I’ve found that this is not always the case, as field values are sometimes cleared when left blank. I’ve had some coworkers attest to this as well.

But things can get not so nice real quick … just try using it to update a list item with more than 5 fields!

Uh-oh.

I’ve got a lot of work ahead of me šŸ˜²

Plus, what if I only need to update 2 or 3 of those fields?

Oh boy.

This form starts to make me feel a little anxious, like I’m filling out medical forms or applying for a mortgage.

Not really, but the experience could be better. Here’s where knowing (or learning) some of the SharePoint REST API endpoints can come in handy.

And REST, 2, 3, 4 …

One of the nifty SharePoint REST API endpoints is ValidateUpdateListItem.

This endpoint allows you to update a specific list item, passing only the field(s) and their value(s) that you want to update. It hangs off the list item object itself, so the API path looks like this:

_api/web/getList('/domain/relative/url/to/list/')/items(ItemId)/ValidateUpdateListItem

Pro Tip: Most code examples tend to get the list either by using _api/web/lists/getByTitle(...)/ or _api/web/lists(guid'...')/. Your code can break if the list title is changed or the GUID is different (dev site vs. production). I prefer the _api/web/getList(...)/ method to get my list. This takes the relative URL of the list which can’t be easily changed and tends to be the same in dev and production, so your code won’t break.

So our previously complex update list item becomes:

Send an HTTP Request to SharePoint

As you can see, the explicit fields/values in the HTTP body removes the ambiguity we had before.

We know which fields are being updated and what their values will be. Win!

{
  "formValues": [
    { "FieldValue": "InternalField1Name", "FieldValue": "Value1AsString" },
    { "FieldValue": "InternalField2Name", "FieldValue": "Value2AsString" }
  ],
  "bNewDocumentUpdate": false|true,
  "checkInComment": ""
}

Notice how the headers are using application/json;odata=nometadata . This is key to the simplicity of the overall HTTP body structure (above) and the field values (below).

  • Only supply the fields and values you want to update. If you don’t include it, it won’t be updated. Simple as that.
  • To clear a field value (depending on its type below) explicitly use null, -1, or "". Now you know what the value will be.
  • For complex fields (choice, lookup, user or group, managed metadata, etc.) those values are now a lot simpler to provide when using this method.

Here’s a good reference on the field values, provided by Andrew Koltyakov, with some examples:

// Text field (single line and note)
{ "FieldName": "InternalTextFieldName", "FieldValue": "123" },

// Number field (passed as string)
{ "FieldName": "NumberField', "FieldValue": "123"  },

// Yes/No field (Yes = "1" or "True", No = "2" or "False")
{ "FieldName": "InternalYesNoFieldName", "FieldValue": "1" },
{ "FieldName": "InternalYesNoFieldName", "FieldValue": "True" },

// Person or group, single and multiple (array of 1 or more users, represented as a string)
{ "FieldName": "InternalPersonFieldName", "FieldValue": "[{'Key':'i:0#.f|membership|username@dom.ain'}]" },
{ "FieldName": "InternalPersonFieldName", "FieldValue": "[{'Key':'i:0#.f|membership|username1@dom.ain'},{'Key':'i:0#.f|membership|username2@dom.ain'}]" },

// Dates should be in in the following formats
{ "FieldName": "InternalDateTimeFieldName", "FieldValue": "01/01/2021 12:00 PM" },
{ "FieldName": "InternalDateFieldName", "FieldValue": "01/01/2021" },

// Choice field (single and multi-valued)
{ "FieldName": "InternalChoiceFieldName", "FieldValue": "Choice 1" },
{ "FieldName": "InternalMultiChoiceFieldName", "FieldValue": "Choice 1;#Choice 2" },

// Hyperlink or picture (URL, description)
{ "FieldName": "InternalHyperlinkFieldName", "FieldValue": "https://steelcutbytes.com, Steelcut Bytes" },

// Lookups fields (single and multi-valued)
{ "FieldName": "InternalLookupFieldName", "FieldValue": "2" },
{ "FieldName": "InternalMutliLookupFieldName", "FieldValue": "3;#4;#5" },

// Mamnaged metadata fields (single and multi-valued)
{ "FieldName": "InternalSingleMMDFieldName", "FieldValue": "Power Automate|220a3627-4cd3-453d-ac54-34e71483bb8a;" },
{ "FieldName": "InternalMultiMMDFieldName", "FieldValue": "Power Automate|220a3627-4cd3-453d-ac54-34e71483bb8a;Power Apps|700a1bc3-3ef6-41ba-8a10-d3054f58db4b;" }

But wait … there’s more!

This endpoint has one last trick up its sleeve, making it even more useful and powerful.

It can act like the server side SystemUpdate method.

This allows you to:

  • Create a new item version or overwrite the existing item version "bNewDocumentUpdate": false|true,
  • Overwrite the Created, Created By (author), Modified, and Modified By (editor) audit fields {"FieldName":"Editor", "FieldValue": "[{'Key':'i:0#.f|membership|not.me@dom.ain'}]"}
  • And if the item is checked-out, you can specify check-in comments. "checkInComment": "Checking this in."

Pretty slick.

Happy coding!


Follow My Blog

Get new content delivered directly to your inbox.

Author: Joey

I develop modern solutions leveraging the Microsoft cloud technologies: Power Platform and SharePoint Online, with a focus on process automation. I got my start writing code professionally beginning with Microsoft VB6, but really fell in love with code (and the art of it) with Microsoft .NET Framework version 1.0. I'm also a fan of Superman, so don't be surprised if references creep in. Up, Up, and Away!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: