📘

This document highlights the syntax for MW DSL

To know about the Data objects that you can use, please visit this document
To go through some common examples, please visit this document

Overview

DSL rules evaluate against Moveworks Data Objects like User, Ticket, Doc, Domain, and Ticket Context or data objects from external connectors (such as API responses in a JSON format).

All keywords in the DSL grammar are case-insensitive (i.e.: TRUE, true, TrUe, tRuE, etc all mean the same thing). Rules can contain extra spaces/tabs/newlines without impacting the execution of the rule. The only exception is for whitespace contained within quotes for string-matching.

Operators and Syntax

Precedence rules (decreasing priority):

Data types

Text

Syntax: "..." | '...'

If the text value contains quotations or an apostrophe, it can be escaped using a backslash (\\). If the text is wrapped in single quotes, then double quotes do not need to be escaped, and if the text is wrapped in double quotes then single quotes do not need to be escaped.

Examples:

"This is a freetext value"
"The dog's name is Spot"
"Spot the dog said \\"Woof\\""
'This is a freetext value using single quotes'
'The dog\\'s name is Spot'
'Spot the dog said "Woof"'

Numbers

Syntax: ### | -### | ###.### | -###.###

We support both integer and float values. Examples:

1
-1
0
0.0
2.345

Null value

Syntax: NULL

Constant value which represents the lack of existence of another object or the canonical value of None

Boolean Constants

Syntax: TRUE | FALSE

Constant values which represent the canonical values of true and false.

Lists

Syntax: [expression1, expression2, ...]

Expressions can be hard-coded values or defined dynamically in terms of the input payload. Examples:

["hard", "coded", "values"]
[1, 2, 3, 4]
[]
[input_data.first_value, input_data.second_value, input_data.third_value]

Objects

Syntax: {keyExpression1: valExpression1, keyExpression2: valExpression2, ...}

Defines a way to construct a set of key-value pairs. Note that the key types must be a primitive type (text/number/boolean/null) Examples:

{"key1": "val1", "key2": 123}
{"key1": "val1", "key2": input_data.dynamic_value}
{"key1": "val1", input_data.dynamic_key_2: input_data.dynamic_value}

Truth Value

Logical operators

NOT

Syntax: NOT ...

Converts a value to the opposite of its truth value. Examples:

NOT "value" -> FALSE
NOT "" -> TRUE
NOT NULL -> TRUE
NOT FALSE -> TRUE
NOT TRUE -> FALSE
NOT [] => TRUE
NOT {} => TRUE
NOT [1, 2, 3] => FALSE

AND

Syntax: expression1 AND expression2 AND expression3 AND ...

Walks down a list of expressions and returns the first falsy value. If all expressions are truthy then the last element of the chain is returned.

Note that AND expressions are lazily evaluated. In other words if expression2 is falsy then expression3 will not be evaluated. Examples:

1 AND 2 -> 2
1 AND 0 AND 2 -> 0
NULL AND TRUE -> NULL

OR

Syntax: expression1 OR expression2 ORAORND expression3 OR ...

Walks down a list of expressions and returns the first truthy value. If all expressions are false then the last element of the chain is returned.

Note that OR expressions are lazily evaluated. In other words if expression2 is truthy then expression3 will not be evaluated. Examples:

1 OR 2 -> 1
1 OR 0 OR 2 -> 1
NULL OR TRUE -> TRUE
NULL OR FALSE -> FALSE

Accessors

Syntax: .identifier

Gets attribute at identifier from parent object. identifier must be alphanumeric (underscores ok) and not start with a number. This accessor method does not work on list objects. For non-standard keys or indexing into list objects, see the section below on complex accessors.

Complex accessor

Syntax: ...[expression]

Gets attribute at expression from the parent object. Works for both objects and lists. If parent object is a list, expression is expected to evaluate to an INTEGER. If expression evalutes to a negative value it's used to index relative to the end of the list. expression can be a simple primitive or a complicated value. Note that you can apply a complex accessor to the root payload by prefixing it with a dollar sign $. This is useful if the top-level keys in your payload do not conform to the definition of an identifier as stated above. Examples include integer keys and keys with special characters.

