Skip to content

Instantly share code, notes, and snippets.

@scarney81

scarney81/functions.md

Created December 13, 2017 23:49
Show Gist options
  • Save scarney81/a7f59060c3aa20e6e6120c5ad1316dfe to your computer and use it in GitHub Desktop.
Save scarney81/a7f59060c3aa20e6e6120c5ad1316dfe to your computer and use it in GitHub Desktop.
Download ZIP
Lanetix Conditional Operators
Raw
functions.md

Formula Function List

NOTE: When listing types a function accepts, the notation here uses the non-standard character * to represent a wild card that can be any type supported by formulas. When multiple *'s are used, they still represent any supported type, but all *'s must have the same type. For example, the notation IF(boolean, *, *) -> * translates to: The function IF takes three arguments, the first of type boolean, and the second and third of any supported type, provided the types are the same. The function returns a value of the same type as the second and third arguments. In more standard notation, it is equivalent to <T>IF(boolean, T, T) => T.

Aggregate

SUM(value)

Returns the sum of a series of numbers

Arguments

  • value - A series of numbers

Examples

=SUM($aggregate_set.related_numeric_field)
=SUM($related_account_set.revenue)

Types

SUM(integer list) -> integer
SUM(decimal list) -> decimal

Notes

  • Not supported in formulas used for validations

  • Only currently supports summations using an ACCESS using what's known in lanetix as a fiber (often referred to as referenced by). This is an object that represents the relationship between two record types. For example, if there is an contact record type with a picker field to the account record type, the relationship representing all contacts which reference a particular account using that picker is the fiber. Note that the fiber is only associated with (and thus able to be used in formulas on) the account record type.

  • Because SUM on integers returns an integer, and integers can only represent values from -2,147,483,647 to 2,147,483,647, it is recommend to not use an integer field for the SUM result, and cast the argument to a decimal: =SUM(DECIMAL($related_account_set.revenue))

Comparison

EQ(value1, value2)

Returns TRUE if two specified values are equal and FALSE otherwise. Equivalent to the = operator.

Arguments

  • value1 - The first value.
  • value2 - The value to test against value1 for equality.

Examples

=EQ(2, 3) -> FALSE
=EQ(2, $field)

Types

EQ(*, *) -> boolean

Notes

Equality comparisons for the following types are not supported:

  • dropdown (Use ISPICKVAL instead)
  • multi-select dropdown (Use INCLUDES instead)
  • hyperlink
  • file upload
  • image
  • location
  • hyperlink list
  • file upload list
  • image list

GT(value1, value2)

Returns TRUE if the first argument is strictly greater than the second, and FALSE otherwise. Equivalent to the > operator.

Arguments

  • value1 - The value to test as being greater than value2.
  • value2 - The second value.

Examples

=GT(2, 3)
=GT(2, $field)

Types

GT(date, date) -> boolean
GT(decimal, decimal) -> boolean
GT(integer, integer) -> boolean
GT(string, string) -> boolean

Notes

  • NULL dates are considered to have occurred at the first instance of time, and thus before all non-NULL dates.

  • It is unfortunately not possible to compare integer and decimal fields together without a manual casting the integer field using the DECIMAL function. The suggested solution to this issue is incredibly complex, and your understanding is appreciated.

  • String comparisons use alphabetical ordering, and are case sensitive. Numeric characters are considered "less than" alphabetical ones, and upper case letters are considered "less than" lower case. Ordering of symbols typically follows the ordering as defined in the unicode specification, but this is not guaranteed. Ordering of symbols between formula fields and formulas as validations may vary slightly.

  • Be aware that string comparisons happen in the order of their characters, so =GT("1", "05") will return TRUE, because the second argument starts with the text 0, which is considered less than than the text 1. Comparing numbers, =GT(1, 05), will return FALSE as expected.

GTE(value1, value2)

Returns TRUE if the first argument is greater than or equal to the second, and FALSE otherwise. Equivalent to the >= operator, or the formula =OR(GT(value1, value2), EQ(value1, value2)).

