Functions

The general form of calling a function is:

FUNCTION (<param1>, <param2>, ... <paramN>)

Available functions are grouped as follows:

Mathematics

Function Description
EXP(x) Returns the ANTILOG BASE E of x.
EXP10(x) Returns the ANTILOG BASE 10 of x.
LN(x) Returns the NATURAL LOGARITHM of x
LOG10(x) Returns the BASE-10 LOGARITHM of x.
SQRT(x) Calculates the square root of x. If x is not defined, returns an undefined value.
MIN(x1, x2, ... xn) Returns the minimal defined value of all arguments. If no argument is a defined value, returns an undefined value.
MAX(x1, x2, ... xn) Returns the maximal defined value of all arguments. If no argument is a defined value, returns an undefined value.
CUTHIGHS(x,y) Returns y if x is greater than y. Otherwise returns x.
ABS(x) Returns the absolute value of x. If x is not defined, returns an undefined value.
INT(x) Returns the integer part of the number. If x is not defined, returns an undefined value.
ROUND(x, n) Rounds the value of x to n decimal places. If either x or n is undefined, returns an undefined value.
ROUNDSIG(x, n) Rounds the value of x to a given number of (n) significant figures.

Trig

For the trigonometric functions, input values are expected to be in degrees and decimals of a degree (DDD.DDDD) as are the results.

Function Description
SIN(x) Returns the SINE of x.
COS(x) Returns the COSINE of x.
TAN(x) Returns the TANGENT of x.
ASIN(x) Returns the ARCSINE of x.
ACOS(x) Returns the ARCCOSINE of x.
ATAN(x) Returns the ARCTANGENT of x.
ATAN2(y, x) Returns the ARCTANGENT of y/x. (if x equals 0, ATAN2 returns 180 if y is positive, -180. if y is negative, or 0 if y is 0.) If either x or y is undefined, the return value is undefined.
ADDANGLE(x1, x2, ... xn) Adds up all passed parameters, making sure the result is in the range [0, 360).
SUBANGLE(x1, x2, ... xn) Subtracts all passed parameters, making sure the result is in the range [0, 360).
DMS2DEG(x) Converts an angle in DMS format (x) to decimal degrees.
DEG2DMS(x) Converts an angle in decimal degrees format (x) to DMS format.

Number Formatting

Function Description
FIXED(x, n) Formats the value of x to a given number of (n) decimal places and returns it as text.
NUMFORMAT_LOCALE(x), NUMFORMAT_LOCALE(x, n) Returns x as text to n decimal places, formatted using the current locale settings.
NUMFORMAT_WORDS(x) Returns x as spelled out words. For example: 135 becomes "one hundred and thirty-four".
NUMFORMAT_ORDINAL(x) Returns x as text of an ordinal number. For example: 134 becomes "134th".

Text

Function Description
LEFT(text, n) Returns the leftmost <n> characters of the text string.
RIGHT Returns the rightmost <n> characters of the text string.
SUBSTR(text, m), SUBSTR(text, m, n) Returns n (or all) characters starting from index m (0 based).
TRIM(text) Returns the text with white space stripped from the start and end of the text string.
REVERSE(text) Returns the text in reversed character order.
REPLACE(text, findStr, replaceStr) Returns the text with all occurrences of findStr replaced with replaceStr.
UPPER(text) Returns the text in UPPER CASE.
LOWER(text) Returns the text in lower case.
TITLE(text) Returns the text in Title Case.
LENGTH(text) Returns the number of characters in the text string.
MATCH(text, match) Returns true if the text matches the match string. Standard Micromine wildcards can be used in the match string. See: Wildcards
MATCH_REGEX(text, regex) Returns true if the text matches the regular expression. Uses Perl regular expression syntax. See: Regular Expressions
STARTSWITH(text, str) Returns true if the text starts with str.
ENDSWITH(text, str) Returns true if the text ends with str.
INDEXOF(text, str), INDEXOF(text, str, m) Returns character position (0 based) of str within the text, starting at the beginning, or at index m.

Date

