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
Hi
Does anyone has solved this issue?
What issue are you referring to?
M.
Hi All,
That’s really a pity, and I’ve just encountered this with one of my lists.
The scenario is that each item has an ‘Effective Date’ and ‘Expiry Date’ fields (which could either be null or contain a valid date) and I wanted to have a calculated column that would tell me whether the item in the list should be viewable/available when retrieved from the list.
Mistakenly I assumed that this formula would update the field when the list was rendered: ‘IF(OR(ISBLANK([Effective Date]),[Effective Date]=TODAY()),”Yes”,”No”),”No”)’. The formula works BTW in Excel (hmmm that got me thinking obviously when a change in the data occurs in Excel then a recalc occurs similar to saving the item in Sharepoint, grrr) and in the list (but obviously only when the item is saved).
SO now I have to put a Grouped filter on in any webpart I use to only show items valid today which is NOT an acceptable solution in Sharepoint IMHO. The filter in the webpart looks like this:
Why is this not a total solution, because I also query this list as a datasource in Infopath, so now I not only have to filter each webpart in Sharepoint, I ALSO have for create complicated filtering in Infopath (if I can even achieve this) which then increases my form complexity AND will ultimately slow data access within my form due to is being an XPath client side filter and not a Sharepoint server side filter.
Not happy Jan! :(
RaYvA:
My approach to get around this is to never use calculated columns. Instead, I either use a Data View Web Part with conditional logic and/or script that alters the DOM upon load. Either of those approaches improves the UX, IMO, and is more reliable. As for InfoPath, I don’t use that, either, preferring to augment the default forms with CSS and/or script.
M.
A tad late to the party here but I have a question. We’re on O365 and creating items via webservice and making use of the webservice response. We’ve created a calculated column that takes the ID and prefixes some text and pads with 0’s. The calculated column worked fine for existing items in the list when I created it, however, we just created a new item using web services and the calculated column returned “LM0000” instead of “LM0123” in the response. Then visiting the list, the calculated column also rendered as “LM0000”. We tried to edit the item using the SharePoint UI and save the item again but it still didn’t update. Any ideas why the calculated column may not work for items created via webservice?
@Ryan:
Have you created the calculated column through the UI or via code somehow? Also, which Web Service are you using? REST, SOAP?
M.
Thank you for this article! I was about to set up a list with a single column tracking 5 variables because I thought it would be more efficient to pull info from it with calculated rather than just have 5 separate data columns.