Arguments

  • value1 - The value to test as being greater than or equal to value2.
  • value2 - The second value.

Examples

=GTE(2, 3) -> FALSE
=GTE(2, $field)

Types

GTE(date, date) -> boolean
GTE(decimal, decimal) -> boolean
GTE(integer, integer) -> boolean
GTE(string, string) -> boolean

Notes

  • NULL dates are considered to have occurred at the first instance of time, and thus before all non-NULL dates.

  • It is unfortunately not possible to compare integer and decimal fields together without a manual casting the integer field using the DECIMAL function. The suggested solution to this issue is incredibly complex, and your understanding is appreciated.

  • String comparisons use alphabetical ordering, and are case sensitive. Numeric characters are considered "less than" alphabetical ones, and upper case letters are considered "less than" lower case. Ordering of symbols typically follows the ordering as defined in the unicode specification, but this is not guaranteed. Ordering of symbols between formula fields and formulas as validations may vary slightly.

  • Be aware that string comparisons happen in the order of their characters, so =GTE("1", "05") will return TRUE, because the second argument starts with the text 0, which is considered less than than the text 1. Comparing numbers, =GTE(1, 05), will return FALSE as expected.

LT(value1, value2)

Returns TRUE if the first argument is strictly less than the second, and FALSE otherwise. Equivalent to the < operator.

Arguments

  • value1 - The value to test as being less than value2.
  • value2 - The second value.

Examples

=LT(2, 3) -> TRUE
=LT(2, $field)

Types

LT(date, date) -> boolean
LT(decimal, decimal) -> boolean
LT(integer, integer) -> boolean
LT(string, string) -> boolean

Notes

  • NULL dates are considered to have occurred at the first instance of time, and thus before all non-NULL dates.

  • It is unfortunately not possible to compare integer and decimal fields together without a manual casting the integer field using the DECIMAL function. The suggested solution to this issue is incredibly complex, and your understanding is appreciated.

  • String comparisons use alphabetical ordering, and are case sensitive. Numeric characters are considered "less than" alphabetical ones, and upper case letters are considered "less than" lower case. Ordering of symbols typically follows the ordering as defined in the unicode specification, but this is not guaranteed. Ordering of symbols between formula fields and formulas as validations may vary slightly.

  • Be aware that string comparisons happen in the order of their characters, so =LT("1", "05") will return FALSE, because the second argument starts with the text 0, which is considered less than than the text 1. Comparing numbers, =LT(1, 05), will return TRUE as expected.

LTE(value1, value2)

Returns TRUE if the first argument is strictly less than or equal to the second, and FALSE otherwise. Equivalent to the <= operator, or the formula =OR(LT(value1, value2), EQ(value1, value2)).

Arguments

  • value1 - The value to test as being less than value2.
  • value2 - The second value.

Examples

=LTE(2, 3) -> TRUE
=LTE(2, $field)

Types

LTE(date, date) -> boolean
LTE(decimal, decimal) -> boolean
LTE(integer, integer) -> boolean
LTE(string, string) -> boolean

Notes

  • NULL dates are considered to have occurred at the first instance of time, and thus before all non-NULL dates.

  • It is unfortunately not possible to compare integer and decimal fields together without a manual casting the integer field using the DECIMAL function. The suggested solution to this issue is incredibly complex, and your understanding is appreciated.

  • String comparisons use alphabetical ordering, and are case sensitive. Numeric characters are considered "less than" alphabetical ones, and upper case letters are considered "less than" lower case. Ordering of symbols typically follows the ordering as defined in the unicode specification, but this is not guaranteed. Ordering of symbols between formula fields and formulas as validations may vary slightly.

  • Be aware that string comparisons happen in the order of their characters, so =LTE("1", "05") will return FALSE, because the second argument starts with the text 0, which is considered less than than the text 1. Comparing numbers, =LTE(1, 05), will return TRUE as expected.

NE(value1, value2)

