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.
[Update October 4, 2018] Added a Hyperlink column type. This column requires you to update 2 properties: the URL and the Description.
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:
All examples are using the Update item action to update the value in SharePoint.
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)
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.
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.
Date Only
“Set the date to today”… Build an expression to hold today’s date and then set the column to it (image).
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.
“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.
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′)
… 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.
If you select Enter custom value, you must enter the Id of the lookup item you want set and not the lookup value:
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:
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
What format is that in? Label|guid of term (image below):
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.
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.
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…
…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:
Update Hyperlink column
This one is a bit tricky since you have to update 2 parts to the column: the URL as well as the description. This is what turns https://www.google.ca into Google in SharePoint.
Using the out-of-the-box Update SharePoint item action in Flow will not allow you to enter the description, so you must use the Send an http request to SharePoint action instead. In the image below, I’m updating the column named ReportURL on a SharePoint list named FavoriteReports. I’m setting both the Description and the Url to other properties from the Flow…
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