RSS Feed Is this your first time here? Get regular updates

How not to develop a SharePoint [Today] Calculated column

Date:August 22nd, 2011 Author: Tags: , ,
Category: Calculated Columns, Highlighter, Training Comments:4 ;

After developing SharePoint Highlighter, we seriously considered expanding on this area with a Calculated Column that would allow you to use [Today] in the formula. If you’re thinking “But you can already!” I suggest looking at Ryan’s article about the [Today] column trick.

Needless to say, this would be a very useful tool for almost any SharePoint user. After much researching and prototyping we came to an unfortunate conclusion: Although it was possible to make a Custom Field Type to do this, it was almost certainly impractical (certainly for us).

The avenues we investigated finished in three types of dead-end:

  • Brick wall: A completely impassable system limitation.
  • Overgrown with brambles: Nothing but pain for all involved.
  • Swamps: A long unpleasant slog, with an uncertain outcome.

The Brick Wall

The first thought in any developer’s mind when making a variation on a class should be “Inherit it”. It allows you to take advantage of the existing class’ methods with minimal difficulty and yet offers (almost) total control over its behaviour.

Attempting to do so will reward you with the slightly misleading error:

The type ‘Microsoft.SharePoint.SPFieldCalculated’ has no constructors defined

A brief search in the MSDN documentation for SPFieldCalculated shows that the absence of public constructors is intentional:

Windows SharePoint Services 3.0 does not support inheriting from this class.

And so this avenue ended in a brick wall. More worryingly; disallowing the inheritance of a class is usually a sign that something so heinous is happening inside that Microsoft doesn’t want it reproduced.

Brambles

Intrigued by the promise of horrifying creatures dwelling within, I had a look inside the class to see what was happening (using my own powers of code intuition, and not any kind of questionable reverse engineering method).

It seems the actual calculations take place outside of the SPFieldCalculated class, using instead a call to SPRequest. This new lane of research quickly ended in spiky brambles, as using a direct call to SPRequest would make our product (and hence anything it’s installed on) unsupported by Microsoft.

Undeterred, I took inspiration from the billing system at my old work and looked at keeping the ‘black box of calculation mystery’ class running in the background, with the new class acting as a wrapper around it.

Creating a Custom Field Type that kept a hidden field for data storage in the background was an architecture we’d experimented with when creating Highlighter, so adding a relatively minor call to update the formula with the current date and time seemed quite easy.

The thorny ending in this case was the enormous potential server load; updating the formula every time the list was viewed (to keep it up to date) caused the entire list’s worth of calculations to be refreshed. Viewing just 20 items would make up to 5000 items recalculate their values for each of our columns on the view.

Swamps

The final and most desperate option was to do it ourselves: Completely rewrite all or a subset of the calculated column functions, and handle the associated function nesting.

I’m DIM: Doin’ It M’self.

Following this path would mean wading through the boggy and unpleasant process of exactly reproducing Microsoft’s function calls; avoiding the murky bottomless pools of performance issues. All the while hoping we wouldn’t run into any of the hungry and sharp-toothed show stoppers along the way.

As you may have guessed, we didn’t venture down this path.

Why are you telling me all this?

Because although these paths aren’t available to us as an ISV, it doesn’t mean they aren’t options to you (except the brick wall, of course).

Brambles: Using SPRequest is unsupported, but if this doesn’t deter you then this is still very much an option. Similarly, if you have an abundance of processing power (or really need a space heater in your server room) performing complete column recalculations on every view shouldn’t bother you.

Swamps: If you have very specific requirements you could get away with only recoding a few functions, which would greatly reduce the chances of you disappearing forever. Alternatively, if you have a large herd of idle developers you could set about recreating the whole set. However, I doubt this is a serious option for anyone except the head of the Microsoft Silverlight team.

Tags: , ,

Did you enjoy this post? Get the next one by RSS or by email

4 Responses to “How not to develop a SharePoint [Today] Calculated column”

  1. Robin says:

    Great piece of tech-writing! What did the Silverlight Team do to upset you?

  2. Stuart Pegg says:

    Thanks Robin. :)

    The Silverlight Team are OK in my books; this was more of a reference to rumors that the next version of SharePoint would be using HTML5 rather than Silverlight. Also that this may be part of a more general Microsoft strategy, although that’s complete speculation.

  3. amarkeev says:

    Hi, Stuart!

    All depends from what are you trying to use your calculated column for. For some scenarios, I reckon it would be enough to use XSLT approach, as soon as in XSLT you can grab the current date easily, for example using corresponding ParameterBinding.

  4. Stuart Pegg says:

    This is true in a some instances, but not if you want to implement a fully functional calculated column. You’d essentially be taking the ‘Swamp’ route, but in XSL instead of C# (possibly add some tar pits).

    One of my previous articles covers this ground: How to use [Today] in a SharePoint list. Although XSL would could solve some requirements, I’m a somewhat cautious when it comes to recommending someone to develop a Custom Field Type for a single purpose (unless they have previous experience in the area).

Leave a Reply

Anti-Spam Quiz: