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
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
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
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.
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
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.
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
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.
Hi Steve,
The title field should definitely be there. Perhaps click the ‘show more’ button when viewing dynamic content in the Flow??
-JCK
I eventually found it, it was just Flow being Flow. Thanks!
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?
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
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?
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
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
Something is missing.
How can we do this with a sharepoint library instead of sharepoint list?
Hi Laura, not sure what you mean. You can do these same updates on library metadata.
Dear Joanne
I have a flow which creates a new SharePoint folder in a Library. It is triggered by a item being added to a Sharepont list. That works great. Conceptually what I’m trying to have is a list or items with lots of columns, and folders associated to those columns.
But in the step to create the folder: “Create new folder”, I do not have an ability to set any library column metadata . I need this, as one of these is a lookup pulls in some additional column values from my list. Very smart!
I have the value for the column inside my flow, but I cannot find a step to use it to update the metadata on the library column. “Update Item” only lets me select lists. The only other thing I can see is about files. Any ideas?
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?
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?
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
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.
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
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
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
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.
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
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
Hi Nick, I don’t have any experience with Adobe sign flows.
Hi Joanne
I have a SharePoint list which I want to update (create items) using excel for business. One of the columns on the list is a lookup to a parent list (using ‘add from existing site columns’). Is there any way to update that column too? it doesn’t appear in the flow ‘Create Item’ list of fields.
Bit of a novice here, so any help would be appreciated…
Laurence
Hi Laurence,
I’ve never used ‘Excel for Business’ so I can’t help you on this one.
-JCK
I am trying to update the Audience column which appears after enabling audience target in a pages library. Can you please suggest the body schema for it.
Hi Parul, Sorry, I cannot.
Hi Joanne,
I am trying to get an approver in a workflow to modify a value in a sharepoint list item to any value eg a cost(integer) column to add the cost of an item.
Power automate does not seem to have that feature, do you have any ideas please , I’m stuck 🙁
Hi Joanne,
I have one of requirement that when we are moving the Ms Flow solution from source to Target Instance. I need to change one of the Ms Flow SharePoint Address value need to change through PowerShell. If you can help me here It’s really great for me. Thanks!
Hi Joanne,
During the “update item” in flow (SharePoint list), I have a “choice” column and display choice using checkboxes (allow multiple selections) example: shopping area: south, east, north, west. when i selected all 4. what is the dynamic field do I need to select from the drop down?: there are three possible: 1.shopping area item, 2.shopping area value and 3.shopping area.
PS. I used “Apply for each” and select “Shopping area” as previous field before add “Update Item”
thank you for your help.
vincent.
Any idea how you can add a managed meta value to a column? Existing values always seem to be overwritten.
Hi Anne-Mie, can you please elaborate more? Do you mean adding an additional value to a multi-value managed metadata column on an item?
If so, I’ve never tried but assume it’s possible.
-Joanne
In our site pages library we added a managed meta data field with multiple values.
We now want to replace some of these values with other values on all our documents.
But other existing values should stay there.
I can assign a new value but the existing values are gone.
Didn’t found any information on internet how to do this.
I think if you run a flow using get values and pull the existing values of the pre-updated version of the record from Start Windows Token then add the new choice in the updated version and then stick back in the previous version’s choices.
Ya Microsoft sure doesn’t make these seemingly simple tasks simple short of acquiring a degree in computer science
Thanks Carl! I was going to suggest to Anne-Mie to ask the Q in the Power Platform/Automate Microsoft tech community… I’m sure this has been done before. I don’t have the current cycles to dig into it unfortunately.
-Joanne
Carl, Joanne,
I tried with the following steps
– retrieve the properties of the document
– define a variable and fill it with the array from the existing properties
– add the new value to the array
– update the properties with this new array.
But this doesn’t seem to work. I can update metadataproperties with one value but not with multiple values.
It seems like something that should be easy but cannot find a solution.
Really. Hmm. I’ve never tried multiple values but am in now way surprised. Like I said, we’re talking Microsoft here. If there were a comparable product on the market, I’d be there faster than you could snap your fingers.
I find EVERY TIME, I mean EVERY SINGLE TIME you try to perform some seemingly straight forward and expected functionality you invariably run into a wall, or some form of gotcha. There’s too many issues I’ve encountered where these expected behaviors are…………….over looked and missed. But they always suggest adding the requirement to UserVoice. So buying a car and expecting 4 wheels is too much to ask. Please add to UserVoice so we can deep six the request and ignore the users.
You may want to try posting your question in the Power Automate Community under the ‘Building Flows’ forum for broader reach. Link: https://powerusers.microsoft.com/t5/Building-Flows/bd-p/BuildingFlows
Good luck! I hope the collective resources on that site will be able to help you out. They work with Power Automate a lot more than me. 🙂
-Joanne
Thanks,
I posted my question. Fingers crossed.
Hi Joanne. Yes I have posted several questions there and rarely receive an answer. Those forums don’t seem as collaborative as they used to. Seems all the ‘know-it-alls’ surf and don’t bother answering questions below they’re standard.
I understand but, better and more samples/examples by Microsoft would really help. Some things I’ve learned I have no idea where people learn these HTTP Requests to do certain things. I assume you learn them from using the force.
Cheers
Hi Carl, it’s unfortunate that forum has turned into that. I asked around and next to that there are some FB groups as well, although since my focus is primarily on compliance these days, I don’t know this first hand nor how effective they are.
Completely agree that more samples/examples would be helpful across many areas of the Microsoft stack.
Cheers,
Joanne
Thanks Joanne.
Yes, I really do try to love the M365 components. It’s the consistent oversights that creates the ambivalence but it is fun to use.