Wednesday, April 16, 2008

Using [Today] in SharePoint calculated fields

I revisited my previously used SPS2003 old trick to force the usage of [Today] value in calculated fields, such as doing a [Created]<[Today]. I think I’ll note it down here for future reference.

By default, SharePoint will not allow creation of a calculated field that is partly derived base on functions such as [Today]. Therefore, the work around is to “trick” SharePoint, literally. This is done by deliberately creating a field call [Today] that of a single line of text (or anything else), then create your calculated field using this text-base [Today] field, e.g. [Created]<[Today]. Once you’re done creating the calculated field, remove the fake [Today] field. Once this is done, the calculated field will make use of the actual [Today] function and calculate your date value accordingly. This article from Chris Johnson has more detail on this approach.

However, do note that there are limitations in using this method since the introduction of content type and site column in MOSS 2007. If you are creating content type up front instead of creating a list the SPS2003 method, you must make sure you do not remove the fake [Today] column from the content type immediately after you’ve created your calculated field. Doing so will prevent you from setting your content type into your list or document library, as SharePoint will perform a round of validation to ensure you do not use [Today] in your fields. Instead, remove the fake [Today] field after you’ve set them into your list or document library.

Also, think there should be a reason why creation of calculated field with [Today] in it is prevented. One reason I can think of is probably for performance reason. Imagine loading a big list and exposing the calculated field. Each rendering of this field might probably invoke the [Today] function, which at the end gives some performance hit to the loading. Therefore, should try to avoid using this method unless it is necessary.

1 comment:

Stefan Costan said...

what about this solution

is a sharepoint custom calculated field