Returns TRUE if two specified values are not equal and FALSE otherwise. Equivalent to the <> operator (the alternate syntax != also works).

Arguments

value1 - The first value. value2 - The value to test against value1 for inequality.

Examples

=NE(2, 3) -> TRUE
=NE(2, $field)

Types

NE(*, *) -> boolean

Notes

Equality comparisons for the following types are not supported:

  • dropdown (Use ISPICKVAL instead)
  • multi-select dropdown (Use INCLUDES instead)
  • hyperlink
  • file upload
  • image
  • location
  • hyperlink list
  • file upload list
  • image list

Info

ISBLANK(value)

Returns TRUE if the value is unknown (NULL), or is a value that would cause lanetix to display -- in the UI instead of the actual value (or the unchecked checkbox with a line through it).

Arguments

  • value - the value to check for blankness

Examples

=ISBLANK("") -> TRUE
=ISBLANK("     ") -> TRUE
=ISBLANK("Not blank") -> FALSE
=ISBLANK($field)

Types

ISBLANK(*) -> boolean

Notes

  • All types are considered blank if they are NULL
  • strings are additionally considered blank if they are empty (""), or contain all whitespace
  • hyperlinks are additionally considered blank if their URL value is empty or all whitespace
  • lists are additionally considered blank if they are not NULL and have no entries
  • All other types have no special semantics around ISBLANK

ISERROR(value)

Checks whether a value is an error.

Arguments

  • value - The value to be verified as an error.

Examples

=ISERROR(5 / 0) -> TRUE
=ISERROR($revenue / $total_revenue)

Logical

AND(logical_expression1, logical_expression2)

Returns TRUE if all of the provided arguments are logically TRUE, and FALSE if any of the provided arguments are logically FALSE.

Arguments

  • logical_expression1 - An expression or reference to a cell containing an expression that represents some logical value, i.e. TRUE or FALSE, or an expression that can be coerced to a logical value.
  • logical_expression2 - Another expression that represents a logical value.

Examples

=AND(TRUE(), TRUE()) -> TRUE
=AND(TRUE(), FALSE()) -> FALSE
=AND(FALSE(), TRUE()) -> FALSE
=AND(FALSE(), FALSE()) -> FALSE

=AND($field_1 = 1, $field_2 = 2)
=AND($boolean_field, $other_boolean_field)
=AND($air_freight_checkbox, $revenue > 1000)

Types

AND(boolean, boolean) -> boolean

Notes

  • The value 0 and the empty string "" are not automatically cast to booleans like in some languages. To use them in AND, use an explicit comparison =AND($field_1 = 0, $field_2 = "")

  • Only two arguments can be supplied to AND, to use more, nest the additional ands: =AND($field_1 = 1, AND($field_2 = 2, $field_3 = 3))

  • Short circuit evaluation is used, so if the first argument evaluates to FALSE, the second argument is never evaluated. This means formulas like =AND(FALSE(), 5 / 0) will return FALSE rather than an error.

FALSE()

Returns the logical value FALSE.

Arguments

None

Examples

=FALSE() -> FALSE

Types

FALSE() -> boolean

IF(logical_expression, value_if_true, value_if_false)

Returns one value if the logical expression is TRUE and another if it is FALSE.

Arguments

  • logical_expression - An expression or reference to a cell containing an expression that represents some logical value, i.e. TRUE or FALSE.
  • value_if_true - The value the function returns if logical_expression is TRUE.
  • value_if_false - The value the function returns if logical_expression is FALSE.

Examples

=IF(TRUE(), 0, 10) -> 0
=IF(FALSE(), 0, 10) -> 10
=IF($education = "PHD", `Hello Dr. $name`, `Hello $name`)
=IF($boolean_field, "boolean_field is true (checkbox is checked)", "boolean_field is false (checkbox is not checked)")

