This list will help you understand all functions and data types from the Expression Editor.

Here's a guide to using the Expression Editor in the App Builder.

And a guide to using it in the Analytics Builder.

## Types

Every field, value, and subexpression has a data type that allows the Tulip system to check whether your expression is valid. This prevents expression from operations like adding 3 to a day of the week. As long as your expression makes logical sense you should not run into any type errors; however, if you do, the editor will display an error with the rule you have violated. The Expression Editor uses the following types:

**Text**: A sequence of characters, like “foo” or “abc123”.**Integer**: An integer (a number without a fractional component), like -3, 0, or 5.**Number:**Any real number, like -3.2, 0, 3, or 4.2345.**Interval:**An amount of time, measured in seconds.**Boolean**: true or false.**Day of Week**: A day of the week, like Monday or Saturday.**Date**: A date, like Wednesday, November 29th, 2017.**Datetime:**A time and date, like Wednesday, November 29th, 2017 at 1:05 PM EST.**Week**: A week, like the week starting on Monday, November 27th, 2017.**Month**: A month, like November 2017.**User**: Any user in the Tulip system, as described here.**Station**: Any station on the shop floor, as described here.**App**: Any Tulip app.**App Version**: A version of an app, as described here.

## Operators

**!**

Logical NOT. Returns true if and only if its argument is false.*Ex:* @variable.work_order_number != 123456

**= **

Checks if the value of two operands are equal or not, if yes, then the condition becomes true.

Ex: (A = B)

**> **

Checks if the value of the left operand is greater than the value of the right operand, if yes, then the condition becomes true.

Ex: (A > B) is not true.

**<**

Checks if the value of the left operand is less than the value of the right operand, if yes, then the condition becomes true.

Ex: (A < B) is true.

**>= **

Checks if the value of the left operand is greater than or equal to the value of the right operand, if yes, then the condition becomes true.

Ex: (A >= B) is not true.

**<= **

Checks if the value of the left operand is less than or equal to the value of the right operand, if yes, then the condition becomes true.

Ex: (A <= B) is true.

**& **

Logical AND. If both the operands are non-zero, then the condition becomes true.

Ex: (A & B) is true.

**| **

If any of the two operands are non-zero, then the condition becomes true.

Ex: (A | B) is true.

**+**

Adds two operands

Ex: A + B will give 30

**- **

Subtracts the second operand from the first

Ex: A - B will give -10

*** **

Multiply both operands

Ex: A * B will give 200