Function Description
TODAY() Returns the current date/time.
TODATE(y,m,d), TODATE(y,m,d,h,n,s) Creates a date given a year, month, and day. Optionally include time with hours, minutes, and seconds.
TODATE(str) Parses str as a date. Returns NIL if failed.
TODATE(str,format) Parses str as a date using a format string. Returns NIL if failed. See Date format conversion below.
DATE(date) Returns a locale formatted date string.
TIME(date) Returns a local formatted time string.
DATETIME(date) Returns a locale formatted date and time string.
FORMATDATE(date, format) Returns a formatted date-time string as defined by the format string, See Date formats below.
WEEKDAY(date) Returns the number of days in the week that the given date falls on. (0=Sunday, 6=Saturday)
WEEKDAYNAME(date) Returns the name of the days in the week that the given date falls on. e.g. "Monday"
MONTH(date) Returns the number of the month that the given date falls in. (1=January, 12=December)
MONTHNAME(date) Returns the name of the month that the given date falls in. e.g. "June"
YEAR(date) Returns the year that the given date falls in. e.g. "1952"
YEARDECIMAL(date) Returns the decimal year that describes the date, where the fractional part is how far through the year it is. e.g. "1952.23"
ADDDAYS(date,n) Adds a number of days to the given date.
ADDMONTHS(date,n) Adds a number of months to the given date.
ADDYEARS(date,n) Adds a number of years to the given date.
DAYSBETWEEN(date1,date2) Returns the number of days between the two dates.
MONTHSBETWEEN(date1,date2) Returns the number of months between the two dates.
YEARSBETWEEN(date1,date2) Returns the number of years between the two dates.

Date format conversion

The PARSEDATE_ADV function parses a string as a date using a format string, for example:

PARSEDATE_ADV("19/08/02","YYY/MMM/dd")

The function returns a date of the specified format, or returns BLANK in the event of failure. To convert from old-style date formats, use the expressions shown in the following table:

Old-style date format Expression
YYYYMMDD =PARSEDATE_ADV([DATE], "yyyyMMdd")
DD/MM/YYYY =PARSEDATE_ADV([DATE],"dd/MM/yyyy")
MM/DD/YYYY =PARSEDATE_ADV([DATE],"MM/dd/yyyy")
DD MM YYYY =PARSEDATE_ADV([DATE],"dd MM yyyy")

Date formats

The FORMATDATE function returns a formatted date-time string as defined by a date format pattern. For example:

FORMATDATE(TODAY(),"dd/MM/YY")

Date patterns are character strings that comprise a combination of pattern fields and literal text. See: Date Formats

Utility

Function Description
IN(y, x1, ... xn) The IN function takes at least two parameters, the first one is the value that gets compared. Other parameters are the values to compare to. If a match is found, the function returns logical TRUE. For example:
RANDOM() Generates a random value. If there are no parameters the generated number is in the range [0,1]. One parameter defines a maximum and the generated number is in the range [0, max]. Two parameters define a minimum and a maximum and the generated number is in the range [min, max].
RGB(r,g,b) Returns an RGB value from individual red, green, and blue values specified as integers between 0 and 255.
  This function is essential for assigning colours directly into Micromine DATA files, especially when XYZ+RGB point clouds are involved.
DEFINED(x) x – variable. Check if it is defined (x exists and was assigned a value which is not undefined (NIL or BLANK).
EVAL(text) Evaluates text as an expression and returns the result. If the expression cannot be evaluated, a BLANK value is returned.

Units

In the Functions pane of the Expression Editor, the following categories of units are provided:

Expand a category to select the units you want to apply to the numeric values in your expression:

Length

Area

Volume

Mass

Proportions

Energy

Density

Grade

Currency

Scalar

QUANTITY

Function Description
Length See: U
Area  
Volume  
Mass  
Proportions  
Energy See: Energy
Density  
Grade  
Currency ($) The currency unit “$” provides a generic representation of 'money'.
Scalar (_1)

An "abstract scalar", specified as "_1", which can be used as a general-purpose place holder for currency values which do not require special conversions. For example:

_1/kg

specifies an amount per kilogram.
QUANTITY(value) Strips unit from a measured value and returns just the quantity part of it.

Tips

  1. Be aware of the type conversion in your expression. Remember that apart from a few exceptions, the type of the result is the type of the leftmost operand. Note therefore, that “5" + 5 equals “55" and not 10. Note also that if any operand is undefined the result of the expression will also be undefined.
  2. Remember that the variable type is the type of the corresponding file field.
  3. Function and variable names referred to in the expression are matched only when they are actually needed to produce a value. Use the DEFINED function to ensure that a variable exists and has a value before referring to it. 2 + MCAF is unsafe, since MCAF might not exist or might not have a value.

    4. Use 2 + if DEFINED (MCAF) then MCAF else <some_defined_value> fi instead.

  1. Use parentheses to ensure that the computation order is correct.
  2. Remember that ALL parts of the 'if' operator are compulsory. Don't forget the 'fi' to mark the end of your conditional operator.

Operators

Unit Conversions

Expression Editor