How to create a calculated column
Calculated columns can be created in the Lightning Conductor by selecting Add calculated column at the top of the Columns tab:
Calculated columns created in this way exist only within this Lightning Conductor instance; they are not created in any SharePoint list like the built-in SharePoint calculated columns are. After clicking on Add calculated column, a dialog will pop up:
Column name: Name the column anything you like.
Column type: Choose the appropriate data type for this column: String (text), DateTime, Integer, Double (with a decimal point), Boolean (true/false, yes/no).
Expression: You write your expression within the large expression box. Where your expression refers to some other field, select from the available fields in the dropdown box and click Insert field to insert a placeholder into your expression at that point. The placeholder will be the internal name of the field, with square brackets - for example [Title]. See further below for syntax and examples of Expressions.
In version 5.1.0.0 we added 3 predefined calculated column examples based on common customer requirements. In version 6.2.0.0, we added Item Attachments to the set of predefined calculated columns. Click on the little star to the right of the Expression box to choose one of those:
Sort/Filter using: This dropdown allows you to select one of the available fields to sort or filter by, if you want the Lightning Conductor to sort or filter by something other than this calculated column. A scenario for this might be: You create a Lightning Conductor calculated column to concatenate First name and Last name into a column called Full name, but when a user clicks on the header to sort the column, you want it to sort by the existing Last name field.
Expressions
Generally speaking, the Lightning Conductor uses JavaScript syntax in its calculated columns. A good reference is W3 Schools.
Conditions
Conditions can be written using the syntax described here:
Condition ? ThenClause : ElseClause
An example might be if you have columns with different internal SharePoint names, and you wish to "merge" them into one column in your Lightning Conductor view. (Note: this is also one of the predefined expressions available in the Lightning Conductor, but it gives a good example of the syntax.) The expression below means: if there is a value in the DisplayName field, then show that, otherwise show the value in the FullName field.
[DisplayName] ? [DisplayName] : [FullName]
Another common use case is to display some generic default text if the value of a column is empty in a given item. The expression below means: if there is a value in YourField, then display it, otherwise display some default text.
[YourField] ? [YourField] : "Some text"
Hyperlinks
Construct a hyperlink
"<a href='YourLink'>DisplayText</a>"
If you wish to include some dynamic data from the current Lightning Conductor item, you can add placeholders within the link portion. For example, to construct a link to a page that uses the item's account number as a Query String parameter (assuming the internal name of your account field is AccountID):
"<a href='YourPage?Account="+[AccountID]+"'>YourDisplayText</a>"
Note that after [AccountID]+ there is one double quotation mark (to begin the next section of the construction) and one single quotation mark (for the end of the URL).
Remember to enable the Allow HTML setting for this calculated column.
Open a file in a new browser tab
"<a target='_blank' data-interception='off' href='"+[FileRef]+"?web=1' >"+[FileLeafRef]+"</a>"
Expressions with Dates
Calculate the difference between two dates
The following expression will calculate the number of days that have passed since the date the item was created:
(new Date()-[Created])/86400000
Notes:
- new Date() gives you the current date and time.
- Instead of [Created], you can use any datetime type column. If it's in the future, you may wish to reverse the calculation.
- Dates are represented by numbers in JavaScript, so when the calculation is performed, the result is in milliseconds. 1000 ms in a second x 60 seconds in a minute x 60 minutes in an hour x 24 hours in a day = 86,400,000 that you must divide by to get a result in days.
- You can then set the formatting of this calculated column to #,### on the Display tab (by typing over the existing format), so the number of days appears as a whole number.