SharePoint 2010 Datasheet View Bugaboo

I ran into this today and thought it might be something worth jotting down to save others the same pain.

When you’re editing a list in Datasheet view in SharePoint 2010 and you’re working with a Calculated column, you can see the formula at the bottom of the view for the cell in which you have your cursor. All well and good. See the screen snip below; the yellow highlighting is mine.

 image

Since it’s a Datasheet view, however, it’s pretty easy to mistakenly type a number into the cell without noticing that it’s a Calculated column. When you do this, you get the prompt below. Again, my highlighting.

image

Now, here’s where I messed myself up. I didn’t read the message carefully enough and just thought it was telling me that it would take a while to be helpful. In fact, what was happening was that SharePoint was about to replace the formula in the column with a new formula, =50. This meant that every cell then had the value 50 rather than the previous calculation.

It was easy enough to fix, once I figured out what I had done, but some calculations I was doing in a DVWP sure looked crazy for a bit!

How Are a SharePoint List’s Calculated Columns Stored and Rendered?

Ever helpful @MSwannMSFTMatt 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/JCP5

sympmarc
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 formula
 
MSwannMSFT
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

Quick Tip: Testing SharePoint Calculated Column Formulas

I usually test my formulas in Excel. The syntax is the same; if it works in Excel, it’s going to work in SharePoint. (There may be exceptions to this, but I haven’t run into them.)

Simply set up some cells with the values you need and then work out your formula. Once you’ve got it working the way you want it, copy it over to your calculated column and replace the cell references with column names (in brackets).

string;# Before Values in a SharePoint List’s Lookup Column Based on a Calculated Value

I’ve seen lots of posts discussing this, but no workarounds.  Here’s the situation:

  • One list (List 1) contains a list of values that are calculated (say ColumnC = ColumnA & ColumnB)
  • Another list (List 2) uses ColumnC above as a lookup for one of its columns
  • If you go into Datasheet view for List 2, you will get the following error every time you try to change an item (with your column name mentioned instead of mine):

image

  • If you click on the dropdown for the column in List 2 that contains the lookup, every value will be preceded by ‘string;#’.
  • If you select the entry that looks exactly like the current value (but with the ‘string;#’ preceding it), you can then save the item.

This seems to be an annoying “feature” of how a calculated lookup column is represented in the Datasheet view.  We’re running SP1 with all current patches at this point, so I don’t hold out hope of a fix anytime soon.

The best workaround that I’ve come up with is to not calculate the lookup column, but to have a simple workflow which does the calculation and updates the column whenever the item changes.  This should work just fine, but since my calculated column is already embedded as a source for many other lists (yes, I inherited a solution, yet again), it’s problematic to make the change.

Anyone seen a better workaround?  This really gets in the way if you need to do some wholesale changes on a long list that contains this problematic type of column.  I’ll certainly post anything I find that gets around this better.

 

Showing This Month’s Documents in a SharePoint List

I’ve frequently been asked if it is possible to display only the current month’s documents in a SharePoint list.  There is no obvious way to do this, as the filtering mechanism in a view will not allow you to specify a formula as its criteria.

The trick is to create a calculated column on the list.  I like to create a Site Column for this so that it is available to me anywhere in the Site Collection that it might turn out to be useful.

So, create a column (either on the list or as a Site Column), choose “Calculated (calculation based on other columns)” as the type of column, and make the data type “Yes/No”. (This is simply a Boolean column, but Yes and No are the values displayed rather than TRUE or FALSE.)  Now all you need is a formula that evaluates to TRUE if your conditions are met, and you’re off and running.

Here’s an example.  I created a column called ThisFiscalPeriod, with the formula:

=AND(TEXT(YEAR(Today),”####”)=[Fiscal Year],(TEXT(29*MONTH(Today),”mmmm”)=[Fiscal Month]))

I had already created columns called Fiscal Year and Fiscal Month that were part of the Content Type that I was using in the list.  Whenever the current year and month matched the Fiscal Year and Fiscal Month, the formula evaluates to TRUE, and the column will display or can be tested to see if it is “Yes”.  (See my earlier post on how to use the Today value in a formula.)

If you wanted to show all of the items that had been modified in the current month, then the formula would look like this:

=AND(TEXT(YEAR(Today),”####”)=TEXT(YEAR([Modified]),”####”),(TEXT(29*MONTH(Today),”mmmm”)=TEXT(29*MONTH([Modified]),”mmmm”)))

Note the neat trick in this part of the formula: TEXT(29*MONTH(Today),”mmmm”).  MONTH(Today) returns a number from 1 to 12 which indicates the current month (1=January, 2=February, etc.).  Multiplying that number by 29 gives a serial day number that *has* to be in the right month.  The following table shows how it works:

  

Date Month Serial Day Text Month
1-Jan      1 29 January
1-Feb      2 58 February
1-Mar      3 87 March
1-Apr      4 116 April
1-May      5 145 May
1-Jun      6 174 June
1-Jul      7 203 July
1-Aug      8 232 August
1-Sep      9 261 September
1-Oct    10 290 October
1-Nov    11 319 November
1-Dec    12 348 December