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
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!
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.
2:58pm, Dec 07 from Web @willhlaw @sympmarc @jvossers that’ll teach me to answer anything before actually looking at the db…
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!
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.
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!
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/JCP5
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