Activities
      Back to home
      1. Knowledge Base
      2. Activities

      Advanced Column Display

      Summary

      You can view more complex data in search results by using special columns called Function Columns. These allow you to name the column heading yourself and express a function name which calculates a value to be displayed.

      Guide

      Adding a Formula Column

      To use a Column Function

      • Click on the cog icon at the far right of the column headings of your search results to bring up the Column selector
      • Select *Oql Column Function from the column selector to add it to your list of columns

      • Once added, the new column will appear at the bottom of the column list. You can move the column around in the order as usual

      img2.png

      • Click on the left side of the column to edit the column heading name. Once you've changed the name, click on the green tick to save the changes.
      • Click on the right side of the column to edit the OQL function. A list of available functions is described below.
      • Click on Save to view the column in the search results.

      Column Functions

      dateDiff()

      Displays a number that represents the amount of time between one date and another.

      Syntax

      dateDiff({"startDate":"startDateField", "endDate":"endDateField", "dateFormat":"dateFormat"})

      startDateField = the name of a date field - if empty assumes current date/time

      endDateField = the name of a date field - if empty assumes current date/time

      dateFormat = the format used for the date comparison, s = seconds, m = minutes, h = hours, d = days, w = weeks

      NOTE you can also use the XX_lastevent fields to identify differences between the dates that events occurred.

      Examples

      • Return the number of days between the Reported Date and the Target Date
      dateDiff({"startDate":"reportedDate","endDate": "targetDate", "dateFormat":"d"})
      • Return the number of minutes between the current date time and the Target Date
      dateDiff({"endDate": "targetDate", "dateFormat":"m"})
      • Return the number of days between the last VERSU event and the last VERCR event.
      dateDiff({'startDate': 'VERSU_lastevent', 'endDate': 'VERCR_lastevent', "dateFormat":"d"}})

      Sum()

      Adds the values from multiple fields together.

      Syntax

      sum({"columns":[columnList],"dp":"decimalPlaces"})

      columnList = names of columns to be added, comma separated, minimum 2

      decimalPlaces = number of decimal places to show result in

      Examples

      • Add the values in the totalCosts and totalSales fields and presents results to 2 decimal places.
      sum({"columns":["totalCosts","totalSales"],"dp":"2"})

      sub()

      Subtracts one value from another field.

      Syntax

      sub({"columns":[columnList],"dp":"decimalPlaces"})

      columnList = names of columns to be added, comma separated, minimum 2

      decimalPlaces = number of decimal places to show result in

      Examples

      • Subtract the totalCosts from the totalSales fields and presents results to 2 decimal places.
      sub({"columns":["totalSales","totalCosts"],"dp":"2"})

      prod()

      Multiplies the values of multiple fields by each other.

      Syntax

      prod({"columns":[columnList],"dp":"decimalPlaces"})

      columnList = names of columns to be multiplied, comma-separated, minimum 2

      decimalPlaces = number of decimal places to show result in

      Examples

      • Add the value of the totalCosts and the value of the quantity field and presents results to 2 decimal places.
      prod({"columns":["totalCosts","quantity"],"dp":"2"})

      div()

      Divides one field value by another.

      Syntax

      div({"col1":"column1","col2":"column2","dp","decimalPlaces"})

      column1 = the number being divided, i.e. the dividend

      column2 = the number the dividend is being divided by

      decimalPlaces = number of decimal places to present the results in

      Example

      div({"col1":"totalCosts","col2":"quantity","dp","2"})
      • Divides the totalCosts by the quantity and presents the results to 2 decimal places
      sub({"columns":["totalSales","totalCosts"],"dp":"2"})

      costSum()

      Returns the sum of the Job Costs for specified cost types.

      Syntax

      costSum({"costType": [costTypeList]}) 

      costTypeList = list of cost types to include, comma separated

      Example

      • Returns total cost for only 'Materials' and 'Labour' cost types
      costSum({"costType": ["Labour","Materials"]})