Yesterday on LinkedIn I was asked about how to clear fields using Power Automate, by Matthew Bourne working at Heathrow.
Creating list items
This is not nice. But help is near. Using the HTTP request I can clear the people field by setting the StringId to an empty string and the Id to -1
Updating List Items
When I run this flow an item will be created and I can use the ID of this item to update my list item.
Managing required fields
The first pain is already visible. The Title field is a required field and I can’t save my flow. I can create a view without the Title field however that will still not help me. The required Title field still appears in the update item action
To work around this issue I’m going to set the Title to the title of the created item
Looking at the item created in SharePoint I’ve now got the described result. Even though I left fields blank the updated item will have the values set as specified in my create an item action.
Clear fields
How do we now set values to blank?
With Choice fields and many other field types I can simply set the value to null and as shown below the Choice field is cleared.
Having built up my confidence I’m just going to set all fields to null. Easy Job done! …
… or maybe not. When I ran the above flow I found that the update item action now failed.
Looking at the detail of the failing action, I got an error on the user field:
The specified user could not be found
This is not nice. But help is near. Using the HTTP request I can clear the people field by setting the StringId to an empty string and the Id to -1
So now it is up to you to either update all fields using the HTTP request action or to just use this for people fields. Both option are perfectly valid.
Note: in the above example MERGE is case sensitive.
Clear Managed Metadata fields
And then you want to clear the value of a managed Metadata field…. Well, using the null value isn’t going to work.
To clear a managed metadata field you will need to use the following expression:
concat('')Yes, that is how easy it can be. Just set it to an empty string and the managed metadata field will be cleared.
Wouldn’t it be nice if the way to clear fields was the same for all types of fields?
Other related posts
Discover more from SharePains by Microsoft MVP Pieter Veenstra
Subscribe to get the latest posts sent to your email.
[…] while back I wrote a post about clearing fields in Microsoft Flow. I covered most types of fields but didn’t cover the multi user field. For details on how to […]
I have tried this with no luck
Hi Saan,
Did you get any error messages?
Hi Peter,
What about clearing a date field using flow? My use case is for an approval scenario where a timestamp field gets set. It’s a date field, unfortunately, and the app is already in production. I’m actually trying to replace a 2010 workflow that was previously used for this.
You can clear a date field by using the null expression.
It worked after modified the list name in send http action (the list name is case sensitive, I kept first letter capital, actually it’s not).
Sorry for the late reply.
When I created the flow it tooks default ID number. Now i deleted the list and when I created the new items its ID starts with next number of previous one.It there any way we can reset start the Id again from “0”
Hi – do HTTP POST’s reactivate workflows? I see above that there is an Update Item and then a HTTP Post. I’m need a HTTP to clear a Date field and was wondering how those calls reacted with Power Automate. Thanks.
Within the rest api you can set a date field to null.
I think modifying a SharePoint item with a “Send an HTTP request to SharePoint” action would start any flow on it that runs when the item is modified…unless the trigger in the flow (“When an item is modified”) has a condition applied to it (in Settings for the trigger) so that if the item is modified by the account running the “Send an HTTP request” action (whatever connection that action is using; can select one from the 3-dot menu on the action — or by default it will be the account that created the flow), it doesn’t trigger the flow. Something like @not(equals(triggerBody()?[‘Editor’]?[‘Email’],’[email protected]’))
If you want the flows not to trigger then you would need to do a system update.
I’m not having any luck clearing choice fields with the null expression. It’s working fine for date fields and I’ve got the HTTP action working for clearing person fields. I guess I will try adding the JSON for choice fields in the HTTP action. Would it just be -1 for ID and ” for value?
This worked for me:
This worked for me.
That is, I was able to set the choice fields to blank in the JSON using the internal column name and ”
Will Upload Item will be under Create Item on the powerautomate flow?
Thank you
It all depends on what your flow needs to do.
What I’m trying to is, that the person’s name it’s not auto-populating on the sharepoint list. Do you have any tips on that?
Do you mean like the modified field?
Correct!
You wouldn’t want to do that. That would break the whole audit within SharePoint. Can you give some more details on the why?
How do I conditionally clear the people column field using http action? If there is a value in variable i would like to update it with value otherwise clear it. Can I use if condition within “Send http request” action? how?
You would have to put the conditional logic in your flow. Not in your http request. You could first collect all items that you want to update and then do the update on those items though.
I have only 1 item with 4 different person columns. All those are coming from variables. If variable has value, i got the column to update to variable’s value otherwise if the variable is blank the same needs to be updated to person columns. Do I need to write 4 different http request to sharepoint actions?
Hi,
Updating an item via Update Item will trigger any associated flows, but the Send HTTP Request will not! No need to use a functional account to work around this! I’ve looked into this…
Hi Sara,
I’ve just done a test and I’m not seeing the same behaviour. For me it doesn’t matter if I update using the Update Item or if I use the REST API through a send an HTTP request action. Please feel free to open a chat and we can have a look at the problem.
I tried this for a Person field and I get the following error:
The parameter __metadata does not exist in method GetById.
clientRequestId: 46583c0c-f14d-4f32-8cfb-9449a2a6facf
serviceRequestId: 617114a0-10d0-c000-a4f1-54a917647ce6
I wasn’t even using GetById(), although it would be preferred, I am using GetByTitle().
Hi Steven,
Can you send me the uri and the body of the Send an HTTP request to SharePoint action. Also what did you set the Content-Type header to?
Please feel free to open chat on the site.
URI: _api/web/lists/GetByTitle(‘IT Work From Home’)/items(‘@{triggerOutputs()?[‘body/ID’]}’)
Body:
{
‘_metadata’: {‘type’: ‘SP.Data.IT_Work_From_HomeListItem’ },
‘ApprovedByStringId’: ”,
‘ApprovedById’: -1
}
Content-Type: application/json;odata=verbose
Wrong Body:
{
‘_metadata’: {‘type’: ‘SP.Data.IT_x0020_Work_x0020_From_x0020_HomeListItem’ },
‘ApprovedByStringId’: ”,
‘ApprovedById’: -1
}
Sorry, This is the body IO am using: {
‘__metadata’: {‘type’: ‘SP.Data.IT_x0020_Work_x0020_From_x0020_HomeListItem’ },
‘ApprovedByStringId’: ”,
‘ApprovedById’: -1
}
Hello Pieter,
I`m quite new on Power Automate, I have a SP List with an “Assigned to” person field, that requestors might populate.
I want this flow to always clear that field before triggering an approval flow that I have already set; once approved, a team member will Assign the item to him/herself and work on the request.
While testing your flow, I´m getting the following error:
The expression “web/lists/GetByID(´MR21 Tracker´)/items(2)” is not valid.
clientRequestId: 0b8291ed-7380-4c40-a7d8-1675706f06ca
serviceRequestId: dcd96fa0-d099-d000-8f70-187ecd676d88
Uri: /_api/web/lists/GetByID(´MR21 Tracker´)/items(2)
Body: {´_metadata´:{´type´:´SP.Data.MR21 TrackerListItem´},´AssignedtostringId´:´´,´AssignedtoId´:-1}
What I´m doing wrong ?
Andres
I would expect that the following will work better
/_api/web/lists/GetByTitle(´MR21 Tracker´)/items(2)
Also what is the method set to?
Then also the type specified in the body looks wrong. If you visit the following url in your browser, you will find the content type to use.
https://mycompany.sharepoiint.com/_api/web/lists/GetByTitle(´MR21 Tracker´)
Hi Pieter. I’m trying to use the http request to clear some people fields (multiple person fields) and I’m getting the following error: “An unexpected ‘PrimitiveValue’ node was found when reading from the JSON reader. A ‘StartObject’ node was expected.” Have you ever come across that? My payload looks like this:
{
“__metadata”: {
“type”:”SP.Data.CV_x0020_CountryContactsListItem”
}, “CountryBackstopId”:451,
“CountryFinanceBackstopId”:{“results”:[294,455]},
“CountryFinanceLeadId”:{“results”:[464,451]},
“CountryDirectorId”:458
}
Turns out I needed to use the {“results”:…} object for the other two fields as well. Even though they only had one person in them, they were set up as multi select. Also, for setting multi select person fields to blank, it seems you have to use {“results”: [0]} instead of -1