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:
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:
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:
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].
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:
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.
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:
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.
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.