SharePoint Calculated Columns
by Andy Wessendorf
SharePoint Calculated Columns are powerful tools when creating out-of-the-box solutions. With these columns, we can manipulate other columns in the list item. Below are a few basic functions complete with details on how to utilize them.
There are some limitations, but we outline what they are and to work around them at the conclusion.
Basic Functions and Common Examples
One common use for a calculated column is to
create deadlines and reminders for date fields. For example, a custom calendar where the End Date is always seven days later than the Start Date. In that example we would create a calculated column such that:
When using dates, adding to that date is done on a day-to-day basis. So in the formula above, we add 7 days.2. 'If' Statements
Another common use is setting the value of the column based on another column, usually a choice field. For example, we present the user with a checkbox. If they check the box, we want the status to be "complete", so we have the following formula:
If Statements, the condition comes first, then the true case and finally the false case.
3. Boolean Operators
Sometimes our IF statements can get complex. To help us along the way, we have
Boolean Operators. For example, in a column called
Favorite Team, a user selects between
Bengals. We want to determine what state they are probably from, so we have the following formula:
=IF(OR([Favorite Team]="Spurs",[Favorite Team]="Cowboys"),"Texas","Ohio")
Also at our disposal are 'AND
' and 'NOT
'. All of these encapsulate their conditions with parentheses and separate them by commas.
4. The HTML Trick
SharePoint pages that utilize Query Strings are very powerful. These pages allow us to create one page that automatically filters based on a user selection. Through that we can create efficiencies and standardizat
ion. How do we navigate to those pages? We can do this through a calculated column and an HTML trick.
Assume we have a list of Projects with a field for Title. We also have a Query String page called Project and the query parameter is ProjectName. In our list, we want to create a calculated column that concatenates an HTML string with the Title field to create our link. See the formula below:
=CONCATENATE("<a href="http://server/sitename/Project.aspx? ProjectName=",Title,"</a>")
This formula brings together a typical anchor tag that will create a link to our page with the Query String properly set. The real secret sauce to making this work though is returning the formula as data type Number. This will convert the text to HTML. Now we input this column on a page in a list view and we have all of our Project Dashboard links! Consider using this also for images that are based on a calculation to display indicators.
For more on the Query String page, check out
Episode 12 of SharePoint Power Hour
presented by Rackspace. (These one hour sessions are presented free and live on YouTube every Wednesday at Noon ET.)
1. Nested 'Ifs'
There is a limitation of
nested ifs in one statement. We commonly use nested ifs to create an output based on the different types of results. To solve this, we can either break out one of the ifs to not be nested, or use a
2. Today function
Wouldn't it be great if we had a calculated column that adjusted based on today's date?
SharePoint doesn't allow you to use the
[Today] function in a calculated column, but there are
ways around that. Unfortunately, even using the trick outlined in that link, the
[Today] function doesn't operate properly because it won't update unless the item is changed.
If you are determined to update based on Today's date, I would suggest a workflow or timer job that automatically updates every item in the list at a specified time and using the
Modified Date as your basis for calculation.
3. Lookup Columns
When creating the calculated formula, you may notice that lookup fields do not show under the
Insert Column heading.
Lookup columns cannot be referenced in a calculated column. The suggested workaround here is to use a workflow that copies the lookup value into a text field and to use that copied field in the formula.