Catalogue of Operators, Functions and Inputs

This topic is a catalogue of the operators, functions and inputs that can be used in calculated columns and calculated header fields. It is divided into:

For each function and input described below, simple examples are given. These are initially displayed collapsed so that you can search through the list to find the function or input you are looking for. You can then expand the example to see more information.

See the Formula Gallery for examples of how specific problems can be solved using combinations of the available functions and inputs.

Functions

The formula editor has a range of functions available for you to use. These are found in the side panel under Functions. You can type a function directly into your formula or build them from the side panel.

Basic Operators

+ Addition

An arithmetic addition operation. As a binary operator, it requires an operand on either side of the operator.

operand+operand

Here 5 is added to the Length column value.

— Subtraction

An arithmetic subtraction operation. As a binary operator, it requires an operand on either side of the operator.

operand — operand

To calculate length, the From value is subtracted from the To value.

/ Division

The mathematical operator for division. As a binary operator, it requires an operand on either side of the operator.

numerator / denominator

Here the input variable Sample weight is divided by 28.35.

* Multiplication

An arithmetic multiplication operation. Note that implied multiplication, i.e. putting factors adjacent to one another, is not supported. The * operator must be explicitly used.

factor * factor

The Length(m) column is multiplied by 100 to show the result value in centimetres.

% Modulo

An arithmetic modulo operation. This is an integer division operation that returns the remainder after the number is divided by a divisor.

numerator % denominator

In this example, the calculation converts a number representing the Length in metres into a string that specifies the length in feet and inches. The first part simply calculates the length in feet by dividing the length in metres by 12 and flooring the result to the next lowest integer. Then to determine the inches part, we take the length in metres modulo 12, which returns a remainder that is just the number of inches (ignoring the feet part of the length) and we round that to the nearest number of inches. The concat function joins these string results together, identifying the units.

< Less than

A logical less-than operator.

variable < test-condition

The result will be true when the variable Temperature is less than 5, and false otherwise.

> Greater than

A logical greater-than operator.

variable > test-condition

The result will be true when the variable Temperature is more than 5, and false otherwise.

<= Less than or equal to

A logical less-than-or-equals operator.

variable <= test-condition

The result will be true when the variable Temperature is less than or equal to 18, and otherwise false.

>= Greater than or equal to

A logical greater-than-or-equals operator.

variable >= test-condition

The result will be true when the variable Temperature is more than or equal to 18, and otherwise false.

== Equal

A logical equality operator.

variable == test-condition

The result will be true when the variable Description is equal to Day Shift, and otherwise false.

!= Not equal

A logical not-equal operator.

variable != test-condition

The result will be true if the variable Rock Types.Value is anything other than Gneiss, and it will be false when it is Gneiss.

& And

A logical And (&) operation.

({column1.value} < 1) & ({column2.value} > 5)

If the Rock Types.Value is Gneiss and the Rock Categories.Value is not Metamorphic, then the result will be Misclassified to indicate the mismatch.

| Or

A logical Or (|) operation.

({column1.value} < 1) | ({column2.value} > 5)

The test condition is a check of the Rock Types.Value column of a Lithology table. The result is Accept if the value matches either the rock types of Gneiss and Braccia, and otherwise the result is Reject.

NOT — Not

A logical NOT operation.

NOT(test-condition)

The test condition is a check of the Rock Types.Value column of a Lithology table. The result is Accept if the rock type is any value other than Gneiss; if it is Gneiss the result is Reject.

String Functions

str — String

A type conversion function that converts the input expression to a text string.

str(value)

Here we have the str function turning the number 1234 into the string '1234'. The result will be treated as text rather than a number. It can be used, for example, to convert numbers from numeric columns to text strings in order to concatenate with other string values.

left — Leftmost characters

This function is used when you want only a certain number of digits or text letters to be displayed. It returns the specified number of the leftmost characters from a string.

left(string,count)

The variable Texture.Value will have just the first 3 letters of the value displayed in the calculated column.

right — Rightmost characters

This function is used when you want only a certain number of digits or text characters to be displayed. It returns the specified number of the rightmost characters from a string.

right(string,count)

The variable Sample Number will have just the last 3 digits of the value displayed in the calculated column.

upper — Uppercase

This function is used when you want to change the case of a string to uppercase.

upper(expression)

The case of the variable Grain Size will be will be changed to uppercase in the calculated column.

lower — Lower case

This function is used when you want to change the case of a string to lowercase

Lower(expression)

The case of the variable Grain Size will be will be changed to lowercase in the calculated column.

trim — Trim text

This function is used to remove both the preceding and following text or blank spaces from a string.

trim(expression,text)

