General Purpose Functions


This article lists General Purpose Functions that can be used in OpenGround expressions.

Some functions may only be supported in certain Expression Engines or scenarios. Please see the documentation for each function below for more information.

Please see our Functions Guide for additional information about other functions.

Table of Contents

abs()

Returns the absolute value of a number.

abs(-1)

Acos()

Returns the arccosine of a number. The returned value is the angle in radians.

Acos(1)

and

Returns the truth of applying the logical AND operator on two Boolean arguments.

if('a'='a' and 'b'='b','Yes','No')

Asin()

Returns the arcsine of a number. The returned value is the angle in radians.

Asin(0)

Atan()

Returns the arctan of a number. The returned value is the angle in radians.

Atan(0)

Ceiling()

Returns the smallest integer greater than or equal to the specified number.

Ceiling(1.5)

chr()

Returns the character for an ASCII value. For example 97 returns 'a'.

chr(97)

concatenate()

Concatenates (joins) text strings together. Any number of arguments can be specified when multiple strings need to be concatenated.

concatenate(value1, value2, value3,...)

concatenatewith()

Concatenates (joins) text strings together (can be any kind of value) with the specified separator between each value. If a value is blank/empty, the subsequent separator is not inserted.

concatenatewith(separator, value1, value2, value3,...)

Coalesce()

Coalesce() can currently only be used in Model Calculation expressions. It can't be used in other Expression Engines.

Coalesce() is an alias for FirstData(). (It operates and returns the same results as FirstData(). Coalesce() can just be used as the function name instead.)

Cos()

Returns the cosine of the specified angle. The angle must be in radians.

Cos(0)

exact()

Returns True if two string arguments are identical. Otherwise, False is returned.

exact(string, string)

Exp()

Returns e raised to the specified power.

Exp(0)

ExtractValue()

Extracts the first set of numeric values from a text sting.

ExtractValue(string)

Examples:

ExtractValue(10)

returns 10

ExtractValue('10')

returns 10

ExtractValue('>10')

returns 10

ExtractValue('ABC')

returns nothing

find()

Locates the position of a substring within a string. The search is 0-indexed and a start position for the search can be entered. The search is case-sensitive. Returns -1 if the substring is not found. 

find(substring, string, start position)

Examples:

find('A','ABC',0)

returns 0

find('B','ABC',0)

returns 1

find('Z','ABC',0)

returns -1

find('a','ABC',0)

returns -1

find('A','ABCABC',1)

returns 3

find('B','ABCABC',1)

returns 1

FirstData()

FirstData() can currently only be used in Model Calculation expressions. It can't be used in other Expression Engines.

Accepts a series of arguments and returns the value from the first argument that contains data (that is not null or empty/blank).

All arguments must have the same data type. If a value is of a different data type, it can be cast to the consistent data type using a function like value() or text(). See the example below where this is done with text() to include a numeric value within the function call.

FirstData(value1, value2, value3,...)

Example:

This expression will return the results shown below in the FirstData header:

FirstData(
  TextValue1,
  TextValue2,
  text(NumericValue3)
)

fixed()

Returns a numeric value to a specified number of decimal places. Also allows you to specify if thousand separators should be shown (true/false).

To show thousand separators, a 'Digit Grouping' option and 'Digit Grouping Symbol' must be set in System>Configuration Packs>Pack Options.

If you don't want to change your Pack Options, the text() function can alternatively be used to format/round values and include thousand separators. Please see our Text Formatting Guide for more information.

There is a known issue where if fixed() is used within certain nested Template expressions, and the value to be formatted is blank, that the entire expression may undesirably return nothing. If you encounter this, please use text() instead of fixed() to format numerical values.

fixed() uses 'banker's rounding' (midpoint values are rounded to even).

fixed(value, decimal places, include thousand separators)

Example:

fixed([LocationDetails.FinalDepth],2,false)

Floor()

Returns the largest integer less than or equal to the specified number.

Floor(1.5)

IEEERemainder()

Returns the remainder resulting from the division of a specified number by another specified number.

IEEERemainder(3, 2)

if()

Evaluates the condition and returns a value depending on its result.

if(expression, True value, False value)

in()

Returns true if a value is in a set of values. Returns false if the value is not in the set. The matching is case-sensitive.

in(value to match, value1, value2, value3,...)

isblank()

Determines whether a value is blank/empty or null.

isblank(value)

isnull()

Determines if a value is 'null'. Note that a Header in the database can be blank/empty but not necessarily be 'null'. Often it is more reliable to use isblank() if you are checking for the presence or absence of a value in the database.

isnull(value)

isnumber()

Determines if a value is numeric.

isnumber(value)

left()

Extracts the substring from the beginning of a string with a specific length.

left(string, length)

len()

Determines the length of a string.

len(string)

ListItem()

ListItem() can currently only be used in Model Calculation expressions. It can't be used in other Expression Engines.

Retrieves an item from a delimited list.

ListItem(List,Delimiter,Item)

Examples:

