Microsoft Flow: Update SharePoint columns

Blog post: 3 minute read

Are you a Power User working with SharePoint in Office 365? Do you plan on using a SharePoint list or library with Microsoft Flow? If so, read on! In this post, I’ll show how to update SharePoint values when building a Microsoft Flow for five frequently used SharePoint column data types: Choice, Date and Time, Lookup, Managed Metadata, and Yes/No.

Check out my other Power User post, Microsoft Flow: Condition syntax for SharePoint columns, where I demonstrate condition-checking for these same 5 column types.

Here are the 5 column names and their column types for my examples:

ColumnDefinitions

All examples are using the Update item action to update the value in SharePoint.

Update item action


Update ExampleChoiceColumn to new value

You get intellisense on the drop down for this column with the values entered in the column definition. You can either select one of the choices directly or select Enter custom value to point to a variable in the Flow containing the value you want. (Depending on the logic you have in the Flow)

UpdateChoiceColumn

Note: If you’ve defined the choice column to allow Fill-in choices (below), you can enter a value not included in the list of choices, otherwise you must choose one of the pre-set values.

ChoiceColumnAllowFillIn


Update ExampleDateColumn to new value

Let’s differentiate a Date Only column from a Date & Time column which is this option selected during column definition.

DateTimeFOrmats

Date Only

“Set the date to today”… Build an expression to hold today’s date and then set the column to it (image).

UpdateDateColumnToday

You can do any kind of date math in the expression as well. Examples:

  • Set the date to 1 week ahead: addDays(utcNow(),7)
  • Set the date to another date column you’ve retrieved in the Flow less 30 days: addDays(triggerBody()?[‘AnotherDateColumn’],-30)

Date & Time

All times are expressed in UTC in Microsoft Flow. In SharePoint, it will display the date to you in the time zone you have specified under Site Settings…Regional Settings.

TimeZone

“Set the date and time to current date and time”… this is the same as setting just the date. If the column you’re setting is a Date & Time format, the current time will be included.

UpdateDateColumnToday

Other examples:

  • Set the date and time to be exactly 7AM local time today. Based on the timezone of the SharePoint site, UTC-6 for me, I am hard-coding the time to be 6 hours past 7AM:
    formatdatetime(utcnow(),’yyyy-MM-dd 13:00′). This works for me since my UTC offset never changes where I live. 🙂
  • If this isn’t the case for you, you can “hard-code” a 7AM time by using the Convert time zone action. Set the Base time to current time at 7AM with this Expression:

formatdatetime(utcnow(),’yyyy-MM-dd 07:00′)
TimeZoneConversion

… then set the Source time zone to your time zone and the destination time zone to UTC. This ensures the time sent to SharePoint will always be 7AM for your timezone! Use the output of this action to set your SharePoint datetime column.

  • If you want to use the current time in an email, you need to convert it to your current timezone and then format it: convertTimeZone(utcnow(),’UTC’,’Canada Central Standard Time’,’dd/MM/yyyy HH:mm’)
    • where Canada Central Standard Time represents my preferred timezone.
    • refer to Time Zone IDs for a list of all time zones

Update ExampleLookupColumn to new value

The UI is confusing on this one. Even though the column prompt says ExampleLookupColumn Id (circled in green), you can select one of the lookup values from the dropdown. This is because behind-the-scenes, those values are stored as IDs.

UpdateLookupColumn

 

If you select Enter custom value, you must enter the Id of the lookup item you want set and not the lookup value:

Update Lookup Enter ID

Update Lookup Enter ID 2

In the second image, the Id of ‘4’ would set the ExampleLookupColumn to ‘West’ based on the lookup list used to supply values for the column:

LookupListwithIDs


Update ExampleManagedMetadataColumn to new value

In this example, the 4 terms defined for the Term set the column is referencing will appear as options in the drop down. You can either select one of them (the easiest) or select Enter custom value and provide a value of type

Microsoft.Azure.Connectors.SharePoint.SPListExpandedTaxonomy

UpdateManagedMetadataColumn

What format is that in? Label|guid of term (image below):

ManagedMetadataTaxonomyFOrmat

To derive this thru the GUI, go to the Taxonomy Term Store where the term set is defined, click the term you want to get the Identifier for and look for it on the bottom of the page (below). You then concatenate the two together with a pipe (|) character separating them.

CustomManagedMetadataValue

To set the value from another managed metadata column that has been previously retrieved in another step in your Flow, ensure you are using the column Value. This is in the required format, Label|guid.

ManagedMetadataSetValueFromOtherItem


Update ExampleYesNoColumn to new value

This one is the most straight-forward of the bunch. You can either directly select either Yes or No from the dropdown…

UpdateYesNoColumnOptions

…or you can select Enter custom value and supply a boolean value (either true or false). This may be required if you have logic in your Flow that is deriving the boolean value based on other conditions:

UpdateYesNoBooleanValue


I hope you found some of these examples practical and helpful as you build your own Microsoft Flows. If you have any other common scenarios you’d like me to include, let me know!

Thanks for reading.

-JCK


Credit: Photo by Scott Webb on Unsplash

6 comments

  1. Joan,
    We were trying to create sharepoint list items and set the value for managed metadata column in that list. But when we select the create item, managed metadata field in the list are not appearing at all in the flow to set the value. Is there any additional step required to have them available in flow?

    Thanks

    1. Hi Joan, I apologize for this tardy response. You probably already have this figured out, but there was a bug a few months back on the SharePoint Update item action whereby if you initially had the action working, added a new field to the SharePoint list of a certain data type (Managed Metadata might have been one of them), and then try to access that field, it wouldn’t be there. The resolution was just to re-add the action from scratch. Are you sure the account that is used for the data connection has access to the term set that is associated to the managed metadata column? Other than that, I’m not aware of any reason why a managed metadata column would not appear for you.

      -JCK

  2. What I would like to do is use Flow to send an email notification to someone if the ‘Status Notes’ column in a SharePoint list item is updated. Thinking of the SharePoint list item as a service order, I want to update the ‘Status Note’ field (simple text field) of that order and, when I click ‘Save’, Flow would send an email to the customer informing them that the status of their order has been updated, and include the contents of the ‘Status Notes’ field. I’m not having success finding notes or articles that tell me that this can or cannot be done.

    1. Hi Brian,
      This is definitely possible. Build a Microsoft Flow that will be triggered when a SharePoint list item is updated. You will have to have a way of determining if the ‘Status Note’ field has been updated and, if it has, send an email. (Assuming the ‘customer’ is a person column on the list, just grab the email address)
      -JCK

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.