Wherever the word shift is found, it will be trimmed from displaying in the calculated column. In this case, it will not display in the value of day shift or night shift. Only the words 'Day' or 'Night' will display in the calculated column.

ltrim — Trim off text left of a substring

This function removes the specified text or blank spaces found at the beginning of a string.

ltrim(expression,text)

Here the word the will be removed whenever it is found at the beginning of the Texture Description.

rtrim — Trim off text right of a substring

This function removes the specified text or blank spaces found at the end of a string.

rtrim(expression,text)

Here we have trimmed the digits 000 from the end of a Sample Number.

len — Length of string

This function returns the number of characters (length) in a string.

len(expression)

Here we have used the len function to determine the number of digits in the Sample Number.

concat — Concatenate strings

Concatenates two or more strings. The last parameter in the function argument identifies a separator that will be injected between tokens to separate them in the result. If no separation between the input strings is needed, use a pair of single quotes ' ' as an empty separator string.

concat(expression1,expression2,expression3,separator)

This will put the contents of the Recovery % column between the symbols pre and ext with an underscore character being used between each as a separator.

startsWith — Tests if a string starts with substring

The result is true if the string starts with the specified prefix value; otherwise it is false. You can provide optional start and end information, specifying the position to search the expression.

startsWith(expression,prefix) or

startsWith(expression,prefix,start,end)

Here the result is true when the prefix Gra is detected in the Rock Types.Description.

endsWith — Tests if a string ends with substring

The result is true if the string ends with the specified suffix value; otherwise it is false. You can provide optional start and end information, specifying the position to search the expression.

endsWith(expression,prefix) or

endsWith(expression,prefix,start,end)

Here the result is true when the word check is detected at the end of the geotech's Comment column.

contains — Tests if a string contains a substring

The result is true if the specified search space contains the target value; otherwise it is false.

contains(search space,target)

This is a search of the Description column in a mineralisation table, it will return true if the column contains the word braccia, if not it will return false.

search — Tests if a string contains a character pattern

The result is true if the string contains the matched object; otherwise it is false.

search(regex,expression)

The test condition is a check using a regex search of the Sample Type.Value column in a sample table. The result is INVALID if the sample type is Original and ends in anything other than a number; otherwise the result will remain empty.

Numeric Functions

trunc — Truncates a number to the decimal point

Removes all digits to the right of the decimal point for any value.

trunc(decimal)

The variable input is the RQD, and in this calculated column, only the whole number will be displayed.

round — Rounds a number to integer

Rounds a decimal value to the closest integer.

round(decimal)

The variable input is RQD, and in this calculated column, the output is the value rounded up or down to the nearest whole number.

ceil — Rounds a number to next integer

Increments a decimal value to the next highest integer. 

ceil(decimal)

This formula will show the ceiling of the length for the From To interval. If the length is 4.2, it will display as 5 in the calculated column.

floor — Rounds a number to previous integer

Decrements a decimal value to the next lowest integer.

floor(decimal)

This formula will show the floor of the length for the From To interval. If the length is 4.2, it will display as 4 in the calculated column.

abs — Absolute

Returns the absolute value of a given expression.

abs(expression)

Here the Dip value in the survey table will display as it's absolute value; that is, without the negative symbol preceding it.

pow — Power

Returns the given expression raised to the specified power.

pow(base,exponent)

Here the pow function is used in the formula to calculate the volume of each core interval.

log — Logarithm

Returns the natural logarithm of the expression, when no base is specified. When the optional base is provided, returns the logarithm of the expression to the specified base.

log(expression) or

log(expression,base)

Calculate the natural log of the interval length.

The log of the interval length is calculated to the base of 2.

sqrt — Square Root

Returns the square root of a given expression

sqrt(expression)

The square root is calculated on the Volume M2 variable.

Row Aggregate Functions

These produce a single output value across a set of input values.

avg — Average

Averages the set of any given amount of specified values.

avg(numeric1,numeric2 ,numeric3)

Calculating the average across 3 different pH Reading columns.

median — Median

Returns the median value, number in the middle, of any given amount of specified values.

median(numeric1,numeric2 ,numeric3)

The middle number of the pH Readings will be shown in the calculated column.

min — Minimum

Returns the minimum value of any given amount of specified values.

min(numeric1,numeric2 ,numeric3)

The minimum alteration intensity value will be given in the calculated column.

max — Maximum

Returns the maximum value of any given amount of specified values.

max(numeric1,numeric2 ,numeric3)

The maximum alteration intensity value will be given in the calculated column.

sum — Sum

Sums the set of any given amount of specified values.

sum(numeric1,numeric2 ,numeric3)

The sum of the intervals will be calculated.

Conditional Functions

ifElse — Conditional Test