Notes

  • Ensure that value_if_true and value_if_false are provided to the function in the correct order - according to google, this is the single most common source of problems with IF.

  • Lanetix booleans are technically tri-state (TRUE, FALSE, or NULL). In order to keep things simple and not introduce three-value logic in formulas, NULL is treated as if it were FALSE. This has consequences as any related booleans accessed through pickers will be false when the picker they're accessed through is not selected. For example, the formula =IF($picker.boolean_field, 1, 0) will only evaluate to 1 if the picker field is selected and the related boolean_field on the picked record instance is TRUE.

  • The case that is not selected is never executed, so errors from it will not propagate. For example, the formula =IF(TRUE(), 1, 1 / 0) will evaluate to 1, not an error from dividing by zero.

NOT(logical_expression)

Returns the opposite of a logical value - NOT(TRUE()) returns FALSE; NOT(FALSE()) returns TRUE.

Arguments

  • logical_expression - An expression that represents some logical value, i.e. TRUE or FALSE.

Examples

=NOT(TRUE()) -> FALSE
=NOT($field = 2)
=NOT(ISERROR(5 / 0)) -> FALSE

Types

NOT(boolean) -> boolean

Notes

  • Neither strings nor integers automatically cast to booleans, in order to get the usual "truthy" semantics from these types, use explicit comparisons: =NOT($string = "") or =NOT($number = 0).

OR(logical_expression1, logical_expression2)

Returns TRUE if any of the provided arguments are logically TRUE, and FALSE if all of the provided arguments are logically FALSE.

Arguments

  • logical_expression1 - An expression that represents some logical value, i.e. TRUE or FALSE
  • logical_expression2 - Another expression representing some logical value, i.e. TRUE or FALSE

Examples

=OR(TRUE(), TRUE()) -> TRUE
=OR(TRUE(), FALSE()) -> TRUE
=OR(FALSE(), TRUE()) -> TRUE
=OR(FALSE(), FALSE()) -> FALSE

=OR($field_1 = 1, $field_2 = 2)
=OR($boolean_field, $other_boolean_field)
=OR($air_freight_checkbox, $revenue > 1000)

Types

OR(boolean, boolean) -> boolean

Notes

  • The value 0 and the empty string "" are not automatically cast to booleans like in some languages. To use them in OR, use an explicit comparison =OR($field_1 = 0, $field_2 = "").

  • Only two arguments can be supplied to OR, to use more, nest the additional ands: =OR($field_1 = 1, OR($field_2 = 2, $field_3 = 3))

  • Short circuit evaluation is used, so if the first argument evaluates to TRUE, the second argument is never evaluated. This means formulas like =OR(TRUE(), 5 / 0) will return TRUE rather than an error.

TRUE()

Returns the logical value TRUE.

Arguments

None

Examples

=TRUE()

Types

TRUE() -> boolean

XOR(logical_expression1, logical_expression2)

Returns a logical Exclusive Or of all arguments. That is, returns TRUE if either logical_expression1 is TRUE or logical_expression2 is TRUE, but returns FALSE if both are TRUE.

Arguments

  • logical_expression1 - An expression that represents some logical value, i.e. TRUE or FALSE
  • logical_expression2 - Another expression representing some logical value, i.e. TRUE or FALSE

Examples

=XOR(TRUE(), TRUE()) -> FALSE
=XOR(TRUE(), FALSE()) -> TRUE
=XOR(FALSE(), TRUE()) -> TRUE
=XOR(FALSE(), FALSE()) -> FALSE

=XOR($field_1 = 1, $field_2 = 2)
=XOR($boolean_field, $other_field = 1)

Types

XOR(boolean, boolean) -> boolean

Notes

  • The value 0 and the empty string "" are not automatically cast to booleans like in some languages. To use them in XOR, use an explicit comparison =XOR($field_1 = 0, $field_2 = "")

  • Only two arguments can be supplied to XOR, to use more, nest the additional ands: =XOR($field_1 = 1, XOR($field_2 = 2, $field_3 = 3))

Lookup

ACCESS(value1, value2)

Allows accessing related fields through pickers, system fields through the use of the $lanetix field, or aggregating related fields through the use of a one to many relationship.

