Functions in Jitterbit App Builder

When writing expressions (particularly when creating Rules), there are various functions that can be used to join, calculate, and otherwise manipulate the data within the expression. This page shows the currently available functions.

Database functions

Database Functions are Functions that are translated to a SQL native function and are therefore evaluated by the database engine. Database Functions have access to information in the Data Layer, including tables, columns, and other database functions.

Database Functions are primarily configured in the Business Logic Layer, specifically in the Column or Expression field when building out the logic for a Business Rule.

• Abs: Returns the absolute value of a number.
• Average: Returns the average (mathematical mean) of the values in a group. Ignores null values.
• Cast: Converts an expression to another data type.
• Char: Converts an int ASCII code to a character value.
• Concat: Returns a string that is the result of concatenating two or more string values (add spaces manually).
• ConcatWithDash: Adds the second parameter to the end of the the first separated with a dash.
• ConcatWithParentheses: Adds the second parameter to the end of the the first in parentheses.
• ContainsAlpha: Determines whether an expression contains an alphabetic character (a-z). Returns 1 or 0.
• Count: Returns the number of items in a group.
• DateAdd: Adds a certain number of days, months, or years to a date.
• DateDiff: Determines how much time has elapsed between a given start and end date.
• DateName: Returns a part of a date or date/time value with an English string representation for days and months.
• DatePart: Same as DateName but returns the appropriate integer for a month or day.
• DenseRank: Sequentially numbers each row within a set, ordered by one or more columns, starting at 1. This function requires the ORDER BY modifier and also supports the PARTITION BY modifier.
• Differ: Checks if both arguments match, if they do, App Builder outputs one of them, if they don't App Builder formats it so it looks like Value -----> New Value
• EscapeIfRequired: Escapes a table or column name, if needed.
• Expression: Allows the designer to specify their own function expression for aggregates
• FromOffSet: Returns the number of minutes for an offset argument.
• FromTimeZone: Converts a DATETIME at a given time zone to UTC.
• GroupConcat: Allows you to aggregate a string column in the context of a group by, for example creating a comma delimited list.
• HasFlag: Evaluates an expression to determine if the field has a flag.
• IIF: Returns one of two values, depending on if the Boolean expression evaluates to true or false.
• IsNull: Returns an alternate value for a null entry, or NULL if all arguments are null.
• IsNumeric: Returns a Boolean value indicating whether an expression can be evaluated as a number.
• Left: Returns a given number of characters from the left side of a supplied text string.
• Length: Returns the number of characters of the specified string, excluding trailing blanks.
• Lower: Returns a lowercase string.
• Lpad: Causes a string to be a given length.
• Max: Returns the largest value in a given list.
• Min: Returns the smallest value in a given list.
• NewUUID: Generates a new, randomized UUID for every entry in a table.
• Now: Returns the current date and time.
• Null: Returns a null value Example: IIF(Status='Delete',Null, Status)
• Position: Finds the position of a substring within a string.
• Power: Returns the value of a number raised to another, where both of the numbers are passed as arguments.
• Rank: Sequentially numbers each row within a set, ordered by one or more columns, starting at 1.
• RowNumber: Sequentially numbers each row within a set, ordered by one or more columns, starting at 1. This function requires the ORDER BY modifier and also supports the PARTITION BY modifier.
• RemoveSpaces: Removes all spaces from a string.
• Replace: Replaces all occurrences of a specified string value with another string value.
• Right: Returns a given number of characters from the right side of a supplied text string.
• Rj0: Causes a string to be a given length by padding the beginning of a string with the number 0.
• Round: Rounds a number up or down, using a second argument to determine how many places to the right(+) or left(-) of the decimal point to round to.
• Rpad: Returns a copy of the string that is right-padded to the total number of characters that the length argument specifies.
• Sqrt: Returns the square root of a number.
• Substring: Pulls out a part of a string.
• Sum: Returns the sum of all the values.
• Timestamp: Takes a string that is a time and/or date and converts it to a timestamp.
• ToOffSet: Returns an offset for the minutes argument.
• ToTimeZone: Converts a DATETIME at UTC to the given time zone.
• Trim: Trims leading and trailing whitespace from a string.
• Type: Provides information about an expression's type without changing the expression itself.
• Upper: Returns a character expression with lowercase character data converted to uppercase.
• UUID: Native support for UUID, standardizing it across vendors.
• VisibilityOption: Provides the ability to use named Visibility Values in an expression.
• Weekday: Returns weekday of the date.
• Workday: Evaluates whether given date is a workday.

Runtime functions

Runtime Functions are Functions that are evaluated during runtime, whereas Database Functions are translated to a SQL native function and are evaluated by the database engine. Runtime Functions run in the Business Layer and are therefore unable to reference information stored in the Data Layer, such as table columns and other database functions.

Runtime Functions can not be used with Migration Rules. Migration Rules run at the data layer, and Runtime Functions run at runtime. For example, you cannot use the Enum Function in a Migration Rule because the function is not able to access the table and do the required PK to Enum string substitution by design.

Runtime Functions are primarily configured in the Business Logic Layer. Runtime Functions can be used in the Column or Expression field when building out the logic for a Business Rule.

• App: Returns the application identifier as a UUID in which the event is executing.
• Caller: Returns value of column from the row which called the event.
• Changed : Reports if a column value was modified at the start of the event.
• Claim: Returns a claim from a security provider.
• Client: Returns client information such as ipaddress, timezone, timezone offset, locale etc.
• Enum: A user-defined data type that consists of integer constants.
• Event: Returns information about the current event execution.
• Generated: Retrieve a generated value during the Event by the business engine.
• Memory: Returns value of column from Shared() function if exists otherwise from the Caller() function.
• Page: Returns the Page Id.
• RandomBytes: Generates random bytes on the server, and returns them as a hex string.
• Realm: Returns the identifier of the security realm in which the event is executing.
• Role: Determines if the current user is a member of a given Role.
• Session: Returns the identifier for the current user's session.
• Shared: Returns the shared link criteria.
• Version: Provides access to App Builder's current version information.
• Who: Returns information about the logged in user based on the parameter specified.