This expression will return the results shown below in the ListItem header:

ListItem(TestList,',',3)

Log()

Returns the logarithm of a specified value to a specified base.

Log(value, base)

Log10()

Returns the base 10 logarithm of a specified number.

Log10(100)

lower()

lower() can currently only be used in Model Calculation expressions. It can't be used in other Expression Engines.

Lowercases all characters in a text string.

Examples:

This expression will return the results shown below:

lower(Remarks)

Max()

Returns the larger of two specified numbers.

Max(value1, value2)

mid()

Extracts a substring at a given position within a string. The position and length cannot exceed the number of characters which the string has.

mid(string, position, length)

Min()

Returns the smaller of two numbers.

Min(value1, value2)

not()

Inverts the Boolean truth of an argument. If the argument is not a Boolean, false is returned.

not(True)

now()

Returns the current UTC date and time.

now()

Examples:

text(now(),'dd/MM/yyyy')
text(now(),'HH:mm')

numberordefault()

Returns a specified value if it does not equal null. If the value is null, a default value is returned instead.

numberordefault(value, default value)

Example:

numberordefault([SPT.NValue],0)

or

Returns the truth of applying the logical OR operator on two Boolean arguments.

if('a'='a' or 'b'='b','Yes','No')

PiValue()

PiValue() can currently only be used in Model Calculation expressions. It can't be used in other Expression Engines.

Returns pi to 28 decimal places. Does not accept any arguments.

PiValue()

proper()

proper() can currently only be used in Model Calculation expressions. It can't be used in other Expression Engines.

Uppercases the first character of each 'word' within a text string and lowercases the remaining characters. A 'word' is defined as any group of letters occurring at the start of the string or after a space or any 'non-letter' character.

Examples:

This expression will return the results shown below:

proper(Remarks)

Pow()

Returns a specified number raised to the specified power.

Pow(value, power)

replace()

Replaces all occurrences of a substring with an alternate value.

replace(string, substring to find, replacement substring)

right()

Extracts the substring from the end of a string with a specific length.

right(string, length)

Round()

Rounds a value to the nearest integer or specified number of decimal places.

Round(3.222, 2)

search()

Locates the position of a substring within a string. The search is 0-indexed and a start position for the search can be entered. The search is case-sensitive. Returns -1 if the substring is not found.  This is an alias for find().

search(substring, string, start position)

Sign()

Returns a value indicating the sign of a number. Returns 1 if a value is positive, -1 if a value is negative, and 0 if the value is 0.

Sign(-10)

Sin()

Returns the sine of the specified angle. The angle must be in radians.

Sin(0)

Sqrt()

Returns the square root of a number.

Sqrt(4)

substitute()

Replaces all occurrences of a substring with an alternate value. This is an alias for replace()

substitute(string, substring to find, replacement substring)

Tan()

Returns the tangent of the specified angle. The angle must be in radians.

Tan(0)

text()

Converts any value to its string representation. An optional argument can be used to specify the format, such as to round numeric values or to specify a date time format.

When used to round a numeric value, text() rounds up for midpoint values.

Please see our Text Formatting Guide for additional information and examples.

text(value)
text(value, format code)

textStyle()

textStyle() can only be used in Template expressions. It can't be used in other Expression Engines.

Allows text to be formatted within an expression. This allows for more than one style to be applied within a text string and also allows finer control than using the Design ribbon to stylize text.

textStyle('My Text','Font Weight','Font Style','Font Color')

Note - All arguments are required.

Examples:

This expression would just bold the text:

textStyle('My Text','700','Normal','000000')

This expression would combine the Sample Type and Reference in bold, italics, and red:

textStyle(ConcatenateWith('-',[SampleInformation.Type],[SampleInformation.SampleReference]),'900','Italic','FF0000')

This expressions would show the text 'Client:' in blue oblique text and then the client name in bold, red, and italics:

textStyle('Client: ','100','Oblique','0000FF')+textStyle([Project.ClientName],'900','Italic','FF0000')

today()

Returns the current UTC date and time. An alias for now().

today()

Examples:

text(today(),'dd/MM/yyyy')
text(today(),'HH:mm')

trim()

Removes leading and trailing whitespace from a text string.

trim(string)

Truncate()

Calculates the integral part of a number.

Truncate(1.7)

upper()

upper() can currently only be used in Model Calculation expressions. It can't be used in other Expression Engines.

Uppercases all characters in a text string.

Examples:

This expression will return the results shown below:

upper(Remarks)

upperfirst()

upperfirst() can currently only be used in Model Calculation expressions. It can't be used in other Expression Engines.

Uppercases the first character of a string and does not modify any other characters within the string.

Examples:

This expression will return the results shown below:

upperfirst(Remarks)

value()

Attempts to convert a given value to its numeric equivalent. This is useful when a function requires a numeric typed argument and only a string is available.

value(value)

 

(This article was formerly titled Excel Functions and also includes the functions from the previous articles on Mathematical Functions and Text Styling.)