Arguments

  • value1 - The field or relationship being accessed
  • value2 - The field to access from value1. If accessing through an aggregatable relationship, multiple values will be accessed and constructed into a list.

Examples

=ACCESS($picker, $name)
=ACCESS($lanetix, $id)
=ACCESS(ACCESS($picker, $lanetix), $id)
=ACCESS(ACCESS($picker, $secondary_picker), $name)

It's highly recommended to use the . operator instead of the ACCESS function for clarity, as the following are equivalent to their respective examples above:

=$picker.name
=$lanetix.id
=$picker.lanetix.id
=$picker.secondary_picker.name

Types

ACCESS(picker, *) -> *
ACCESS(lanetix, *) -> *
ACCESS(related record accessor, *) -> *
ACCESS(aggregatable relationship, *) -> list of *

Notes

  • Not supported in formulas used for validations
  • Shadow record types (e.g. users) cannot be accessed even though pickers can be created to select them

Math

ADD(augend, addend)

Returns the sum of two numbers. Equivalent to the + operator.

Arguments

  • aguend - The number add to
  • addend - The number to be added

Examples

=ADD(2, 3) -> 5
=ADD($field, 2.5)
=ADD(DECIMAL($integer_field), $decimal_field)

Types

ADD(integer, integer) -> integer
ADD(decimal, decimal) -> decimal

Notes

  • Any field for which ISBLANK returns TRUE will be treated as 0

  • It is unfortunately not possible to add an integer and decimal fields together without a manual casting the integer field using the DECIMAL function. The suggested solution to this issue is incredibly complex, and your understanding is appreciated.

  • Literals are auto-casted, which means they might unexpectedly be rounded. For example =ADD($field, 2.5) is evaluated as =ADD($field, 3) if field's type is integer. This does not apply to formulas used as validations.

  • Formulas used as validations use floating-point representation for all numbers. Although quite technical, it is recommended to read and understand the limitations and problems with this representation.

DECIMAL(value)

Converts the value to a decimal

Arguments

  • value - The value to convert to a decimal

Examples

=DECIMAL(5) -> 5
=DECIMAL($integer_field)

Types

DECIMAL(integer) -> decimal

Notes

DECIMAL can currently only be used on fields - not literals or expressions. Formulas used for validations do not have this restriction.

DIVIDE(dividend, divisor)

Returns one number divided by another. Equivalent to the / operator.

Arguments

  • dividend - The number to be divided.
  • divisor - The number to divide by.

Examples

=DIVIDE(10, 5) -> 2
=DIVIDE(5, 10) -> 0.5
=DIVIDE($field, 2)
=DIVIDE(DECIMAL($integer_field), $decimal_field)

Types

DIVIDE(integer, integer) -> integer
DIVIDE(decimal, decimal) -> decimal

Notes

  • Any field for which ISBLANK returns true will be treated as 0

  • It is unfortunately not possible to divide integer and decimal fields together without a manual casting the integer field using the DECIMAL function. The suggested solution to this issue is incredibly complex, and your understanding is appreciated.

  • When dividing two integers, the resulting value is rounded to an integer using an away from zero strategy (if needed)

  • Dividing by zero will result in an #Error! message being displayed as the value when using formula fields, and the validation being considered failed when using formulas as validations.

INCLUDES(multi_select_dropdown, api_name)

Returns TRUE if the api_name is one of the selected dropdown options, FALSE otherwise

Arguments

  • multi_select_dropdown - a multi-select dropdown field
  • api_name - a string matching the API Name (not display name) of the option to check if it's selected

Examples

=INCLUDES($states, "va")

Types

INCLUDES(multi-select dropdown, string) -> boolean

Notes

  • This cannot be used on single-select dropdowns, use ISPICKVAL to check what the selected option is instead.
  • The second argument must be the API name of the option. Using display names is not supported, as it's possible to have multiple options that all have the same display name, which makes it ambiguous as to which particular option is being searched for.
  • To check for multiple selected values, use the AND and OR functions. For example, =OR(INCLUDES($states, "va"), INCLUDES($states, "ca"))

