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

2 comments

Leave a Reply