The ifElse conditional function is the natural function to use when a condition is being tested. It evaluates a test condition and if true, the first result value provided will be returned. If the test condition is false, the second result value provided will be returned.

ifElse(test-condition,true-result,false-result)

The test condition is a search of the Description column of a Mineralisation table. If the column contains the word braccia the true result is Yes and if false the result is No.

In the example shown here, we have chosen to have a text value returned of Valid or Not Valid based on the Strength Value of 0-5 selected by a logger in a structural table. For this column we have selected a text type column as the return value is text and not numeric.

ifThen — Conditional Switch

The ifThen conditional function is used when a number of conditions need to be tested. In this sense, it is more like a switch or case statement than an if conditional, but it is not limited to testing a single conditional input. Provide a list of comma-separated conditional expression pairings inside square brackets, with each pairing comprising a test condition and a result value to return if the test condition is true. The ifThen statement will return the result value of the first conditional expression pairing where the test condition is true. There is no specific limit for the number of conditional expression pairings that can be listed.

ifThen(

[test-condition1,result1],

[test-condition2,result2],

[test-condition3,result3])

For each of Lithology.Values Qal, Tba, Ktc and Mig, a different result value is specified.

in — Test if in Set

The in conditional function returns true if the result of the expression is found in the set of test items. There can be any number of test items not just three.

in(expression,test-item1,test-item2,test-item3)

If the Lithology.Value field contains one of the listed values then the function returns Found; otherwise it returns Missing.

Trigonometric Functions

sin — Sine

Calculates the trigonometric sine of an angle, expecting the angle provided to be in radians (not degrees).

sin(angle)

sin will be calculated as the sine of the Core Angle value entered by the logger.

cos — Cosine

Calculates the trigonometric cosine of an angle, expecting the angle provided to be in radians (not degrees).

cos(angle)

cos will be calculated as the cosine of the Core Angle value entered by the logger.

tan — Tangent

The trigonometric tangent function, expecting the angle provided to be in radians (not degrees).

tan(angle)

tan will be calculated as the tangent of the Core Angle value entered by the logger.

asin — Arc Sine

The trigonometric arcsine (or inverse sine) function. Calculates the arcsine of an angle returning a result in radians (not degrees).

asin(angle)

asin will be calculated as the arcsine of the Core Angle entered by the logger.

acos — Arc Cosine

The trigonometric arccosine (or inverse cosine) function. Calculates the arccosine of an angle returning a result in radians (not degrees).

acos(angle)

acos will be calculated as the arccosine of the Core Angle entered by the logger.

atan — Arc Tangent

The trigonometric arctangent (or inverse tangent) function. Calculates the arctangent of an angle, in radians.

tan(angle)

atan will be calculated as the arctangent of the Core Angle entered by the logger.

atan2 — Arc Tangent (y/x)

The two-argument trigonometric arctangent (or inverse tangent) function, taking y and x arguments instead of a distance as an input, where (x,y) are the coordinates of a point; the result is the angle between the positive x axis and the vector to (x,y). atan2(y,x) is the same as atan(y/x). The result is an angle in radians.

atan2(y,x)

This calculation is using the atan2 function to determine the angle between the y co-ordinate and the x co-ordinate.

Angle Functions

degrees — Degrees

Calculates the angle from radians to degrees.

degrees(angle)

The Core Angle that has been entered in radians will be displayed in degrees.

radians — Radians

Calculates the angle from degrees to radians

radians(angle)

The Core Angle that has been entered in degrees will be displayed in radians.

Table Functions

previous — Previous Row

Use this function to reference data for a column from the previous row. You can also, optionally, specify the initial value that should be used when the function is applied to the first row, i.e there is no previous row value to obtain. If no initial value is provided, then 0 will be used instead.

previous (column.name) or

previous(column.name,initial-value)

Here the Beta Angle from the row above will be displayed in the calculated column.

Here the data from the Azimuth column in the previous row is referenced and 12.5 added, but for the first row 0 is assumed as the working value because the previous row does not exist. The result looks like this:

You cannot use the previous function for a column that is also used elsewhere in a function. For instance, the following function fails with a 'An argument of the function has an inappropriate type' error:

To achieve the goal of referencing both the current and previous values for a column in the same function, you will need to create a new calculated column that contains the value of the previous row and reference that calculated column instead:

Here the new column PrevAz is created referencing the previous Azimuth

Then the PrevAz column is used in the formula to check for Azimuth deviation

Date Functions

You can include calculated columns when creating a new column. Below are the functions that can be used:

today — Today

Use this function to add the current local date stamp. This function works best in date and text column types.

today()

Today's date will appear automatically when a new row is created. This function will not run a recalculation, it only works when a new row of data has been created, it does not run on old data

dateAdd - Date Addition

