# 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

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