List range accessor

Syntax: ...[start:stop] | ...[start:stop] | ...[start:] | ...[:stop]
Arguments:
    start: expression (must evaluate to INTEGER)
    stop: expression (must evaluate to NULL or INTEGER)

Selects all elements from a parent list / text starting at start (inclusive) and ending at end (exclusive). If start or stop is negative it is used as an index relative to the end of the list. Evaluation fails with an error if any element of the range expression is not INTEGER or NULL type. Examples:

Given Payload Response

{
  "user": {
    "name": "John Doe",
    "age": 30,
    "emails": ["[email protected]", "[email protected]"],
    "address": {
      "street": "123 Main St",
      "city": "Anytown",
      "zipcode": "12345"
    }
  },
  "items": [10, 20, 30, 40, 50],
  "12months": ["Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"],
  "special keys": {
    "!@#$%^&*()": "secret"
  }
}

Examples

Using .identifier

To access the name of the user:

user.name

Expected Result:

"John Doe"

To get the city from the address:

user.address.city

Expected Result:

"Anytown"

Complex Accessor

To access the last email of the user:

user.emails[-1]

Expected Result:

"[email protected]"

To get a month using an expression:

["12months"][2]// Getting March, assuming January is 1

Expected Result:

"Mar"

To access a value with a non-standard key:

["special keys"]["!@#$%^&*()"]

Expected Result:

"secret"

List Range Accessor

To get the first three items:

items[:3]

Expected Result:

[10, 20, 30]

To select a range of months (from March to May):

["12months"][2:5]

Expected Result:

["Mar", "Apr", "May"]

To get the last three months of the year:

["12months"][-3:]

Expected Result:

["Oct", "Nov", "Dec"]

Constants

A constant value can be denoted in DSL as:

"'ABC'"

Expected Result

"ABC"

When working in JSON make sure to wrap in single quotes '' with double quotes ""

Membership

The IN operator is used to determine if the left operand is contained within the right operand. This operation is versatile and can be applied to various types of data on the right side, including text, lists, and objects. The interpretation of "membership" varies depending on the data type of the right operand:

• Text: The left operand must be a text string that is found within the right text string. If the left operand is not text, the operation will result in an error.
• List: The left operand must be an element present in the right list.
• Object: The left operand must be a key existing in the right object.

Should the right operand be of any type other than these specified, the operation will fail with an error.

Examples

Text Membership

Checking if a character is part of a string:

"a" IN "cat"// Returns: TRUE
"b" IN "cat"// Returns: FALSE

Attempting to check membership of a non-text type in a text string:

1 IN "cat"// Results in: ERROR

List Membership

Verifying if an element is in a list:

1 IN [1, 2, 3]// Returns: TRUE

Checking if a list is an element of another list (which is not the case here):

[1] IN [1, 2, 3]// Returns: FALSE

Object Key Membership

Determining if a value is a key in an object (note that it checks for keys, not values):

"v" IN {"k": "v"}// Returns: FALSE

Invalid Membership Check

Attempting to perform a membership check where the right operand is not a valid type for this operation:

1 IN 1// Results in: ERROR

These examples illustrate how the IN operator functions across different data types, providing a flexible means to check for the presence of an item within various data structures.

Lambdas

