Calculated Columns

This blog post is an addition to the knowledge served under the technology domain in my blog. Going specifically into MOSS 2007, which has been the source of my bread and butter (no wonder I've gained weight :)), there is a feature in this product called as MOSS 2007. All people associated with the technology know deep into how to deploy the site column through features, how to create a new site column, types of site columns. What I thought people would love to know is that there is a feature called as calculated site columns in MOSS 2007. The formulas mentioned below can be used in MS EXCEL as well in case you are not a MOSS buff :).

The real mantra here is that there are site columns that need the currently logged in user name or the current date at times. The way to do these is to use the [Me] and [Today] objects. There is a further flaw in this approach. Directly entering this value in the visual code editor for the column value will throw an error. For this first create a column as Me in the content type and use that in the formula. Delete Me after the calculated column has been created to achieve the desired result. The same applies to Today object as well. Happy developing from here on :).

• How to deploy these calculated columns through features:

It is not very easy to find a sample of how to define a site column of type calculated using the SharePoint 2007 features framework. The documentations shows how to do it using the user interface (see Calculate data in lists or libraries ). The documentation in the WSS SDK about the  Formula Element in the CAML Core Schemas points in the right direction but is maybe not clear enough. I tested the sample code below by using the site columns in a list definition. You might want to test it out in a content type as well - please provide feedback in the comments.

Code sample feature.xml

<?xml version="1.0" encoding="utf-8" ?>
<Feature xmlns="http://schemas.microsoft.com/sharepoint/" Scope="Site" Title="Calculated Sitecolumn" Id="49C90DCA-6C76-4b30-99DB-7C4C7D100761">
<ElementManifests>
<ElementManifest Location="calcsitecolumn.xml"/>
</ElementManifests>
</Feature>

Code sample calcsitecolumn.xml

<?xml version="1.0" encoding="utf-8" ?>
<Elements xmlns="http://schemas.microsoft.com/sharepoint/">
<Field Name="CalcDemo" StaticName="CalcDemo"  Type="Calculated" DisplayName="Calculated column" ID="{DD01AF0E-5E58-4e50-B3E8-1BFC866CA9A0}" ResultType="DateTime" Format="DateOnly" ReadOnly="TRUE">
<Formula>=StartDate+7</Formula>
<Default></Default>
<FieldRefs>
<FieldRef Name="StartDate" ID="{64cd368d-2f95-4bfc-a1f9-8d4324ecb007}" />
</FieldRefs>
</Field>
</Elements>

• How to use the formulas in the site column (Its basically simple VB code):

You can use the following examples in calculated columns. Examples that do not include column references can be used to specify the default value of a column. For details on how to use these formulas refer to: http://office.microsoft.com/en-us/sharepointtechnology/HA011609471033.aspx