INTEGER(value)

Casts the value to an integer, rounding away from zero (if needed).

Arguments

  • value - the value to convert to an integer

Examples

=INTEGER(10.1) -> 10
=INTEGER(10.5) -> 11
=INTEGER(-10.5) -> -11
=INTEGER($decimal_field)

Types

INTEGER(decimal) -> integer

Notes

INTEGER can currently only be used on fields - not literals or expressions. Formulas used for validations do not have this restriction.

ISPICKVAL(dropdown, api_name)

Returns TRUE if the dropdown's value has the same API name as the api_name argument, FALSE otherwise

Arguments

  • dropdown - a dropdown field
  • api_name - a string matching the API Name (not display name) of the option to check if it's selected

Examples

=ISPICKVAL($states, "va")

Notes

  • This cannot be used on multi-select dropdowns, use INCLUDES to check what the selected option(s) is instead.
  • The second argument must be the API name of the option. Using display names is not supported, as it's possible to have multiple options that all have the same display name, which makes it ambiguous as to which particular option is being searched for.

MINUS(minuend, subtrahend)

Returns the difference of two numbers. Equivalent to the - operator.

Arguments

  • minuend - The number to be subtracted from.
  • subtrahend - The number to subtract from minuend.

Examples

=MINUS(2, 3) -> -1
=MINUS($field, 2.5)
=MINUS(DECIMAL($integer_field), $decimal_field)

Types

MINUS(integer, integer) -> integer
MINUS(decimal, decimal) -> decimal

Notes

  • Any field for which ISBLANK returns true will be treated as 0

  • It is unfortunately not possible to subtract an integer and decimal fields together without a manual casting the integer field using the DECIMAL function. The suggested solution to this issue is incredibly complex, and your understanding is appreciated.

  • Literals are auto-casted, which means they might unexpectedly be rounded. For example =MINUS($field, 2.5) is evaluated as =MINUS($field, 3) if field's type is integer. This does not apply to formulas used as validations.

  • Formulas used as validations use floating-point representation for all numbers. Although quite technical, it is recommended to read and understand the limitations and problems with this representation.

MOD(dividend, divisor)

Returns the result of the modulo operation: the remainder after a division.

Arguments

  • dividend - The number to be divided to find the remainder.
  • divisor - The number to divide by.

Examples

=MOD(10, 7) -> 3
=MOD(-10, 7) -> 4
=MOD(10, -7) -> -4
=MOD(-10, -7) -> -3

=MOD($field, 2.5)
=MOD(DECIMAL($integer_field), $decimal_field)

Types

MOD(integer, integer) -> integer
MOD(decimal, decimal) -> decimal

Notes

  • Any field for which ISBLANK returns true will be treated as 0

  • Since MOD performs a division, a divide by zero error will result if the divisor is 0

  • There's some contention mathematically around what value should be returned in the event that the dividend or divisor is negative. The value returned in the case of formulas will have the same sign as the divisor, the same behavior as Excel.

  • It is unfortunately not possible to subtract an integer and decimal fields together without a manual casting the integer field using the DECIMAL function. The suggested solution to this issue is incredibly complex, and your understanding is appreciated.

  • Literals are auto-casted, which means they might unexpectedly be rounded. For example =MOD($field, 2.5) is evaluated as =MOD($field, 3) if field's type is integer. This does not apply to formulas used as validations.

  • Formulas used as validations use floating-point representation for all numbers. Although quite technical, it is recommended to read and understand the limitations and problems with this representation.

MULTIPLY(multiplicand, multiplier)

Returns the product of two numbers. Equivalent to the * operator.

Arguments

  • multiplicand - The number to multiply.
  • multiplier - The number to multiply by.

Examples

=MULTIPLY(2, 3) -> 6
=MULTIPLY($field, 2.5)
=MULTIPLY(DECIMAL($integer_field), $decimal_field)