**/**

Divide the numerator by the denominator

Ex: B / A will give 2

**% **

Outputs the remainder of an integer division

Ex: B % A will give 0

## Functions

These are all the available functions in both the Analytics Builder and App Builder. Keep in mind, all examples are based on app completions. So, when you use a MAXIMUM() or MINIMUM() function, for example, you are looking for the maximum or minimum value of a variable across all completions of an app.

## Analytics Builder and App Builder

**TEXTTOBOOLEAN()**

Parses text into a boolean.

"y", "yes", "t", "true" and "on" and any capitalized variants are recognized as true, and "n", "no", "f" "false" and "off" are recognized as false.

Ex. TEXTTOBOOLEAN("yes")

**TEXTTONUMBER()**

Parses text or timestamp into a number

Ex. TEXTTONUMBER("2.4")

**TEXTTOINTEGER()**

Parses text or interval into an integer.

Ex. TEXTTOINTEGER("123")

**TOTEXT()**

Parses anything into text.

Ex. TOTEXT(2)

**ROUND()**

Rounds a number to the specified number of decimal places. Maximum of 3 decimal places.

Ex. 1 - ROUND(123.123456, 1)

This will return 123.1

Ex. 2 - ROUND(123.12)

This will return 123

**SECONDS_TO_INTERVAL()**

Converts seconds into an interval.

SECONDS_TO_INTERVAL(seconds: Integer or Number) => Interval

Ex. SECONDS_TO_INTERVAL(20) => 0:00:20

**CEIL()**

Rounds up to the nearest integer

**FLOOR()**

Rounds down to the nearest integer

**LINK()**

Returns the first of the arguments that is not null.

Ex. LINK(weight, 70)

This will return the value of *weight *if weight has a value

**SUBSTITUTE()**

Within a string given in the first argument, replace all examples of the second argument with the third argument

Ex. SUBSTITUTE("A734", "7", "3")

This will return "A334"

**IF()**

Based on the first argument, returns the second argument if first argument is true, or third argument if first argument is false.

Ex. IF(true, 1, 2)

**DATE_TRUNC_TZ()**

Truncates a datetime value to a given unit, like month or day.

Ex. DATE_TRUNC_TZ(*datetime_variable*, "Month", "America/New_York")

This will convert a datetime to just the month in Eastern Standard Time

More guidance here

**Note:** The last parameter, the time zone, must come from one of the tz database names listed here

**FORMAT_DATE_TZ()**

Converts a datetime value to a text value that is easily readable by a human

Ex. FORMAT_DATE_TZ(*datetime_variable*, "Month", "America/New_York")

This will convert a datetime to just the month in Eastern Standard Time and show it in traditional date formatting like "January 1, 2019"

More guidance here

**Note:** The last parameter, the time zone, must come from one of the tz database names listed here

**PARSE_DATETIME_TZ()**

Converts a text value into a datetime value with the specified parameters. Commonly used if you have a connector function that shares a datetime in text format and you need to convert it

More guidance here

**LEFT()**

Returns a substring of a text value, beginning at the position specified by the second argument.

**RIGHT()**

Returns a substring of a text value, ending at the position specified by the second argument.

**MID()**

Returns a substring of a text value, starting at the position specified by the second argument. The third argument specifies the length of the return.

**SPLIT()**

Splits a text value into an array by a specified delimiter.

**FIND()**

Returns the position for where the first argument occurs within the second argument.

**LEN()**

Returns the length of a text value.

**TRIM()**

Removes a substring (specified by the second argument) from the front and back of the first argument.

**REGEX_MATCH()**

Returns an array of regular expression matches of a text value. Matches based on the given regular expression and modifiers.

## App Builder Only

**ARRAYCONTAINS()**

Determine if a value is in an array

Ex. ARRAYCONTAINS([1,2,3], 1)

This will return "true"

**ARRAY_MIN()**

Returns the minimum from a given array

Ex. ARRAY_MIN([1,2,3])

This would return 1

**ARRAY_MAX()**

Returns the minimum from a given array

Ex. ARRAY_MAX([1,2,3])

This would return 3

**ARRAY_AVG()**

Returns the average of all numbers in an array

Ex. ARRAY_AVG([1,2,3])

This would return 2

**ARRAY_CONCAT()**

Returns a single array with all elements from the arrays included in the arguments. Only works with arrays of same type ie all text arrays.

Ex. ARRAY_CONCAT([1,2],[3,4],[5,6])

This would return [1,2,3,4,5,6]

**HEX_TO_INT()**

Return the numeric representation of a given hexadecimal string.

Ex. HEX_TO_INT('0xAB18') = '43800'

**INT_TO_HEX()**

Return the hexadecimal representation of a given number.

Ex. INT_TO_HEX('43800') = '0xAB18'

**RANDOMSTRING()**

Returns a random 17-character string. Meant for creating an ID for a new Table record.

**MAX()***App Builder Only*

Returns the argument with the highest value

Ex. MAX(var1, var2)

This will return the value of var1 if it is greater than the value of var2

**MIN()***App Builder Only*

Returns the argument with the highest value

Ex. MIN(var1, var2)

This will return the value of var1 if it is less than the value of var2

**UPPERCASE()**

Converts a string to all uppercase

**LOWERCASE()**

Converts a string to all lowercase

**SIN()**

Returns the sine of the given input

**COS()**

Returns the cosine of the given input

**TAN()**

Returns the tangent of the given input

**ABS()**

Return the absolute value of a number

**SQRT()**

Return the square root of a number

**POW()**

Return the first argument raised to the second argument

Ex. POW(3,2)

This will return 9

**CONTAINS()**

Returns whether a string contains a given substring

Ex. CONTAINS("123ABC", "123")

This would return "true"

**RAND()**

Generates a random decimal number between 0 and 1

**RANDBETWEEN()**

Generates a random integer between two specified numbers

Ex. RANDBETWEEN(5,10)

**MAP_TO_BOOLEAN_LIST**(*a:* {} array, *b*: text) => array

Converts an object array to a base array for the given key

Ex. MAP_TO_BOOLEAN_LIST([{key: value}], ‘key’) = [value]

**MAP_TO_NUMBER_LIST**(*a:* {} array, *b*: text) => array

Converts an object array to a base array for the given key

Ex. MAP_TO_NUMBER_LIST([{key: value}], ‘key’) = [value]

**MAP_TO_INTEGER_LIST**(*a:* {} array, *b*: text) => array

Converts an object array to a base array for the given key

Ex. MAP_TO_INTEGER_LIST([{key: value}], ‘key’) = [value]

**MAP_TO_TEXT_LIST**(*a:* {} array, *b*: text) => array

Converts an object array to a base array for the given key

Ex. MAP_TO_TEXT_LIST([{key: value}], ‘key’) = [value]

## Analytics Builder Only

**COUNT()**

Returns the number of completions matching condition.

Ex. COUNT(temperature > 70)

**COUNTOFCOMPLETIONS()**

Returns the total number of completions. Does not take an argument.

Ex. COUNTOFCOMPLETIONS()

**SUM()**

Returns the sum of arguments across all completions.

Ex. SUM(weight)

**SUMFILTERED()**

Returns the sum of arguments across all completions, filtered to match a condition

Ex. SUMFILTERED(weight, temperature > 70)

**AVERAGE()**

Returns the average of arguments across all completions.

Ex. AVERAGE(weight)

**AVG()**

Alias for AVERAGE()

**AVERAGEFILTERED()**

Returns the average of arguments across all completions, filtered to match a condition

Ex. AVERAGEFILTERED(weight, temperature > 70)

**AVGFILTERED()**

Alias for AVERAGEFILTERED()

**MAXIMUM()***Analytics Builder Only*

Returns the maximum of arguments across all completions

EX. MAXIMUM(weight)

**MAX()***Analytics Builder Only*

Alias for MAXIMUM()

**MINIMUM()***Analytics Builder Only*

Returns the minimum of arguments across all completions

EX. MINIMUM(weight)

**MIN()***Analytics Builder Only*

Alias for MINIMUM()

**MAXIMUMFILTERED()**

Returns the maximum of arguments across all completions, filtered to match a condition

Ex. MAXIMUMFILTERED(weight, temperature > 70)

**MAXFILTERED()**

Alias for MAXIMUMFILTERED()

**MINIMUMFILTERED()**

Returns the minimum of arguments, filtered to match a condition

Ex. MINIMUMFILTERED(weight, temperature > 70)

**MINFILTERED()**

Alias for MINIMUMFILTERED()

**NULLIF()**

The `NULLIF`

function returns a null value if `argument_1`

equals to `argument_2`

, otherwise it returns `argument_1`

.

Ex. NULLIF(argument_1, argument_2)

This would return null if argument_1 was 1 and argument_2 was 0

**DATE_TRUNC()**

Truncates a datetime value to a given unit, like month or day.

Ex. DATE_TRUNC(*datetime_variable*, "Month")

This will convert a datetime to just the month