Calculated expression functions
Use calculated expression functions to manipulate strings, dates, lists, geometry values, resources and data types in calculated custom property definitions.
Functions can be selected from the Expression Editor.
Some functions have aliases. The canonical function name and its aliases return the same result.
The following tables list commonly used functions. Additional functions and operators are available in the Functions Library, including aggregation, logical/conditional, geometry/spatial, resource, shift, product, and measurement unit converters.
Arithmetic operators
| Function | Description |
|---|---|
| + (Add) | Adds numeric values. In String data type, + concatenates or joins the strings instead. |
| - (Subtract) | Subtracts one value from another. |
| * (Multiply) | Multiplies numeric values. |
| / (Divide) | Divides one value by another. |
| % (Modulus) | Returns the remainder of a division. |
| & (And) | Logical AND operation. |
| | (Or) | Logical OR operation. |
| ! (Not) | Logical NOT operation. |
Aggregation functions
| Function | Description |
|---|---|
| SUM(values...) | Sums values from a list. |
| AVERAGE(values...) | Returns the average value. |
| COUNT(values...) | Counts values in a list. |
| MIN(value1, value2), MAX(value1, value2) | Returns the smaller or larger of two values. |
| MINOF(values...), MAXOF(values...) | Returns the minimum or maximum from supplied values. |
| MEDIAN(values...) | Returns the median of numeric values. |
| MODE(values...) | Returns the most frequent value. |
| MODES(values...) | Returns all values tied for the mode. |
| DISTINCT(values...) | Returns unique values from a list. |
Mathematical functions
| Function | Description |
|---|---|
| ABS(number) | Returns the absolute value. |
| ACOS(number), ASIN(number), ATAN(number) | Inverse trigonometric functions. |
| COS(number), SIN(number), TAN(number) | Trigonometric functions. |
| CEILING(number), FLOOR(number), ROUND(number[, decimals]), TRUNCATE(number) | Numeric rounding and truncation. |
| EXP(number) | e raised to the specified power. |
| LOG(number, base), LOG10(number) | Logarithm functions. |
| POW(number, power) | Raises a number to the specified power. |
| SIGN(number) | Returns sign indicator (-1, 0, 1). |
| SQRT(number) | Returns the square root. |
| IEEEREMAINDER(dividend, divisor) | Returns the IEEE remainder for the division. |
Logical and conditional functions
| Function | Description |
|---|---|
| IF(condition, trueValue, falseValue) | Returns a value based on a condition. |
| IN(value, values...) | Returns whether value is in supplied set. |
| ISNULL(value) | Checks whether value or list result is null. |
| COALESCE(values...) | Returns first non-null, non-empty value. |
| NULLIF(value1, value2) | Returns null when values are equal. |
| BETWEEN(value, min, max) | Checks whether a value is within an inclusive range. |
| CASEWHEN(cond1, val1, ..., elseVal) | Returns first matching condition value. |
| ANY(values...), ALL(values...) | Evaluates whether any or all supplied values are true. |
| POINTEQUALS(p1, p2[, tolerance]) | Compares two points. |
| WITHINRADIUS(point, center, radius) | Checks whether point is within XY radius. |
String functions
| Function | Description |
|---|---|
| TRIM(string text) | Removes leading and trailing spaces from a string. |
| TRIMLEFT(string text)
Aliases: TRIM_LEFT, LTRIM |
Removes leading spaces from a string. |
| TRIMRIGHT(string text)
Aliases: TRIM_RIGHT, RTRIM |
Removes trailing spaces from a string. |
| REPLACE(string text, string find, string replacement, [bool caseSensitive = false]) | Replaces matching text in a string. |
| INDEXOF(string text, string find, [bool caseSensitive = false], [int start = 0])
Alias: INSTR |
Returns the index of the first matching value in a string. |
| LASTINDEXOF(string text, string find, [bool caseSensitive = false], [int start = text.Length-1])
Alias: INSTRREV |
Returns the index of the last matching value in a string. |
| LEFT(string text, int length) | Returns characters from the start of a string. |
| RIGHT(string text, int length) | Returns characters from the end of a string. |
| SUBSTRING(string text, int start, int length)
Alias: MID |
Returns part of a string. |
| SPLIT(string text, string delimiter) | Returns a list of strings split on the supplied delimiter. |
| REGEX_SPLIT(string text, string pattern) | Returns a list of non-matching strings split on the matched regular expression instances. |
| REGEX_MATCH(string text, string pattern) | Returns a list of regular expression matches. |
| REGEX_ALL(string text, string pattern) | Returns a list of all regular expression matches and non-matches, interleaved in source order. |
| CONTAINS(string text, string find, [bool caseSensitive = false]) | Checks whether text contains a value. |
| STARTSWITH(string text, string find, [bool caseSensitive = false]) | Checks whether text starts with a value. |
| ENDSWITH(string text, string find, [bool caseSensitive = false]) | Checks whether text ends with a value. |
| TOUPPER(string text), TOLOWER(string text) | Converts text to upper or lower case. |
| LENGTH(string text) | Returns text character count. |
| REGEXREPLACE(string text, string pattern, string replacement) | Replaces text using regular expression pattern. |
Date and time functions
| Function | Description |
|---|---|
| DATESERIAL(int y, int m, int d, [int h=0], [int n=0], [int s=0], [int ms=0], [int mcs=0]) | Returns a date-time value. |
| DATEADD(DateTime date, string part, int number) | Adds a number of years, months, days, hours, minutes or seconds to a date-time value. |
| DATEDIFF(DateTime date1, DateTime date2) | Returns the difference between two date-time values as a decimal number of days. |
| YEAR(DateTime date), MONTH(DateTime date), DAY(DateTime date) | Returns the year, month or day from a date-time value. |
| WEEKOFYEAR(DateTime date, [int firstWeekOfYear=1])
Alias: WEEK_OF_YEAR |
Returns the week of the year. Values for firstWeekOfYear are 0 for system setting, 1 for the week in which January 1 occurs, 2 for the first week with at least four days in the new year, or 3 for the first full week. |
| WEEKDAY(DateTime date, [int firstDayOfWeek=1])
Aliases: DAY_OF_WEEK, WEEK_DAY |
Returns the day of the week. Values for firstDayOfWeek are 0 for system setting, 1 for Sunday, 2 for Monday, 3 for Tuesday, 4 for Wednesday, 5 for Thursday, 6 for Friday, or 7 for Saturday. |
| HOUR(DateTime date), MINUTE(DateTime date), SECOND(DateTime date) | Returns the hour, minute or second from a date-time value. |
| AMPM(DateTime date) | Returns 0 for AM or 1 for PM. |
| MILLISECOND(DateTime date), MICROSECOND(DateTime date), NANOSECOND(DateTime date) | Returns the millisecond, microsecond or nanosecond from a date-time value. |
| CURRENTDATETIME(), CURRENTDATETIMEUTC(), NOWLOCAL(), TODAY() | Returns the current local or UTC date-time value. |
| STARTOFDAY(date), STARTOFMONTH(date), STARTOFYEAR(date) | Returns the start of the day, month or year. |
| ENDOFDAY(date), ENDOFMONTH(date), ENDOFYEAR(date) | Returns the end of the day, month or year. |
| ADDBUSINESSDAYS(date, days) | Adds or subtracts business days excluding weekends. |
Geometry and spatial functions
| Function | Description |
|---|---|
| POINT(double x, double y, double z) | Returns a point geometry value. |
| DISTANCE(point1, point2) | Returns 2D XY distance between two points. |
| DIST3D(point1, point2), DIST_3D(...) | Returns 3D distance between two points. |
| BEARING(point1, point2), AZIMUTH(point1, point2) | Returns XY bearing or azimuth in degrees. |
| MIDPOINT(point1, point2) | Returns midpoint between two points. |
| TRANSLATE(point, dx, dy[, dz]) | Offsets point by delta values. |
| ROTATE2D(point, origin, degrees) | Rotates point in XY plane. |
| SCALEFROM(point, origin, factor) | Scales point from origin in XY plane. |
| X(point), Y(point), Z(point) | Returns point coordinate components. |
| AREA(points...), POLYAREA(points...) | Returns polygon area from points. |
| POLYPERIM(points...) | Returns polygon perimeter from points. |
| ANGLEDEG(p1,p2,p3,p4) | Returns angle between two segments in degrees. |
| MASSCALC(volume, density) | Returns mass from volume and density. |
| CUBEVOLUME, CUBEAREA, BOXVOLUME, BOXAREA, CYLINDERVOLUME, CYLINDERAREA, SPHEREVOLUME, SPHEREAREA, CONEVOLUME, CONEAREA, PYRAMIDVOLUME, PYRAMIDAREA, TORUSVOLUME, TORUSAREA | Shape volume and area helper functions. |
List functions
| Function | Description |
|---|---|
| EXTRACT(int fromIndex, int toIndex, params object[] values) | Returns a list containing values in the specified index range. |
| TAKE(int skip, int take, params object[] values) | Skips the specified number of values and returns the specified number of following values. |
| PAGE(int pageNumber, int pageLength, params object[] values) | Returns the values for the specified page number and page length. |
| SORT([string mode='AUTO'], [bool descending=false], params object[] values) | Sorts values and returns the sorted list. Use descending to sort in descending order. |
| FOREACH([int fromIndex], [int toIndex], string template, [string delimiter=','], params object[] values) | Generates a string from list values using the specified template and delimiter. |
| FIRST(values...), LAST(values...) | Returns first or last value in a list. |
| AT(index, values...), INDEX(index, values...) | Returns value at zero-based index. |
Resource functions
Resource functions return information about operator, drill machine and truck resources. If a required input is blank, the function returns a blank value. If the resource identifier is valid but the resource is not found, the function returns NA.
| Function | Description |
|---|---|
| ResourceName(guid, type) | Returns the display name for the specified resource and resource type. |
| ResourceTypeID(guid), ResourceTypeName(guid) | Returns the resource type identifier or name. |
| ResourceTruckTypeID(guid), ResourceTruckTypeName(guid) | Returns the truck subtype identifier or name for a truck resource. |
| ResourceOperatorName(guid) | Returns the display name for an operator resource. |
| ResourceTruckName(guid) | Returns the display name for a truck resource. |
| ResourceDrillMachineName(guid)
Alias: ResourceDrillName |
Returns the display name for a drill machine resource. |
Other functions
Functions that return information about products and shifts. If a required input is blank, the function returns a blank value. If the item identifier is valid but the item is not found, the function returns NA.
| Function | Description |
|---|---|
| SHIFTNAME(guid) | Returns shift name for the supplied shift GUID. |
| SHIFTSTARTTIME(guid) | Returns shift start time for the supplied shift GUID. |
| SHIFTENDTIME(guid) | Returns shift end time for the supplied shift GUID. |
| PRODUCTNAME(guid) | Returns the product name for the supplied product GUID. |
| PRODUCTTYPEID(guid) | Returns numeric product type value for supplied GUID. |
| PRODUCTTYPENAME(guid) | Returns product type name for supplied GUID. |
| PRODUCTINFO(fieldName, guid) | Returns an approved product field value by field name and product GUID. |
Data type conversion functions
| Function | Description |
|---|---|
| BOOLEAN(object value)
Aliases: BOOL, BIT |
Converts a value to a Boolean value. Zero is false and any other numeric value is true. |
| BYTE(object value), SHORT(object value), INTEGER(object value), LONG(object value) Alias for INTEGER: INT |
Converts a value to the selected integer type. |
| SINGLE(object value), DOUBLE(object value), DECIMAL(object value) Alias for SINGLE: FLOAT Alias for DOUBLE: DBL Alias for DECIMAL: DEC |
Converts a value to the selected decimal type. |
| CONVERTTONUMBER(object value) | Converts a string or object value to a Double. |
Measurement Unit Converters
Measurement unit converters are available in the Functions Library under Measurement Unit Converters.
Distance
| Converter Function | Description |
|---|---|
| Convert_FT_to_IN(value) | Convert feet to inches. |
| Convert_MM_to_IN(value) | Convert millimetres to inches. |
| Convert_CM_to_MM(value) | Convert centimetres to millimetres. |
| Convert_MM_to_M(value) | Convert millimetres to metres. |
| Convert_CM_to_M(value) | Convert centimetres to metres. |
| Convert_CM_to_CM(value) | Convert centimetres to centimetres. |
| Convert_MM_to_CM(value) | Convert millimetres to centimetres. |
| Convert_M_to_CM(value) | Convert metres to centimetres. |
| Convert_M_to_KM(value) | Convert metres to kilometres. |
| Convert_M_to_MM(value) | Convert metres to millimetres. |
| Convert_KM_to_M(value) | Convert kilometres to metres. |
| Convert_FT_to_M(value) | Convert feet to metres. |
| Convert_IN_to_FT(value) | Convert inches to feet. |
| Convert_YD_to_FT(value) | Convert yards to feet. |
| Convert_ML_to_FT(value) | Convert miles to feet. |
| Convert_M_to_FT(value) | Convert metres to feet. |
| Convert_KM_to_FT(value) | Convert kilometres to feet. |
| Convert_IN_to_M(value) | Convert inches to metres. |
| Convert_IN_to_MM(value) | Convert inches to millimetres. |
| Convert_YD_to_M(value) | Convert yards to metres. |
| Convert_ML_to_M(value) | Convert miles to metres. |
| Convert_KM_to_CM(value) | Convert kilometres to centimetres. |
| Convert_CM_to_IN(value) | Convert centimetres to inches. |
| Convert_MM_to_FT(value) | Convert millimetres to feet. |
| Convert_CM_to_FT(value) | Convert centimetres to feet. |
| Convert_FT_to_YD(value) | Convert feet to yards. |
| Convert_FT_to_ML(value) | Convert feet to miles. |
Time
| Converter Function | Description |
|---|---|
| Convert_Hours_to_Seconds(value) | Convert hours to seconds. |
| Convert_Hours_to_Minutes(value) | Convert hours to minutes. |
| Convert_S_to_MS(value) | Convert seconds to milliseconds. |
| Convert_MS_to_MCS(value) | Convert milliseconds to microseconds. |
| Convert_MS_to_S(value) | Convert milliseconds to seconds. |
| Convert_Min_to_S(value) | Convert minutes to seconds. |
| Convert_S_to_Min(value) | Convert seconds to minutes. |
| Convert_MCS_to_MS(value) | Convert microseconds to milliseconds. |
| Convert_Seconds_to_Hours(value) | Convert seconds to hours. |
Volume
| Converter Function | Description |
|---|---|
| Convert_M3_to_L(value) | Convert cubic metres to litres. |
| Convert_CM3_to_L(value) | Convert cubic centimetres to litres. |
| Convert_L_to_MM3(value) | Convert litres to cubic millimetres. |
| Convert_M3_to_Ton(value) | Convert cubic metres to tonnes. |
| Convert_CM3_to_M3(value) | Convert cubic centimetres to cubic metres. |
| Convert_M3_to_CM3(value) | Convert cubic metres to cubic centimetres. |
| Convert_MM3_to_M3(value) | Convert cubic millimetres to cubic metres. |
| Convert_M3_to_MM3(value) | Convert cubic metres to cubic millimetres. |
| Convert_YD3_to_FT3(value) | Convert cubic yards to cubic feet. |
| Convert_YD3_to_IN3(value) | Convert cubic yards to cubic inches. |
| Convert_L_to_M3(value) | Convert litres to cubic metres. |
| Convert_L_to_CM3(value) | Convert litres to cubic centimetres. |
| Convert_MM3_to_L(value) | Convert cubic millimetres to litres. |
| Convert_FT3_to_L(value) | Convert cubic feet to litres. |
| Convert_L_to_FT3(value) | Convert litres to cubic feet. |
| Convert_IN3_to_L(value) | Convert cubic inches to litres. |
| Convert_L_to_IN3(value) | Convert litres to cubic inches. |
| Convert_YD3_to_L(value) | Convert cubic yards to litres. |
| Convert_L_to_YD3(value) | Convert litres to cubic yards. |
| Convert_YD3_to_M3(value) | Convert cubic yards to cubic metres. |
| Convert_M3_to_YD3(value) | Convert cubic metres to cubic yards. |
| Convert_IN3_to_YD3(value) | Convert cubic inches to cubic yards. |
| Convert_FT3_to_YD3(value) | Convert cubic feet to cubic yards. |
| Convert_FT3_to_M3(value) | Convert cubic feet to cubic metres. |
| Convert_M3_to_FT3(value) | Convert cubic metres to cubic feet. |
| Convert_IN3_to_M3(value) | Convert cubic inches to cubic metres. |
| Convert_M3_to_IN3(value) | Convert cubic metres to cubic inches. |
Mass
| Converter Function | Description |
|---|---|
| Convert_G_to_KG(value) | Convert grams to kilograms. |
| Convert_KG_to_G(value) | Convert kilograms to grams. |
| Convert_LB_to_KG(value) | Convert pounds to kilograms. |
| Convert_T_to_KG(value) | Convert tonnes to kilograms. |
| Convert_OZ_to_KG(value) | Convert ounces to kilograms. |
| Convert_KG_to_T(value) | Convert kilograms to tonnes. |
| Convert_KG_to_LB(value) | Convert kilograms to pounds. |
| Convert_KG_to_OZ(value) | Convert kilograms to ounces. |
| Convert_OZ_to_G(value) | Convert ounces to grams. |
| Convert_G_to_OZ(value) | Convert grams to ounces. |
Mass per volume
| Converter Function | Description |
|---|---|
| Convert_GCC_to_LBFT3(value) | Convert g/cc to lb/ft³. |
| Convert_GCC_to_KGM3(value) | Convert g/cc to kg/m³. |
