Microsoft® SharePoint® allows you to add a calculated column to a list or library. By creating a formula in these calculated columns you can include data from other columns and performs functions to calculate dates and times, to perform mathematical equations, or to manipulate text. In calculated columns, formulas are based on Microsoft® Excel® functions and syntax, but there are some limitations on the functions you can use and some subtleties in the syntax. You can find more information about the various kinds of formulas you can use in a SharePoint calculated columns in the Microsoft article Calculated Field Formulas.
As with other columns, with a Lightning Conductor calculated columns, you can apply conditional formatting on the calculated value, filter on the calculated column as well as sort or group by the result of the calculation. This page contains information on how to add a Lightning Conductor calculated column and information on formulas you can use. Other examples of calculated columns can be found on the Walkthroughs section.
Adding a Calculated Column
To add a Lightning Conductor calculated column, use the following steps:
- Click the Add calculated column icon in the top right hand corner of the Columns section on the Columns tab.
The Add calculated column dialog is displayed., which contains three sections:
- Column name: In the text box, type a name for the column.
- Column type: Select one of the following values: String, DateTime, Integer, Double or Boolean. You must select the data type that is to be calculated. This may not be the same value as the columns involved in the calculation.
- Expression: Use this section to create the formula for the calculated column. A formula can contain functions, column references, operators, and constants. To insert a column reference, select the column in the dropdown list and then click Insert field.
You must not type the column reference, you should always use the drop down list to insert references to columns. Examples of formulas can be found later on this page.
- Once you have created finished configuring the formula, click Save.
The Add calculated column dialog box closes and the new calculated column is displayed at the top of the columns in the Columns Configuration section.
- Select the check box to the left of the column if you wish to display the column in the Lightning Conductor App Part. You can then drag-and-drop the column so it appears in the required order in the App Part.
- To edit the calculated column, click the Edit calculated column icon to the right of the column name.
- To delete the calculated column, click the Remove calculated column icon.
Information on calculated column formulas
You can use 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.
- To combine two or more columns, use the addition operator ( + ), for example to create a calculated column, fullname, which concatenates the columns, Firstname and Lastname, use the following formula:
- Use String properties, for example, to find the length of the values stored in the Title column:
- Use String methods, for example,
- To convert a strings stored in the Title column to lower case,
- To retrieve a substring for values stored in the Title column, starting from character position 5, for a length of 6 characters, where the first character of a string is position 0,
- To remove all spaces for values stored in the Title column,
Date and time formulas
- Calculate the difference between two dates, use the subtraction operator ( – ) and then use Math object methods to present the difference as required, for example, when using a task list, create a calculated column using the following formula to display the number of days to complete a task.
- The date method, getMonth, returns a number that represents a month, for example, 0 is January, 1 is February. To return the name of the month use the following expression:
['January', 'February', 'March', 'April', 'May', 'June', 'July', 'August', 'September', 'October', 'November', 'December'][([LastModifiedTime].getMonth())]
- Many of the out-of-the-box managed properties that represent date values are provided as string in the format: YYYY-MM-DDTHH:MM:SSZ, such as, 2013-11-18T08:00:00Z.
- To display such managed properties without the characters, T and Z, use the expression, [DueDateOWSDate].replace(/[TZ]/g,” “).
- To convert such values so you can subtract or compare values of a date/time use the formula, Date.parse([DueDateOWSDate])
You can use the formulas to test the condition of a statement and return a value. Conditional expressions take the form of a ternary operator called the conditional expression operator, ?:, which use the following format:
(condition)?(evaluate if condition was true):(evaluate if condition was false)
For example, if you want to create a calculated column that contains either Before or After, depending on the year the item / file was created, then the formula would look similar to:
Also, if the Lightning Conductor conditional formatting feature does not satisfy all your need, you can apply formatting as part of your calculated column, for example,
[DueDate]<[StartDate]?"<font style=‘color:red;’>Before</font>":"<font style=‘color:blue;’>After</font>"
This would result in a Lightning Conductor Web Part displaying data similar to the following screenshot. Note: You must select the Allow HTML check box for the calculated column on the Display tab.
- The following formula, displays a red circle, if the item is overdue, otherwise green circle is displayed. Remember to select the Allow HTML check box on the Display tab:
[DueDate]<Date.now() ? ("<div style=‘background:red; border-radius: 5px; width: 10px; height: 10px;’></div>"):("<div style=‘background:green; border-radius: 5px; width: 10px; height: 10px;’></div>")
- The following formula adds a clickable button:
[ID]%2 ? $("<button onclick=‘alert("+[ID]+"); return false;’>"+[ID]+"</button>") : [ID]
You can use formulas to perform a variety of mathematical calculations, such as adding ( + ), subtracting ( – ), multiplying ( * ), dividing numbers ( / ), remainder after dividing a number by a second number ( % ) ; and using the Math object properties and methods, for example,
- Round values in the NoTasks column to the nearest integral value:
- Calculate the area of a circle when the value of the radius is saved in the Radius column: