Technical Details of the Expression Editor

Overview

Here is a list of all data types and functions within the Expression Editor.

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 Editor and a guide to using it in the Analytic Editor.

Types

Every field, value, and sub-expression 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 Editor and App Editor. 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 Editor and App Editor

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)

ROUNDDATETIME()
Truncates a Datetime value to a given unit, like month or day.
Ex. ROUNDDATETIME(datetime_variable, "Month", "America/New_York")
This will convert a datetime to just the month in Eastern Standard Time.

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

DATETIMETOTEXT()
Converts a Datetime value to a text value that is easily readable by a human
Ex. DATETIMETOTEXT(datetime_variable, "MMMM DD, YYYY", 'America/New_York')
This will convert a datetime to Eastern Standard Time and show it in traditional date formatting like "January 1, 2019".

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

TEXTTODATETIME()
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.

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.

ISNULL()

Returns true if the argument is null, otherwise false.

App Editor 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_STDEV_POPULATION()
Returns the standard deviation of the population of a given numeric array
Ex. ARRAY_STDEV_POPULATION([1,2,3])
This would return 0.816496580927726

ARRAY_STDEV_SAMPLE()
Returns the standard deviation of the sample of a given numeric array
Ex. ARRAY_STDEV_SAMPLE([1,2,3])
This would return 1

ARRAY_CONCAT()
Returns a single array with all elements from the arrays included in the arguments. Only works with arrays of the 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 Editor 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 Editor 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)

RANDOM_COLOR()

Generates a random color

Ex. RANDOM_COLOR() = ##c537a5

RGB(r: Integer, g:Integer, b:Integer) => Color

Returns a color of the specified red, green, and blue components

Ex. RGB(255, 255, 0) = ##ffff00

RGA(r: Integer, g:Integer, b:Integer, a:Integer) => Color

Returns a color of the specified red, green, blue and alpha components

Ex. RGA(255, 255, 0, 0.5) = ##ffff00 (50% opacity)

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]

USER_ID_TO_USER(id: Text) => User

Converts a user id to a user object

Ex. User_id_to_user("abc") = User

ENCODE_URI(component: Text) => Text
Returns a URL-encoded string version of a URI.
Ex. ENCODE_URI("http://example.com/a=foo&b=a test") = "http://example.com/a=foo&b=a test"

ENCODE_URI_COMPONENT(component: Text) => Text
Returns a URL-encoded string version of a URI component.

EX. ENCODE_URI_COMPONENT("a & b") = "a%20%26%20b"

**TEXTTOIMAGEURL(**url:Text) => Image URL

Converts a string to an image URL.

EX. TEXTTOIMAGEURL("https://example.com/photo.jpg")

INTERVAL_TO_SECONDS(interval: Interval) => Number

Returns the length of an Interval in seconds

EX. INTERVAL_TO_SECONDS(seconds_to_interval(30.0))

ADD_TIME()

Increments a Datetime value by a specified amount and interval unit.

Ex. ADD_TIME(datetime_variable, 2, 'years')

SUBTRACT_TIME()

Decrements a Datetime value by a specified amount and interval unit.

Ex. SUBTRACT_TIME(datetime_variable, 3, 'months')

REGEX_REPLACE()

Returns a modified Array with string replacements defined in the expression.

Ex. REGEX_REPLACE("BaNaNa", "N", "n", "g") = "Banana"

FILETOTEXT("url: File") => Text

Converts File into a text string of the URL

Ex: FILETOTEXT("http://www.africau.edu/images/default/sample.pdf")

Analytics Editor Only

COUNT()
Returns the number of completions matching condition.
Ex. COUNT(temperature > 70)

COUNTOFCOMPLETIONS()
Returns the total number of Completion. 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 completionsCompletion.
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 Editor Only
Returns the maximum of arguments across all completions
EX. MAXIMUM(weight)

MAX()
Analytics Editor Only
Alias for MAXIMUM()

MINIMUM()
Analytics Editor Only
Returns the minimum of arguments across all completions
EX. MINIMUM(weight)

MIN()
Analytics Editor 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 1

---

Did you find what you were looking for?

You can also head to community.tulip.co to post your question or see if others have faced a similar question!