Types

MULTIPLY(integer, integer) -> integer
MULTIPLY(decimal, decimal) -> decimal

Notes

  • Any field for which ISBLANK returns true will be treated as 0

  • It is unfortunately not possible to subtract an integer and decimal fields together without a manual casting the integer field using the DECIMAL function. The suggested solution to this issue is incredibly complex, and your understanding is appreciated.

  • Literals are auto-casted, which means they might unexpectedly be rounded. For example =MULTIPLY($field, 2.5) is evaluated as =MULTIPLY($field, 3) if field's type is integer. This does not apply to formulas used as validations.

  • Formulas used as validations use floating-point representation for all numbers. Although quite technical, it is recommended to read and understand the limitations and problems with this representation.

POW(base, exponent)

Alias of the POWER function

POWER(base, exponent)

Returns a number raised to a power.

Arguments

  • base - The number to raise to the exponent power.
  • exponent - The exponent to raise base to.

Examples

=POWER(2, 3) -> 8
=POWER($field, 2.5)
=POWER(DECIMAL($integer_field), $decimal_field)

Types

POWER(integer, integer) -> integer
POWER(decimal, decimal) -> decimal

Notes

  • Not supported outside of formulas used for validations

UMINUS(value)

Returns a number with the sign reversed.

Arguments

  • value - The number to have its sign reversed. Equivalently, the number to multiply by -1. Equivalent to the unary - operator.

Examples

=UMINUS(0) -> 0
=UMINUS(10) -> -10
=UMINUS(-10) -> 10

Types

UMINUS(integer) -> integer
UMINUS(decimal) -> decimal

Notes

  • It's usually much clearer to use the unary - operator: =UMINUS($field) -> =-$field
  • Although extremely confusing, it can be used in conjunction with the binary - operator: =5 - -$field. It would be better to change this to =5 + $field, though, as they're equivalent.

UPLUS(value)

Returns a specified number, unchanged.

Arguments

  • value - The number to return.

Examples

=UPLUS(0) -> 0
=UPLUS(10) -> 10
=UPLUS(-10) -> -10

Types

UPLUS(integer) -> integer
UPLUS(decimal) -> decimal

Notes

  • Shouldn't ever be needed. Exists for parity with Google sheets, and a couple other technical implementation details specific to formulas.

Text

CONCAT(value1, value2)

Returns the concatenation of two values. Equivalent to the & operator, or template strings. Because CONCAT only takes two arguments, it's highly recommended to use template strings instead.

Arguments

  • value1 - The value to which value2 will be appended.
  • value2 - The value to append to value1.

Examples

=CONCAT("Hello ", "World") -> "Hello World"
=CONCAT("Hello ", $name)

Types

CONCAT(string, string) -> string

Notes

  • Non-string values must be converted using the TEXT function before being used in CONCAT: =CONCAT("The number is: ", TEXT(5))

  • Any field for which ISBLANK returns TRUE will be treated as the empty string, ""

TEXT(value)

Converts the value to a string

Arguments

  • value - the value to convert to a string

Examples

=TEXT(5) -> "5"
=TEXT($field)
=TEXT($field = 2)

Types

TEXT(id) -> string
TEXT(date) -> string
TEXT(boolean) -> string
TEXT(decimal) -> string
TEXT(integer) -> string
TEXT(dropdown) -> string
TEXT(string) -> string

Notes

  • Dates will be converted to YYYY-MM-DD form, using the timezone set in the user preferences configuration of the Lanetix UI. This may cause the date to be displayed differently that the date field itself, which is formatted in the UI using the timezone reported by the browser. If these timezones are different, you may see odd behavior.

  • Integers or decimals for which ISBLANK returns true will be converted to "0"

  • Booleans for which ISBLANK returns true will be converted to "false"

  • The id type is not a picker, as is the common technical nomenclature in lanetix proper, but a more specific, formula only type that only $lanetix.id values can inhabit

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment