This is somewhat, what many will describe as a designflaw in SharePoint. It’s supposed to be “By Design”, but still, it’s just something that many people get very annoyed about.
In SharePoint, if you create a Calculated column, which has a calculation that is based on something dynamic, like “=Today()”, you would expect that column to just display todays date, every day you visit your list. But unfortunately, that is not so. To update the column with the calculation, you need to visit the item and the save it again. But if you update the column in the list settings, SharePoint will the re-calculate the list for you. So the trick is to get Microsoft Flow to update the column every night to keep the calculation correct.
To create the MS Flow that will update the column, you need the “Send an HTTP request to SharePoint” action. In this example I have a calculated column on a tasklist. The Column is called “CalcToday”.
Uri: /_api/web/lists/getbytitle(‘Tasks’)/fields/getbytitle(‘CalcToday’)
Headers
| Label | Value |
| content-type | application/json;odata=verbose |
| X-HTTP-Method | MERGE |
| Accept | application/json;odata=verbose |
Body: {‘__metadata’:{‘type’:’SP.FieldCalculated’},’Formula’:’[your formula]‘}
Thanks this is useful. Do you know if it is possible to make a similar HTTP request from Flow to update a Calculated Site Column’s formula?
Hi munchkin. that is what you do. Just update the code in the “body” input form.
It took me a bit to troubleshoot my flow failures. Eventually I found that all single quotes needed to be re-typed, as they don’t copy & paste correctly for some reason, as noted in the below post.
https://powerusers.microsoft.com/t5/Building-Flows/Send-HTTP-Request-to-Sharepoint-fails-with-Status-400-Bad/m-p/243172#M24024
However, the flow is running successfully now, but my calculated fields are still not updating. Any idea as to why not?
What the flow does is basicly, just setting the calculated columns calculation. When that is done, SharePoint internals take care of the rest. My guess is that the flow is running and there are no errors. But still you are not getting your dates updated. What if you try to update the column with something else? Like plan tekst. Just to see if anything actually gets written to the column.
Quotes and browsers don’t match. I always test my copy/pasting through the notepad to see if it looks funky.
Could you use the same approach in a single flow to update calculated columns in multiple lists?
I don’t see why not. If you have lots of lists, I would make a list with the URL’s of all those lists and the in a loop that go through that list, repeat the update for each item.
Would this change the “Modified By” field to the name of the flow owner ?
No. It would not change it.
Hi Ulrich,
I keep getting this error:
An entry without a type name was found, but no expected type was specified. To allow entries without type information, the expected type must also be specified when the model is specified.
Can you point me in the right direction?
– Cheers
Hi Oshi
I would look to see if there are any of your rows that are miscalculated. When to flow is triggered it essentially does the recalculation for all the rows again. But to me it looks like the error is trying to tell you that one row is missing some data to do the calculation.
To test your function, try and create a new list with the exact same columns and calculated column and then see if you can get it to work on that one. That will tell you that it’s not the flow but the list that has an error.
Thank you this worked perfectly. Question. If I need to update two columns, is there a way to do it with one HTTP request or do I need to call it twice?
Hi M.A
For that, you will need to make another http request. One requst for each field.
This is almost a great solution, only limited by the calculated column values not re-calculating after being updated to the same column formula. I’m able to test this by saving my calculated column formula as Now() which gives me a new column value, but once set it does not recalculate the column values when re-saving the formula until toggled to some other value and back to Now(). Same results are seen with Flows using the provided technique in this article or manual updates on SharePoint. Thanks for sharing your solution.
Does this work for a SharePoint Library too? The properties of a library are just like lists, so I imagine it would. Do I need to update /_api/web/lists/getbytitle(‘Tasks’)/fields/getbytitle(‘CalcToday’) to /_api/web/library/getbytitle(‘Tasks’)/fields/getbytitle(‘CalcToday’), or similar?
Also, if your library title or column titles are two or more words, do you just use a %20 to represent the space?
Thanks!
It should! You don’t need to change the /web/lists section to /web/library either — SharePoint’s API treats library properties as list information, so the request URL is the same for both. You can use either a space or a %20 in the request since spaces will be translated to %20 automatically when the request is sent. Hope that helps.
I think I know why some people’s work and some don’t. One thing that SharePoint does is it makes slight housekeeping to formulas. It capitalises NOW() and TODAY(). If I go into my sharepoint column and change NOW() to Now(). The formulae is seen as altered and the calculated field updates its calculation and the beauty is Now() also reverts back to NOW(). I believe if you used NOW() or TODAY() in the JSON properties of Formula in the flow it wouldn’t work. By making a subtle change like Now() or Today() (where its not all capitalised) it sees it as a modification and triggers the calculation to update and then it must do some housecleaning and recapitalise it. Also adding something as simple as a space in between the brackets of NOW() also has the same affect e.g. NOW( )
At least this is what I have found in my quick test on this. Hope that helps someone.
Hi Danny
Nicely spotted. I’ve made it a habbit of always using Now() and Today().
Thanks Danny Mosquito, that subtle change to use “Today()” instead of “TODAY()” triggered my calculated field formula to run!
Thanks for this post. However I am getting an error that the URL was not found.
/_api/web/lists/getbytitle(‘Tasks’)/fields/getbytitle(‘CalcToday’)
Is ‘Tasks’ in the formula above the name of the list?
If so, my List Information in the list settings shows my Name is ‘Invoice Compliance Intake Form’ and the web address is https://……./Lists/Invoice Compliance Tracker/AllItems.aspx
What should I input?
Hi Joe
Yes, Tasks is the name of the list.
HI, I cannot get the formulas to work. This is what I have typed in {‘__metadata’:{‘type’:’SP.FieldCalculated’},’Formula’:’=if(or(‘Returning Date’)<Today(),('Returning Date')=" "),'In','Out')‘} I get the property name " is not valid. The name of a property cannot be empty
Hi Kris
You are copy/pasting it wrong. Try running the code by a notepad. There is a difference between
'and´..{‘__metadata’should be{'__metadata'etc.Then it should work.
Hi, thank-you for the response. I re-wrote the formula in notepad and am still receiving an error…..Invalid JSON. A comma character ‘,’ was expected in scope ‘Object’. Every two elements in an array and properties of an object must be separated by commas.
{‘_metadata’:{‘type’:’SP.FieldCalculated’},’Formula’:’=’if(or((‘Returning Date’)<today(),(‘Returning Date’)=””),’Out’,’In’)’}
Remember the 2 x _ on metadata. It should be __metadata and not _metadata. I also think that your use of escaping ” and ‘ plays a part in your error. Tryr and start by keeping it simple and have the Formula to be just “=Today()”… When that works, build out.