Using Formulas in Calculated Columns

Calculated Column is great feature of SharePoint 2007. Calculated columns are used in lists and Document Libraries. We can use formulas in Calculated Columns to manipulate and display the data in different formats.

Formulas can be categorised into 4 types:

1. Conditional Formulas

2. Date & Time Formulas

3. Mathemetical Formulas

4. Text Formulas

1. Conditional Formulas : we can use these formulas to test the condition of a statement and return a YES or NO value, to return blank value to represent null value.

Example,Check if a number is greater than or less than another number

=[Column1]>[Column2]

=IF([Column1]<=[Column2], "OK", "Not OK")

2.Date and time formulas: We can use these formulas to perform calculations that are based on dates and times, such as adding a number of days, months, or years to a date, calculating the difference between two dates, and converting time to a decimal value.

Note:When you manipulate dates, the return type of the calculated column must be set to Date and Time.

Example,To add a number of days to a date, use the addition (+) operator.

=[Column1]+[Column2]

Adds 3 days to 6/9/2007.Column1 value = 6/9/2007, Column2 value = 3, Result = 6/12/2007

3.Mathematical formulas: We can use these formulas to perform a variety of mathematical calculations, such as adding, subtracting, multiplying, and dividing numbers, calculating the average or median of numbers, rounding a number, and counting values.

Example,To add numbers in two or more columns in a row, use the addition operator (+) or the SUM function.

=[Column1]+[Column2]+[Column3]

4.Text formulas: We can use these formulas to manipulate text, such as combining or concatenating the values from multiple columns, comparing the contents of columns, removing characters or spaces, and repeating characters.

Example,To change the case of text, use the UPPER function.

=UPPER([Column1])

For more reference Click Here

Creating a Site Column and adding it to ContentType using Object Model

Here is an example, how to create a Site Column and add the Site Column to a Content Type.

public SPField AddField(string fieldDisplayName, SPFieldType fieldType, bool bRequired)
{
SPField field = null;
try
{
// get the parent web
SPWeb web = contentType.ParentWeb;

string fieldName = web.Fields.Add(fieldDisplayName, fieldType, bRequired);
web.Fields[fieldDisplayName].Group = "My Site Columns";
web.Fields[fieldDisplayName].Update(true);

field = web.Fields[fieldDisplayName];

// add a field link to the content type
contentType.FieldLinks.Add(new SPFieldLink(field));
contentType.FieldLinks[fieldDisplayName].Required = bRequired;
contentType.Update(true);
}
catch
{ }
return field;
}