How Are a SharePoint List’s Calculated Columns Stored and Rendered?
Ever helpful @MSwannMSFT – Matt Swann at Microsoft (why aren’t there more like him?) – spotted a Twitter discussion between @jvossers, @willhlaw, and me (@sympmarc) yesterday, and in his usual tenacious style, promised an answer. This guy doesn’t just guess, he goes and tracks down the line of code or the actual developer and finds the real answer. (Matt: Sorry if I’m letting the cat out of the bag, here.)
The original question came from @jvossers:
jvossers: At what point does #sharepoint perform the calculation for calculated fields? Does it actually store the calculated value in the Content DB?
Damn good question, IMHO. I’ve never seen a clear answer, and we’ve all seen the blog posts (mine included) about tricking SharePoint into using the [Today] and [Me] columns in calculated columns and the like. Our posts all put forth some best guesses about what happens with calculated columns and when, but I, at least, never knew if I was really right.
Enter Matt. He saw the question, and promised an answer, as he is want to do:
MSwannMSFT: @jvossers pretty sure it’s calculated at render time, we only store the field XML in the db…
I respectfully disagreed:
sympmarc: @MSwannMSFT @jvossers pretty sure it’s calculated at render time @willhlaw @sympmarc -> Not sure I agree. Talking 2007?
MSwannMSFT: @sympmarc yeah, calc fields comment was re: 2007. I’ll try to take a look tomorrow, it’s entirely possible that my mental model is wrong!
sympmarc: @MSwannMSFT We could both be wrong. I’ve heard much misinformation on this, like lots of other stuff SharePoint. Trust but verify, I say.
And Matt went off and did his thing. Today he had the answer:
MSwannMSFT: @sympmarc Looks like we store the formula in the field schema using the <Formula> element… eg <Field>…<Formula>…</Formula></Field>
MSwannMSFT: @sympmarc some of the confusion might be from trying to do things like http://tinyurl.com/askdpa …that’s definitely not going dynamic!
MSwannMSFT: @sympmarc net-net, we don’t store anything other than the formula in the field schema, and that’s the authoritative place for field info
sympmarc: @MSwannMSFT Yes, the Today thing is a common bugaboo. Given that the formula is stored, what event(s) trigger an evaluate? Not a render?
MSwannMSFT: @sympmarc any render will cause the formula in the field XML to be evaluated… so, list view web part, object model, SOAP
QED. Matt found the answer, based on fact. I like facts. Note to Microsoft: Give Matt a raise and hire more like him.
UPDATE 2009-12-07/08: If you read through the comments below, you’ll see that I rethought my easy acceptance of Matt’s conclusions. I wasn’t doubting Matt, but just thinking through how this all holds together. Today Matt tweeted us some more info, which is below. I’d rather have info that’s corrected later than no info at all. Matt once again proves his thoroughness!
MSwannMSFT
2:57pm, Dec 07 from Web @willhlaw @sympmarc @jvossers time for me to eat crow — I looked at the content db and calc fields *are* updated at save time.MSwannMSFT
2:58pm, Dec 07 from Web @willhlaw @sympmarc @jvossers that’ll teach me to answer anything before actually looking at the db…MSwannMSFT
2:58pm, Dec 07 from Web Turns out I was wrong — we update calculated fields at save time, not render time. My fault for not looking in the db to begin with!sympmarc
3:19pm, Dec 07 from HootSuite @MSwannMSFT: @willhlaw @sympmarc @jvossers that’ll teach me to answer anything before actually looking at the db… -> No harm, no foul.willhlaw
3:21pm, Dec 07 from twidroid RT @MSwannMSFT @willhlaw @sympmarc @jvossers that’ll teach me to answer anything before actually looking at the db. > thx 4 following up!sympmarc
3:21pm, Dec 07 from HootSuite @MSwannMSFT So, “calc columns are only updated at save time”. Is this a correct statement? I’ll update my post: http://ow.ly/JCP5sympmarc
3:21pm, Dec 07 from HootSuite @MSwannMSFT Kind words at http://ow.ly/JCPz still apply. We really appreciate your willingness to find answers.MSwannMSFT
2:48pm, Dec 08 from Web@sympmarc correct: each item’s entry in AllUserData table contains the calculated value, not the formulaMSwannMSFT
2:49pm, Dec 08 from Web@sympmarc and (based on my testing) we update the value when you save an item OR when you change the calculation in the field setting
So the strange thing is that if you add a formula that includes [ID] it will work for rendering any existing item but if you add or edit an item in the list, the formula is changed to just ID and breaks the field value substitution (only for the ID field). The Create/Edit list item function for some reason changes the formula…
Ian:
Can you give an example of this? Also, I’m going to update this post, as Matt found out that the values are actually recalculated at save time, as I suspected.
M.
Ian, this makes sense for the ID field because at the time of save for a new item, the ID is not known. The transaction needs to take place first at the database level before an ID is assigned.
-Will
So is it some internal SharePoint code that performs the calculation and not the implementation of the Calculated field (control) itself? I have a feeling it must be, as I understand that when rendering a list view web part, it does not instantiate an instance of the field nor the field control class for any of the fields in that view.
Which class is responsible for performing the calculation?
Is the Calculated field of a special kind of breed, in a sense that SharePoint “knows about it” and gets a special treatment? I thought that all fields, including the out of the box ones, were implementations of the extensible field model, and that the fields know SharePoint, but SharePoint doesn’t know the fields.
Anyway, thanks for writing this blog post Marc, and thanks you Matt for your help. Agree with Marc’s last point in his post!
The more I think about this one, the more I want to dig into it further. When I use GetList (yes, I’m now a Web Services junkie) to look at a list definition where there are calculated columns, I can see the the formula is indeed stored at the field level. What I’m wondering (and I tweeted this back over at Matt) is what is stored in each item. It wouldn’t make sense to me to store the formula redundantly per item; I would think that the evaluated value would be stored in the items. If *that’s* the case, then when would the evaluated value change? That’s why I originally said I didn’t buy that the formula was evaluated at render time. I’m going back to my belief that it may only be on a save until proven wrong.
M.
Marc,
I concur with your comments that the formula is stored at the field level. In fact, looking at the schema XML returned from the GetListContentType function of the lists.asmx web service, the calculated column I called “calc” and formula = “=CONCATENATE(Title, ‘Joooo’)” looks like this (since WordPress strips out xml notation, I’ve replaced the brackets with -> pretty, I know):
=CONCATENATE(Title,” Joooo”)
Here is an an observation I had a while ago. A change to a calculated column to a very large list took a very long time. So long in fact, I could not do it anymore on the client side. I had to log in to the server to use a local browser in order to perform the calculation change. Microsoft’s design decision makes sense because a calculated column is not changed that often and pre-calculating the item saves time the next time a list view is rendered.
Thus, here is my conclusion:
Microsoft evaluates an item on save AND evaluates ALL items in the database whenever the calculated column is changed.
-Will
I’m not quite clear from all of this what is really calculated when then, consider this scenario:
We have a calculation useing YearPart, MonthPart,SetID(adds padding zeros),ID to get a calculated case number, e.g., [formula =YearPart&SetMonth&MonthPart&SetID&ID] rendering
2010340001 (for ID=1), Once saved however ANY edit of the row (even, edit/cancel) drops the ID from the recalc (the other columns are also calc columns). If you “edit” the CaseNumber calc column by opening it and closing it the CaseNumber is properly recreated but you can’t expect users to do this :) .
Russell:
The behavior you’re describing doesn’t sound right to me. (But if you’re seeing it, then it’s happening, of course!) The calculation should *only* occur on a commit of a change to the item. If you open the EditForm and then cancel, you haven’t made a change, so the calculation ought to stay the same.
M.
In response to Permalink April 7 – ***If you “edit” the CaseNumber calc column by opening it and closing it the CaseNumber is properly recreated but you can’t expect users to do this :) .
I am having the same issue (I have used calcualted fields in a document library and I have to go in and pretend like I am editing the calculation in order for it to refresh the calcualtion on the entire list (because it will only change on a single list item when it changes).
Please provide a non-code solution to having to do this (something like a workflow that performs the updated calcualtion on the entite list so I do not have to do so manually).
Thanks, Ron
Ron:
You’ll need to figure this one out yourself. When you save an item, the calculated column recalculates for that item.
M.
I am confused about this. Your argumentation is purely observational. That will lead to speculation.
To add some complication to this, I want to describe another issue that shows that neither of you seems to really yet know the exact point of triggering / executing the calculation.
When you create a list item in MOSS 2007 (SPD) workflows, you can find, that even after the “create” is executed, the calculated field is not yet evaluated.
sample
Take a list “Race Condition” with two columns Title and a Caluclated Column “Calculated” with the formula “=[Title]”
Create a workflow (in another list) that creates a line by just setting the title and display (e.g. log) the value in the “Calculated” attribute. You will find it is empty.
An article refering to this phenomenon can be found here (with a non working recipe for a work-around): http://sharepointbriefing.com/features/article.php/3866646/Use-Calculated-Columns-to-Close-Gaps-in-Workflows.htm
Conclusion:
– Not a candidate: “save time” – NO (not if saved from a workflow)
– Candidate: “rendering time” – when viewing the list (browser) it always shows the correct value
– No a candidate: “after copy” – neither the list copy nor the create item workflow activity can trigger the evaluation if immediately followed by the creation in a workflow
Is it just a timed race condition and one must wait (hard in a workflow if not known how long…
Can anyone shed some light on this.
Is it something that is patched with a recent update?
Thanks for your thoughts in advance,
Olaf
Olaf:
Yes, this post is pretty much observational. My hope was to gather information and perhaps have someone provide “the answer’. I think we all settled on the fact that the calculated values are stored on a save and not recalculated. I’m not sure I fully understand your example above, but my guess is that once the item your workflow creates is fully saved, the calculated value wil be “there”.
M.
Dear Marc, thanks for your fast response.
I might have not been clear enough.
But seen from an SPD workflow the “save” event is definetly *not* the time of calculation.
The field is calculated at a later time. It seems like the stated case above when a formula is changed and the update of all the calculated fields runs for some time.
A read of a list item with a calculated field right after creating it shows an empty calculated field!!!
We are currently trying to update to the latest patch level and come back on this.
We are using a calculated field as the powerful Excel-Like formulas extend missing features in the SPD workflows but we fail to read the result of the calculations as they are not calculated at save.
I keep you posted about the impact of udpating to the latest patches. (we have currently tested on pre-SP1 and SP1 platform MOSS 2007 and will update WSS and MOSS to SP2 + latest CU).
Interesting. The save is usually asynchronous, so I wonder if its a multi-pass asynchronous save (if that’s a real term). I’ll be interested to hear what you find out.
Ok, here I am back with some news.
1. SP2 didn’t really change much in the way calculated fields were written from the workflow
2. Results from debugging with SQL Profier showed the following:
It seems correct that the calculated fields’ values are calculated before the first insert (then by the way there is not yet an [Id] value.
Not debugged: When updating the formula, all fields are updated probalby asynchroneously…
Regarding the usage of calculated fields, we have resorted (relying on only SPD Workflows and SharePoint List Customizations) to create “onchage, oncreate” workflows that fills in fields instead of using calculated columns with IDs as a workaround.
More detail:
What was debugged:
Manual creation of an list entry (web interface) with a calculated column (“[Id];#[Title]).
Seen in the queries triggered by that interaction:
[Id] is left null and the calculated field is handed (evaluated as “;#[Title]” (here the title is filled in, but the Id is empty) as a parameter to the Stored Procedure creating the entry. Voilá: As stated in a comment above: on save!
I hope the helps a llittle bit, but am afraid that when creating a list entry by a workflow the process might be different …
It sounds like the same net effect is happening, in that the calculated column is calculated on save, but what you are seeing is that it takes two passes, right? So as long as you don’t inspect the item in between the two passes, everything is “as expected”?
M.
Since mý application is a document library, I used calculated fiedlds to avoid creating “background versions” so published documents would not undergo a revision when published to a najor revision. Is there a good way to refresh the calculations of an entire list rather than on change of each item, since thise do not seem to keep over time?
I’m not sure what you mean by “background versions”, but the only way to force a recalc on all items in the list us to resort to code.
M.
I just meant that I did not want to create a revision when the calculation was updated since the document library has revision control in place that would change a published document to a draft document.
I now know I should have had a workflow determine the list value (of a due date) on change rather than use a calculated field, but I am not able to make that change now becuase it would require all items in the library to undergo a revision before the values were correct.
Anyway, do you have/ know of an example of the code needed to run a recalculation of the field on the entire list, say a at a certain time every day?
Thanks,
I have been using some javascript to attempt to dynamically calculate the fields in a content editor web part on the new and edit form. So far I haven’t had any luck. I think in order to get the calculation to happen on the new and edit form you have to have some type of OnChange or OnFocus javascript in the content editor web part link. In 2010 you save a text file containing your javascript in the document library, then link to it in the content editor to call your javascript. I would really like to know how to do this with java but no one seems to know ..
Jeb:
You can definitely do what you describe. The trick is to decide which events to attach to to fire the recalculations. That will vary based on the type of column you’re using.
I’d recommend jQuery over JavaScript, as it give you far more powerful abstractions.
M.
Hi,
I wanted to know if there are any field editor for the calculated column, which saves the formula and updates the list items
Vivek:
I’m not sure I understand your question.
M.
I’m trying to understand how does all the list items get updated when you set the formula in calculated column?
Any kind of event involved , like asynchronous event handlers?
Vivek:
I’m not sure how it all works under the covers, but I believe that when you make a change to the formula an asynchronous process kicks off to recalculate all of the values.
M.