Follow

Advanced Column Display

Summary | Guide | Adding a Formula Column | Column Functions | Related Articles

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.

Back to top

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

img 1

  • 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

img 2

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

Back to top

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

Back to top

Related Articles

OQL Keywords and Operators

Search Overview

Saving/Deleting a Search View

Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request