Lambdas are powerful keywords that allow you to run programming functions, significantly enhancing the expressiveness of our DSL rules. They can be called in two ways, as a regular function f(x) or a method x.f(). Functions are prefixed with a $ sign (e.g., $LOWERCASE("hElLo). Below is a catalog of the available DSL lambda functions with corresponding examples.

Stream Operators

MAP

Description: Applies a mutator function to all values within a container (e.g., list, object), potentially transforming each item based on the mutator function provided.

Output Type: any (The type of the output will match the type of the input container, with the values mutated as specified by the operator function.)

Parameters:

• Items: The container whose values will be mutated. This can be a list, an object, or container type. (Type: any)
• Operator: A function that takes each value (and optionally a key or index) from the container and returns a mutated version of that value. This function defines how each item in the container is transformed.
  • Val: The current value being processed in the container. (Type: any)
  • Key: The current key or index associated with the value in the container. This parameter is optional and may not be applicable for all container types. For example, Lists would not work - usually used for Object/Dict types (Type: any)

Example Usage:

Suppose you have a list of integers and you want to square each number:

Given the payload response:

{"numbers": [1, 2, 3, 4]}

To apply the MAP function to square each number:

$MAP(numbers, (val) => val * val)

Expected Result:

[1, 4, 9, 16]

Or, if you have an object and you want to append "_modified" to each value:

Given the object:

{"fruits": {"a": "apple", "b": "banana"}}

To modify each value in the object:

$MAP(fruits, (val) => val + "_modified")

Expected Result:

{"a": "apple_modified", "b": "banana_modified"}

Using .method Mode

Suppose you have a list of strings representing names, and you want to convert each name to uppercase:

Given the list:

{"names": ["john", "jane", "doe"]}

To apply the MAP function in .method mode to convert each name to uppercase:

names.$MAP(name => name.$UPPERCASE())

Expected Result:

["JOHN", "JANE", "DOE"]

Using the key Parameter

Let's consider an example with an object where you want to append the key to each of its values:

Given the object:

{"fruits": {"fruit1": "apple", "fruit2": "banana"}}

To modify each value in the object by appending its key:

fruits.$MAP((val, key) => val + "_" + key)

Expected Result:

{"fruit1": "apple_fruit1", "fruit2": "banana_fruit2"}

FILTER

Description: Filters items in a container (such as a list or an object) based on specified criteria. Based on a boolean(true/false) condition, the filter function determines which items should be included in the output.

Output Type: any (The type of the output matches the input container type but only includes items that meet the filter criteria.)

Parameters:

• Items: The container whose items will be filtered. This can be a list, an object, or any other container type. (Type: any)
• Operator: A function that evaluates each item (and optionally a key or index) against a condition. Items that satisfy the condition (i.e., for which the function returns true) are included in the output.
  • Val: The current value being processed in the container. (Type: any)
  • Key: The current key or index associated with the value in the container. This parameter is optional and may not be applicable for all container types. (Type: any)

Regular Usage Based on a Payload Response

Example 1: Filtering a List

Given the payload response:

{"numbers": [1, 2, 3, 4, 5]}

To filter out all numbers greater than 2:

$FILTER(numbers, num => num > 2)

Expected Result:

[3, 4, 5]

Example 2: Filtering an Object

Given the payload response:

{"items": {"a": 10, "b": 20, "c": 5}}

To filter out entries with values greater than 10:

$FILTER(items, (val) => val > 10)

Expected Result:

{"b": 20}

Advanced Usage

Using .method Mode

Given the payload response:

{"words": ["apple", "banana", "cherry", "date"]}

To filter out words longer than 5 characters:

words.$FILTER(word => word.$LENGTH() > 5)

Expected Result:

["banana", "cherry"]

Using the key Parameter

Given the payload response:

{"data": {"key1": "value1", "key2": "value2", "specialKey": "specialValue"}}

To filter out entries where the key contains "special":

$FILTER(data, (val, key) => !key.$CONTAINS("special"))

Expected Result:

{"key1": "value1", "key2": "value2"}

Given the payload response:

{"readings": {"temp1": 22, "reading1": 45, "temp2": 25, "reading2": 50}}

To filter out entries where the key starts with "temp":

$FILTER(readings, (val, key) => !$STARTS_WITH(key, "temp"))

Expected Result:

{"reading1": 45, "reading2": 50}

ALL

Description: Evaluates whether all elements within a given container (such as a list or an object) meet specified criteria. If an operator is not provided, a default truthy check is performed on each element.

Output Type: boolean

Parameters:

• Items: The container whose elements will be evaluated. This can be a list, an object, or any other container type. (Type: any)
• Operator: A function that evaluates each element (and optionally a key or index) against a condition, returning true if the condition is met. If omitted, a default truthy check is performed.
  • Val: The current element being processed in the container. (Type: any)
  • Key: The current key or index associated with the element in the container. This parameter is optional and may not be applicable for all container types. (Type: any, Default: null)

Regular Usage Based on a Payload Response

Example 1: Checking a List of Numbers

Given the payload response:

{"numbers": [1, 2, 3, 4, 5]}

To check if all numbers are greater than 0:

$ALL(numbers, num => num > 0)

Expected Result:

true

Example 2: Checking an Object for Value Conditions

Given the payload response:

{"scores": {"math": 80, "science": 90, "history": 85}}

To check if all scores are above 75:

$ALL(scores, score => score > 75)

Expected Result:

true

Using .method Mode

Given the payload response:

{"words": ["apple", "banana", "", "date"]}

To check if all words are non-empty:

words.$ALL(word => word.$LENGTH() > 0)

Expected Result:

false

Using the key Parameter

Given the payload response:

{"data": {"key1": true, "key2": true, "specialKey": false}}

To check if all keys start with "key":

$ALL(data, (val, key) => key.$STARTS_WITH("key"))

Expected Result:

false

ANY

Description: Evaluates whether any elements within a given container (such as a list or an object) meet specified criteria. If an operator is not provided, a default truthy check is performed on each element.

Output Type: boolean

Parameters:

• Items: The container whose elements will be evaluated. This can be a list, an object, or any other container type. (Type: any)
• Operator: A function that evaluates each element (and optionally a key or index) against a condition, returning true if the condition is met for any element. If omitted, a default truthy check is performed.
  • Val: The current element being processed in the container. (Type: any)
  • Key: The current key or index associated with the element in the container. This parameter is optional and may not be applicable for all container types. (Type: any, Default: null)

Regular Usage Based on a Payload Response

Example 1: Checking a List of Numbers

Given the payload response:

{"numbers": [0, -1, -2, -3, -4]}

To check if any number is greater than 0:

$ANY(numbers, num => num > 0)

Expected Result:

false

Example 2: Checking an Object for Value Conditions

Given the payload response:

{"scores": {"math": 70, "science": 65, "history": 60}}

To check if any score is above 75:

$ANY(scores, score => score > 75)

Expected Result:

false

Using .method Mode

Given the payload response:

{"words": ["apple", "banana", "", "date"]}

To check if any word is non-empty:

words.$ANY(word => word.$LENGTH() > 0)

Expected Result:

false

Using the key Parameter

Given the payload response:

{"data": {"key1": false, "key2": false, "specialKey": true}}

To check if any key starts with "special":

$ANY(data, (val, key) => key.$STARTS_WITH("special"))

Expected Result:

true

String Analysis

STARTS_WITH

Description: Determines whether a given text value starts with a specified prefix.

Output Type: boolean

Parameters:

• Text: The text to be checked for the presence of a prefix. (Type: text)
• Prefix: The prefix to look for at the beginning of the text. (Type: text)

Example Usage:

Given a string, you want to check for a specific starting sequence:

{"filename": "report_2023.pdf"}

To check if the filename starts with "report":

Using Method Syntax:

filename.$STARTS_WITH("report")

Using Function Syntax:

$STARTS_WITH(filename, "report")

Expected Result:

true

ENDS_WITH

Description: Determines whether a given text value ends with a specified string.

Output Type: boolean

Parameters:

• Text: The text to be checked for the presence of the ending string. (Type: text)
• Prefix: The string to look for at the end of the text. (Type: text)

Example Usage:

Given a string, you want to check for a specific ending sequence:

{"filename": "report_2023.pdf"}

To check if the filename ends with ".pdf":

Using Method Syntax:

filename.$ENDS_WITH(".pdf")

Using Function Syntax:

$ENDS_WITH(filename, ".pdf")

Expected Result:

true

MATCH

Description: Searches for all occurrences of a regex pattern within a given text and returns a list of matches found.

Output Type: list of strings

Parameters:

• Text: The text in which to search for the pattern. (Type: text)
• Pattern: The regex pattern to check against the text. (Type: text)
• Case Insensitive: Specifies whether the regex pattern matching should be case-sensitive or not. Defaults to false (case-sensitive). (Type: boolean, Default: false)

Example Usage:

Given a paragraph from which you want to extract all the email addresses:

{"content": "For support, contact [email protected] or visit our website at http://example.com. For sales, reach out to [email protected]."}

To find all email addresses within the content using a simple regex pattern:

Using Method Syntax:

content.$MATCH("[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\\.[a-zA-Z]{2,}")

Using Function Syntax:

$MATCH(content, "[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\\.[a-zA-Z]{2,}")

Expected Result:

["[email protected]", "[email protected]"]

If you're dealing with a case where the case sensitivity of the pattern is important, you can adjust the case_insensitive parameter accordingly. For example, to match words that start with "Ex" or "ex":

Given:

{"text": "Example text with exclusive content starting with Ex and ex."}

To find all occurrences of words starting with "Ex" or "ex":

Using Method Syntax:

text.$MATCH("\\bEx", true)

Using Function Syntax:

$MATCH(text, "\\bEx", true)

Expected Result:

["Example", "exclusive", "Ex", "ex"]

String Formatting

LOWERCASE

Description: Converts a given string to lowercase.

Output Type: text

Parameters:

• Text: The string to be converted to lowercase. (Type: text)

Example Usage:

Given the payload response:

{"name": "JoHn DOe"}

You can implement the LOWERCASE function in two ways:

Using Method Syntax:

name.$LOWERCASE()

Using Function Syntax:

$LOWERCASE(name)

Expected Result:

"john doe"

UPPERCASE

Description: Converts a given string to uppercase.

Output Type: text

Parameters:

• Text: The string to be converted to uppercase. (Type: text)

Example Usage:

Given the payload response:

{"name": "JoHn DOe"}

You can implement the UPPERCASE function in two ways:

Using Method Syntax:

name.$UPPERCASE()

Using Function Syntax:

$UPPERCASE(name)

Expected Result:

"JOHN DOE"

TITLECASE

Description: Converts a given string to title case, where the first letter of each word is capitalized.

Output Type: text

Parameters:

• Text: The string to be converted to title case. (Type: text)

Example Usage:

Given the payload response:

{"name": "john doe"}

You can implement the TITLECASE function in two ways:

Using Method Syntax:

name.$TITLECASE()

Using Function Syntax:

$TITLECASE(name)

Expected Result:

"John Doe"

ENCODE_BASE64

Description: Encodes a given string in base64 format.

Output Type: text

Parameters:

• Text: The string to be encoded in base64. (Type: text)

Example Usage:

Given the payload response:

{"name": "Hello World"}

You can implement the ENCODE_BASE64 function in two ways:

Using Method Syntax:

name.$ENCODE_BASE64()

Using Function Syntax:

$ENCODE_BASE64(name)

Expected Result:

"SGVsbG8gV29ybGQ="

CURRENCY_FORMAT

Description: Formats a currency value for a given denomination, correctly placing decimal points and adding the currency symbol as per the denomination code.

Output Type: text

Parameters:

• Quantity: Numerical value of the currency. For USD, this should be in cents. (Type: decimal)
• Denomination: Denomination code (e.g., USD, CAD). (Type: text)

Example Usage:

Given the payload response:

{"amount": 123456}

And you wish to format this amount as USD:

You can implement the CURRENCY_FORMAT function in two ways:

Using Method Syntax:

amount.$CURRENCY_FORMAT("USD")

Using Function Syntax:

$CURRENCY_FORMAT(amount, "USD")

Expected Result:

"$1,234.56"

String Manipulation

CONCAT

Description: Concatenates a set of text values into a single string, optionally using a specified separator and allowing for the exclusion of empty strings.

Output Type: text

Parameters:

• Items: A list of strings to be combined. (Type: list of text)
• Separator: Text to place between items when concatenating. Defaults to an empty string if not specified. (Type: text, Default: '')
• Skip Empty: Whether to exclude items which evaluate to empty text from the concatenation. Defaults to false. (Type: boolean, Default: false)

Example Usage:

Given the payload response:

{"words": ["hello", "world", "", "john", "doe"]}

To concatenate these words with a space as a separator and skipping empty strings:

Using Method Syntax:

words.$CONCAT(" ", true)

Using Function Syntax:

$CONCAT(words, " ", true)

Expected Result:

"hello world john doe"

If you choose not to skip empty strings and use a comma as a separator:

Using Method Syntax:

words.$CONCAT(",", false)

Using Function Syntax:

$CONCAT(words, ",", false)

Expected Result:

"hello,world,,john,doe"

SPLIT

Description: Splits a text value into a list of text values based on a specified delimiter text.

Output Type: list of text

Parameters:

• Text: The string to be split into a list. (Type: text)
• Separator: The delimiter text used to split the string. (Type: text)

Example Usage:

Given a string that you want to split into words:

{"sentence": "hello world john doe"}

To split this sentence into a list of words using a space as the separator:

Using Method Syntax:

sentence.$SPLIT(" ")

Using Function Syntax:

$SPLIT(sentence, " ")

Expected Result:

["hello", "world", "john", "doe"]

If you have a string with a different separator, like commas:

Given:

{"data": "apple,orange,banana,grape"}

To split this data into a list of fruit names using a comma as the separator:

Using Method Syntax:

data.$SPLIT(",")

Using Function Syntax:

$SPLIT(data, ",")

Expected Result:

["apple", "orange", "banana", "grape"]

TRIM

Description: Removes leading and trailing whitespace from a given text value.

Output Type: text

Parameters:

• Text: The string from which leading and trailing whitespace will be removed. (Type: text)

Example Usage:

Given a string with unnecessary leading and trailing spaces:

{"name": "  John Doe  "}

To remove these spaces and clean up the string:

Using Method Syntax:

name.$TRIM()

Using Function Syntax:

$TRIM(name)

Expected Result:

"John Doe"

TRUNCATE

Description: Truncates a text value to a specified maximum length. If the text exceeds this length, it is cut off and ends with "..." to indicate truncation. Optionally handles Unicode characters properly if specified.

Output Type: text

Parameters:

• Text: The string to be truncated. (Type: text)
• Max Length: The maximum allowed length of the text before truncation. (Type: int)
• Handle Unicode: Specifies whether to handle Unicode characters properly (1) or not (0). Defaults to false (0). (Type: int, Default: false)

Example Usage:

Given a long piece of text that needs to be truncated:

{"quote": "In a galaxy far, far away, an empire was defeated by a group of rebels."}

To truncate this quote to a maximum length of 25 characters:

Using Method Syntax:

quote.$TRUNCATE(25)

Using Function Syntax:

$TRUNCATE(quote, 25)

Expected Result:

"In a galaxy far, far..."

If handling a string with Unicode characters and wanting to ensure proper truncation:

Given:

{"emojiText": "Hello, world 🌍🚀"}

To truncate this text to 10 characters, considering Unicode properly:

Using Method Syntax:

emojiText.$TRUNCATE(10, 1)

Using Function Syntax:

$TRUNCATE(emojiText, 10, 1)

Expected Result:

"Hello, wor..."

REPLACE

Description: Replaces occurrences of a pattern within a text value with a specified substitution. This function supports regular expressions (regEx) for pattern matching and can optionally perform case-insensitive replacements.

Output Type: text

Parameters:

• Text: The text value in which certain expressions will be replaced. (Type: text)
• Pattern: The regular expression used to identify values that need to be replaced. (Type: text)
• Sub: The value with which to replace the matched expressions. (Type: text)
• Case Insensitive: Specifies whether the matching should be case-sensitive or not. Defaults to false (case-sensitive). (Type: boolean, Default: false)

Example Usage:

Given a text that contains some misspelled words:

{"message": "Hello, this is an exampel. An exampel of a common misspelling."}

To correct the spelling of "exampel" to "example":

Using Method Syntax:

message.$REPLACE("exampel", "example")

Using Function Syntax:

$REPLACE(message, "exampel", "example")

Expected Result:

"Hello, this is an example. An example of a common misspelling."

If you want to replace "Hello" with "Hi" regardless of case sensitivity:

Given:

{"greeting": "hello Hello hEllo"}

To replace all variations of "hello" with "Hi", making the operation case-insensitive:

Using Method Syntax:

greeting.$REPLACE("hello", "Hi", true)

Using Function Syntax:

$REPLACE(greeting, "hello", "Hi", true)

Expected Result:

"Hi Hi Hi"

Datetime Manipulation

NOW

Description: Retrieves the current time as a text value, with options to format the timestamp, specify the timezone, and adjust the time with an offset.

Output Type: text

Parameters:

• Output Format: The format string specifies how the timestamp should be formatted. If not provided, a default format is used. (Type: text, Default: null)
• Timezone: The timezone to which the timestamp should be converted. If not provided, UTC is used as the default timezone. (Type: text, Default: null)
• Offset: The offset from the current time in seconds. This allows for adjustments to the time, such as getting the time from a certain number of seconds ago. For example, an offset of -600 would return the time 10 minutes ago. (Type: int, Default: null)

Example Usage

1. Getting the Current Time in Default UTC Format

To simply get the current time in the default format and UTC timezone:

$NOW()

Expected Result:

"2024-07-16T12:34:56Z"

2. Specifying a Custom Output Format

To get the current time formatted as "YYYY-MM-DD HH:mm:ss":

$NOW("%yy-%m-%d %H:%M:%S")

Expected Result:

"2024-07-16 12:34:56"

3. Adjusting Timezone

To get the current time in Eastern Standard Time (EST):

$NOW(null, "EST")

Expected Result:

"2024-07-16T07:34:56"

4. Getting Time with an Offset

To get the time 10 minutes (600 seconds) ago:

$NOW(null, null, -600)

Expected Result:

"2024-07-16T12:24:56Z"

TIMECONV

Description: Converts a time value from one format to another, with options for specifying input and output formats, input and output timezones, and handling conversion errors.

Output Type: text

Parameters:

• Value: The initial time value to be converted. (Type: any)
• Input Format: The format string indicating the initial format of the timestamp. Defaults to ISO format if null, and attempts to auto-detect if not specified. (Type: text, Default: null)
• Output Format: The format string for the transformed timestamp. Defaults to ISO format if null. (Type: text, Default: null)
• Input Timezone: The initial timezone of the timestamp. If not specified, assumes the timestamp is in UTC. (Type: text, Default: null)
• Output Timezone: The final timezone to which the timestamp will be converted. If not specified, the output will be in UTC. (Type: text, Default: null)
• Strict: Indicates whether the function should throw an error if any format or timezone conversion operations fail. (Type: boolean, Default: true)

Example Usage

1. Basic Conversion with Default Formats and Timezones

Given an initial time value:

"2024-07-16T12:34:56Z"

To convert this timestamp with default settings:

$TIMECONV("2024-07-16T12:34:56Z")

Expected Result:

"2024-07-16T12:34:56Z"

(Note: The result remains the same since default formats and timezones are used.)

2. Specifying Input and Output Formats

To convert a time value from "MM/DD/YYYY HH:mm:ss" format to "YYYY-MM-DD" format:

$TIMECONV("07/16/2024 12:34:56", "%m/%d/%y %H:%M:%S", "%Y-%m-%d")

Expected Result:

"2024-07-16"

3. Timezone Conversion

To convert a timestamp from Eastern Standard Time (EST) to Pacific Standard Time (PST):

$TIMECONV("2024-07-16T12:34:56", null, null, "EST", "PST")

Expected Result:

"2024-07-16T09:34:56Z"

4. Converting input to a different output

To convert a time value “MM/DD/YYYY” format to “MM-DD-YYYY” without specifying an input format

$TIMECONV("07/16/2024", NULL, "%Y-%m-%d")

Expected Result:

"07-16-2024"

Type Casting

TEXT

Description: Converts any given value to its text (string) representation.

Output Type: text

Parameters:

• Val: The value to be converted to text. (Type: any)

Example Usage:

To convert a numerical value to text:

$TEXT(1234)

Expected Result:

"1234"

INTEGER

Description: Converts any given value to its integer representation.

Output Type: int

Parameters:

• Val: The value to be converted to an integer. (Type: any)

Example Usage:

To convert a text value to an integer:

$INTEGER("5678")

Expected Result:

5678

DECIMAL

Description: Converts any given value to its decimal (floating-point) representation.

Output Type: decimal

Parameters:

• Val: The value to be converted to a decimal. (Type: any)

Example Usage:

To convert a text value to a decimal:

$DECIMAL("123.45")

Expected Result:

123.45

LENGTH

Description: Returns the size of a string, list, or object. Returns 0 for null values. It throws an error if passed a value of any other type.

Output Type: int

Parameters:

• Val: The value for which to evaluate the length. (Type: any) - You can do a list of elements or a string in most cases

Example Usage:

To get the length of a string:

$LENGTH("Hello World")

Expected Result:

11

To get the length of a list:

$LENGTH(["apple", "banana", "cherry"])

Expected Result:

3

Approximation Comparators

LIST_CONTENTS_EQUAL

Description: Evaluates whether two lists contain exactly the same elements in any order. This function is useful for comparing lists where the sequence of elements is not important, but their presence is. It checks for equality of contents without considering the order of the items.

Output Type: boolean

Parameters:

• Val1: The first list to be compared. (Type: list, Item Type: any)
• Val2: The second list to be compared. (Type: list, Item Type: any)

Example Usage

1. Comparing Lists with Identical Contents

Given two lists:

["apple", "banana", "cherry"]
["cherry", "banana", "apple"]

To check if these lists have identical contents:

$LIST_CONTENTS_EQUAL(["apple", "banana", "cherry"], ["cherry", "banana", "apple"])

Expected Result:

true

This result indicates that the two lists contain the same elements, regardless of their order.

2. Comparing Lists with Different Contents

Given two lists:

["apple", "banana", "cherry"]
["cherry", "banana", "grape"]

To check if these lists have identical contents:

$LIST_CONTENTS_EQUAL(["apple", "banana", "cherry"], ["cherry", "banana", "grape"])

Expected Result:

false

This result indicates that the two lists do not contain the same set of elements.

3. Comparing Lists with Different Lengths

Given two lists:

["apple", "banana", "cherry", "date"]
["cherry", "banana", "apple"]

To check if these lists have identical contents:

$LIST_CONTENTS_EQUAL(["apple", "banana", "cherry", "date"], ["cherry", "banana", "apple"])

Expected Result:

false

This result indicates that the lists do not have the same contents, as their lengths are different.

4. Comparing Lists with Repeated Elements

Given two lists with repeated elements:

["apple", "banana", "banana"]
["banana", "apple", "banana"]

To check if these lists have identical contents:

$LIST_CONTENTS_EQUAL(["apple", "banana", "banana"], ["banana", "apple", "banana"])

Expected Result:

true

This result indicates that the two lists contain the same elements, including the correct count of repeated elements, regardless of their order.