Calculated fields in SharePoint are very helpful, when you want to store information in an item depending on the values of another item.

Example:

When you have a column “Purchase date” in your list and you need to store (or display) the period of the purchase in the form “yyyy-mm” you can use a calculated field in your list with the formula

=year([Purchase date]) & “-” & month([Purchase date])

Within the formula you can use each visible field from your list or library. But, which functions are available within these formulas?

A good overview about formulas and functions is available at the Microsoft Office Support site: http://office.microsoft.com/en-us/windows-sharepoint-services-help/CH001171154.aspx

An overview about all available functions could also be found in the Microsoft Office Support site: http://office.microsoft.com/en-us/windows-sharepoint-services-help/CH001171117.aspx

Some examples are available at http://office.microsoft.com/en-us/windows-sharepoint-services-help/examples-of-common-formulas-HA001160947.aspx

Information for developers:

As I have discovered, the result value of a calculated field is stored in the content database. The calculation is done, when you call SPListItem.Update() for an item. But the calculation is ignored, when you use SPListItem.SystemUpdate() for an item. That means, when you change the value of a column used in a formula and you call the SystemUpdate-method, your calculated field will show the wrong value (my experience with SharePoint 3.0/2007, not verified with SharePoint 2010).

Advertisements