Use this function add a specified number of days or weeks to the input date. This function works best in date and text column types.

dateAdd({date-value},add-value,unit)

1 day will be added to the Drill Date in the calculated column.

dateDiff- Date Difference

Use this function to return the difference in either days or weeks between the specified dates. The latter date should be listed first, before the initial date, to enable accurate subtraction. This function works best in numeric and text column types.

dateDiff(latterDateValue-initialDateValue,unit)

This formula calculates the amount of weeks between the Drill Date, when drilling started and Todays Date.

year - Year

This function takes a date and displays only the year. Only text and numeric column types will work with this function.

year(dateValue)

While the Drill Date column displays the full date, the year function leaves just the year.

month - Month

This function takes a date and displays only the month. Only text and numeric column types will work with this function.

month(dateValue)

While the Drill Date column displays the full date, the month function leaves just the month.

day - Day

This function takes a date and displays only the day. Only text and numeric column types will work with this function.

day(dateValue)

While the Drill Date column displays the full date, the day function leaves just the day.

date — Date

Use this function to format the input string expression that is in ISO 8601 format as a date. Only date and text column types work with this function.

date(expression)

Here the string 2015-04-18 will be formatted as a date.

dateStr - Date String

This function formats the input date as a text string controlled by an explicit format string. Choose either text or numeric column types, depending on if the result is text or numeric. The date column type does not work with this function.

dateStr(dateValue,expression)

Explicit Format String Expression Will Display As Example
%a Weekday, short version Wed
%A Weekday, full version Wednesday
%w Weekday as a number 0-6, 0 is Sunday 3
%d Day of month 01-31 27
%b Month name, short version Dec
%B Month name, full version December
%m Month as a number 01-12 12
%y Year, short version, without century 24
%Y Year, full version 2024
%Z Timezone AEST
%j Day number of year 001-366 365
%U Week number of year, Sunday as the first day of week, 00-53 52
%W Week number of year, Monday as the first day of week, 00-53 52
%C Century 21
%x Local version of date 31/12/2024

Here the string expression used will format the date to display as a short version of the year.

Constants

E — e Constant

The constant e, the base of the natural logarithm, is an existing item you do not need to define yourself. It is approximately equal to 2.71828.

Euler's base is multiplied with the cosine of the Core Angle.

PI — pi Constant

The mathematical constant pi, the ratio of a circle's circumference to its diameter, is an existing item you do not need to define yourself. It is approximately equal to 3.14159.

Here PI is used to calculate the volume of core for a specific interval.

Inputs

There are a range of options for inputs available for you to use in building your formula. These are suggested as you type your formula, or you can find them in the side panel of the formula editor. The inputs will mostly vary depending on which table you are in. However, some inputs will always be available; these are described below.

Entry Inputs

Project — Project Name

References the active project name

{Project.Name}

The project name will be displayed in each row.

Activity — Activity Name

References the active activity name

{Activity.Name}

The activity name will be displayed in each row.

Hole/Point — Drill Hole or Point Name

References the active hole/point name

{Hole/Point.Number}

The hole or point number will be displayed in each row.

Collection Inputs

Projects — Projects Name

References the project name you have specified.

{Projects.Name}

Here, the calculated column is in a table that is part of an activity in the Foxfire project. By using this collection input, each row will display the project name LEDA, regardless of what project the table is used in.

Activities — Activities Name

References the activity name you have specified.

{Activities.Name}

Here, the calculated column is in a table that is part of an activity called RC Logging. By using this collection input, each row will display the activity name Core Logging, regardless of what activity the table is used in.

Header Field Inputs

Header Fields

References the header field you have specified.

{Header.FieldName}

This formula is used to calculate what percentage the interval length is of the total target depth. So the Target depth field from the Collar Header is referenced.

External Input Columns

External Columns

Reference multiple columns, across tables. To reference an external column from a table other than the one you are editing, select External Input Column from the inputs and complete the parameters for that column in the window that appears.

{ExternalInputColumnName}

This formula populates the Recovery% in the destination table using data from the RQD table.

See Obtain Recovery % From RQD to Samples Table When Intervals are Mismatched in the Formula Gallery for an example of how to use this function.

Ranked Columns Inputs

Sample Result Ranked Columns

Reference the sample result in a samples table where a ranked column has been used.

Ranked column inputs only applies to calculated columns on sample tables. Ranked columns are a numeric input.

{RankedColumnName,Unit(ActivityName)}

In this example the ranked column, CU Best, is used to show the percentage of CU in a column in the Samples table in the Core Logging activity. This is useful when you want specific sample results to display in each row of the sample table, such as here:

If the sample results have already been released, the formula will not run. To get the formula to run on a samples table you will need to unrelease the results, then release them again.