Advanced Column Display

Follow

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

img1.png

  • 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"]})