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

PARSEBOOLEAN()
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. PARSEBOOLEAN("yes")

PARSEFLOAT()
Parses text or an interval into a float.
Ex. PARSEFLOAT("123.12")

PARSEINTEGER()
Parses text or interval into an integer.
Ex. PARSEINTEGER("123")

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

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)

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

Did this answer your question?