Forum Discussion
SharePoint calculated column not updating automatically
Dear All
I need some help to understand the calculated column feature on SharePoint as the formulas are not calculating the values automatically and/or they are incapable of reading information in the reference columns.
May I clarify on the following:
i) Formula Syntax - Do all column names have to be expressed with square brackets in the formula dialogue box?
In the formula dialogue box, reference column names will be expressed in square brackets when first entered, but if I were to return back to the page again, the reference column names will not have the square brackets. I'm unsure if this caused the formula to stop working.
ii) Formulas are not picking out the data in the reference columns as expected
For example, I made a calculated column to pick out the folder/file names in the document library, where formula for calculated column = [Title]. Surprisingly, SharePoint is unable to return the exact text string in "Title" and it will only respond when I make a change to the folder/file names. Calculated columns also failed to respond when I add in a new folder/file name.
iii) Is it possible for SharePoint to perform the calculations automatically?
For instance, how do we ensure that the calculated columns are updated whenever the SharePoint page is loaded? (just like in excel, where formulas can be configured to calculate automatically?)
3 Replies
YSTCTM2321 "In the formula dialogue box, reference column names will be expressed in square brackets when first entered, but if I were to return back to the page again, the reference column names will not have the square brackets". That is normal behaviour for SharePoint and doesn't affect how the formula works.
The value in calculated column is actually static and does not update dynamically. It's only updated when the item is edited. For dynamic updates you need to use JSON column formatting.
Rob
Los Gallardos
Microsoft Power Automate Community Super User.
Principal Consultant, SharePoint and Power Platform WSP (and classic 1967 Morris Traveller driver)Thanks for the reply.
I'm completely new to JSON. May I know how JSON can help with dynamic updates?
I thought the scripting will only affect the formatting (ie visual output) of the column but it may not have an impact on the behavior of the formulas in the calculated column?Sorry for asking these because I was trying to construct a "helper" column in my SharePoint library that is capable to update dynamically in order to help me to sort my folders (named in the format of MMMYYYY) in chronological order, so I intended to use "IF" conditions to assign a month index to the folders that will help me to sort the folders in my desired order (instead of the default ascending/ descending order).
Please feel free to point out my misconceptions and propose any alternative solutions/ examples on JSON scripting that can achieve a similar result of the "IF" function.
This page explains how to do it.
https://learn.microsoft.com/en-us/sharepoint/dev/declarative-customization/column-formatting
