SharePoint’s Calculated Columns


In SharePoint, you can create a calculated column for any list or library. A calculated column can use values from one or more of the other columns in the list. For example, one of the first uses I found for using a calculated column was when I wanted to sort by more than two columns in my view. For example, suppose I had a library of project files that included the following fields:

Calculated Columns 01

In my view, I want to sort first by [Project_Name], then by [Year] and then by [Title]. When I attempt to create a custom view, I only get to choose two levels as shown below:

Calculated Columns 02

This would sort my files first by project, then year, but the actual documents would appear randomly by document name. The way to solve this problem is to create a calculated field to concatenate two or more of the columns that will be sorted in the same direction (either both ascending or both descending). For example, I might combine project and year into a single calculated column as shown in the following figure:

Calculated Columns 03

Now I can go back to my new definition and use [Project_Year] as my first sort and [Title] as my second sort. In the columns that I display, I can still use [Project_Name] and [Year] as separate fields and not display the calculated field [Project_Year].

Calculated Columns 04

Note the use of the ampersand operator to concatenate the two string fields. (Yes, [Year] is stored as a string in this list.) If I had used a plus sign as the operator, I would get an error because the project name field does not begin with a number even though the [Year] string can be in theory converted to a number.

The net result is a view that appears to sort my files by project first, then year, and then document Title. There are other web parts where you use a similar trick such as the Content Query web part.

When to use the ‘+’ vs. the ‘&’ operator

To illustrate the difference between the plus sign (+) operator and the ampersand (&) operator, I will use a very simple table that has several string fields and several numeric fields as shown below:

Calculated Columns 05

The columns [String] and [Number] are both stored in the list as string data (note the left justification of the numbers for the [Number] column. On the other hand, both [Number_Col] and [Number_Col_2] are numeric columns as you can discern by the right justified values.

Next I am going to create several calculated columns using the following formulas:

        Calc1 = [Number] + [String]

        Calc2 = [Number] & [String]

        Calc3 = [String] + [Number_Col]

        Calc4 = [String] & [Number_Col]

        Calc5 = [Number_Col] + [Number_Col_2]

        Calc6 = [Number_Col] & [Number_Col_2]

While SharePoint allows me to create each of these formulas, displaying the resulting values in my list shows some interesting results.

Calculated Columns 06

As you can see from the results, attempting to use a plus sign (+) when concatenating two values in which at least one is a string results in an invalid value (#VALUE!). On the other hand, concatenating a string and a number is no different than concatenating two strings or even two numbers when using the ampersand (&) operator. These results were the same no matter whether I choose the final result to be a string or a numeric value when defining the calculated column.

TODAY() or [TODAY]

Next I tried to use the [TODAY] variable to see if I could create a calculated column that would add the numeric value in [Number_Col] to display a future date. When I tried to save the formula for the calculated column, I got the following error:

Calculated Columns 07

Note that this error message tells me that I cannot use volatile functions like [TODAY] or [ME] in a calculated column. However, when I tried to use the function TODAY(), the calculated column was not only accepted when I saved my column definition, but the data displayed as expected when I displayed the list.

Calculated Columns 08

I also tried many of the other Excel-like functions to work with strings and numbers and found that all of the ones I tested also worked as expected as long as the function arguments (columns) were of the correct type.

That’s it for this time. C’ya next week.

Advertisements

3 comments on “SharePoint’s Calculated Columns

  1. Hi Mike, Calculated Columns are only (re)calculated when an item is Created or Changed. Thus the Today() function will return the correct date on Creation or Change. But it will not update if the item is never changed. You can try for yourself, use Today() in Column… press F5 till it’s the next day and… bummer… the calculation now shows yesterdays date… Edit the item.. and yes.. the date is “correct” again.

    • I guess that depends on what you expect the column to do. If you want a column to always have the current date, you are right. However, if you want to grab the date when the row is added or changed, then the behavior is as expected.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s