With the Lightning Conductor App Part, you can add calculated columns to your Lightning Conductor views.

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.

Using the Lightning Conductor App Part, you add calculations similar to those you can add to a SharePoint calculated column plus you can add almost any calculation that you want. The advantage of using a Lightning Conductor calculated column is that the calculation is executed every time the page is displayed, whereas with a list / library calculated column, the formula is only executed when the list item or file is modified. Also, the Lightning Conductor calculated column, is not based on Microsoft Excel formulas, but on JavaScript based operators, calculations and functions.

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:

  1. Click the Add calculated column 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.


    Select Field, 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.

  1. 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.

  2. 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.

    Calculated column

  3. To edit the calculated column, click the Edit calculated column Edit Calculated Column icon to the right of the column name.

  4. To delete the calculated column, click the Remove calculated column Delete Calculated Column icon.

← Go to top of section

Information on calculated column formulas

This section contains examples of formulas you can use in the Lightning Conductor calculated column. These formulas are based on the JavaScript language.

Text 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.

  1. 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:
       [Firstname]+" "+[Lastname]

  2. Use String properties, for example, to find the length of the values stored in the Title column:
       [Title].length

  3. Use String methods, for example,

    • To convert a strings stored in the Title column to lower case,
         [Title].toLowerCase()

    • 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,
         [Title].substr(5,6)

    • To remove all spaces for values stored in the Title column,
         [Title].replace(" ","")


Date and time formulas

JavaScript does not offer extensive date parsing or formatting. This section contains examples of manipulating out of the boxYou can use formulas to perform calculations that are based on dates and times, such as finding the day of the week, calculating the difference between two dates, and converting time to a decimal value.

  1. 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.
       Math.floor(Math.abs([DueDate]-[StartDate])/(24*60*60*1000))

  2. 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())]

  3. 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])


Conditional Formulas

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:
   [DueDate]<[StartDate]?"Before":"After"

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.

Other examples:

  • 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]

Mathematical Formula

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,

  1. Round values in the NoTasks column to the nearest integral value:
       Math.round([NoTasks])

  2. Calculate the area of a circle when the value of the radius is saved in the Radius column:
       Math.pow([Radius],2)*Math.PI

← Go to top of section

← Column Filtering
Display Tab →

Feedback

Was this helpful?

Yes No
You indicated this topic was not helpful to you ...
Could you please leave a comment telling us why? Thank you!
Thanks for your feedback.

Post your comment on this topic.

Please do not use this for support questions.
For customer support, please contact us here.

Post Comment