Microsoft Flow: Update SharePoint columns

Reading Time: 5 minutes

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:

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


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…

UpdateHyperlink


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

31 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

  3. Awesome Post, Joanne. Do you have an example for setting a Hyperlink column’s value to a URL with a display name? The old method of separating the link and the display name with a comma space doesn’t seem to be working.

    1. Hi Todd,
      Thanks! I don’t, however that would be a good add to this post. If I figure it out, I’ll add it.
      -JCK

  4. Hi Joanne,
    The title field in your example is required, as I would expect. When I try to do a similar Flow, the Title field is not available in the Dynamic Content selections. I’m doing an update item Flow.

    1. Hi Steve,
      The title field should definitely be there. Perhaps click the ‘show more’ button when viewing dynamic content in the Flow??

      -JCK

  5. Hi Joanne,

    I’m trying to update a lookup column via flow, but not seeing it available when I do a create or update item action. Its just not there. Do you know by any chance if this functionality has been disabled? Is there a preview connector of some sort?

    1. Hi Josh, by any chance are you not clicking the ‘see more’ link when the column list shows up? That will show the complete list of columns, by default they don’t all show.

      Thought I’d ask.
      Joanne

  6. I’m confused. I guess our definitions of updating something is different. In no world I know of will anyone want to create a new file and have a flow ALWAYS update the metadata as you suggest.

    When I create a file, I want a dialog box to pop up and prompt me to choose the values of the various columns. Anything short of that capability is in my opinion bait and switch. In fact this is the fourth comment/email I’ve made this afternoon to Microsoft.

    Everyone is making such a big deal with Flow and all I can see are pretty boxes that allow a person to have an email sent out when something is done. Ewwwwww, so cooool. An email notification. Yes that for sure solves 99% of people’s business needs, lets not talk about REAL LIFE scenarios.

    I’ve even told Microsoft to hire me to sort these problems out for people since they are apparently totally inept in this area. That’s what happens with program creep. Spread yourself so thin that nothing gets done to completion!! Can you hear my exasperation with Microsoft?

    1. Hi CW,

      I certainly wasn’t suggesting you would always update metadata this way. The post shows examples of how to update several data types if they were part of a more complex Flow to automate business processes.

      Some examples:
      1. When a contract end date is reached, use Flow to update the contract status to ‘Complete’
      2. When a budget is approved, use Flow to store who approved it, when they approved it, their division, etc.
      3. When a site is provisioned, use Flow to update a SharePoint list with details of the site (could include metadata spanning many of these data types)

      I have done these types of process automation use-cases in many Flows in tenants I work across.

      I sense your frustration, however I don’t work for Microsoft so I can’t speak directly to your concerns with their products.

      Hope this was helpful.
      -JCK

      1. Hey. Thanks for your insightful response. Yes frustration is correct. Sorry, I sounded like I was nit-picking. No I guess your example was so concise for your example, I referenced that as my view on how Microsoft might ought to share ‘usable’ examples to users.

        I think Flow has great potential, but currently it needs to really stop the program creep, slow down in some areas, speed up in others. Make certain functionalities complete before fiddling around with other stupid issues that a small percentage of users would want. Saving photos, to SharePoint, parent-child this, dropdown field referencing there. Everything is ‘almost there’. Complete them. You shouldn’t need to have a PhD. in expression design just to be able to move your data around, manipulate it the way you want, save it wherever. If it’s a Tuesday with a full moon then this function may work as expected (prayed). It never ends. They’re not listening, they’re spreading things too thin trying to be too much to too many.

        LOL. Anyways, Cheers Joanne 🙂 CW

  7. I have a flow running on a SharePoint Library that modifies a data field whenever someone updates a document. It is working as designed except it always puts me as the modified by each time it runs. How can it run using the credential of the person that triggered it?

  8. Hi Joanne, I’m trying to update metadata on all items in a file with nested folders and so far I’ve hit a brick wall. I can only get it to update the top level. Any suggestions?

    1. Hi Daniel, not enough detail in the question… what technique are you using to iterate thru the nested folders? I’ve never tried to update in nested folders before but it should work

  9. Hi Joanne,

    I have threaded comments via versions and the append text to existing feature, but in views this only displays as a link to “View Entries”. I need to be able to view the latest comment. I tried creating a mirror column and use a flow to populate that column, but even though my flow runs successfully, the mirror column is always blank. I’m assuming this has something to do with the versioning, but I can’t figure it out. Is there a way to accomplish this with Flows? Thanks.

    1. Hi Jen, I’ve never tried this before and I don’t have the cycles to test this right now. Once I do, I’ll let you know.
      Joanne

  10. Hi Joanne,

    I am preparing a sharepoint flow that keeping tracking of asset renewing process and date, is that possible whether flow can help to perform the following:
    1) When status change to completed (Completed the renew process)
    2) Change the status to NIL (Which ready for next cycle of the renewing process, which might be 1, 2 or 5 years later)
    3) Update the expired date to the new date (based on one column that I have put for number of validity years), eg. if the expired date is 01/01/2019 and the validity years are 2 years, then the new expired date will be 01/01/2021.

    By doing this, all my renewing of the asset date will be in under monitoring rather than the manual process. Just wonder can MS flow and SharePoint are able to do so or not? Please help as I did some research for sometimes but just couldn’t find any similar solution where I can referring to. Thanks

    1. Hi Alvin,
      Yes… I think a SharePoint list and Microsoft Flow would definitely be able to provide a solution to your business problem. The only “gotcha” I see with what you describe is changing the status to NIL… although you can certainly do it, perhaps you might want to change the status to an actual value… like “Pending” or “Waiting for renewal”, etc. It’s just easier to set that in a Flow.
      Trigger your Flow on the Item added/changed event and check the status. When it’s completed, do your stuff you need done (update date, status, and any other metadata)

      That’s at a high-level, but should be enough to get you going.
      -Joanne

  11. Hi Joanna…nice work…can you talk about how to read the metadata values…plural…from one document and use those to assign to a new document…case is insurance carrier docs with specific managed metadata that are in one library and the received docs from the customer in another library…I would like to just ask the user to input the Item ID from the carrier doc and apply the managed metadata to the corresponding customer document…instead of hoping they assign them properly.

    1. HI Brandt, you would add a step to read the item from the insurance carrier docs using the unique ID as provided by the end-user and then update the received docs’ metadata with the values from the insurance carrier docs. Does that help?
      -JCK

  12. HI hi,
    I am running into an issue with one of my sharepoint-adobe sign flows, “when the state of an agreement changes”. I have scope as “any agreement in my group”, but I am getting an error on action “get the status of an agreement when someone else in my group initiates the form. If I initiate the form it works correctly. Is this some kind of permission issue or might something be wrong with my flow

Leave a Reply to toddbleeker Cancel reply

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