Jitterbit Script language in Jitterbit Design Studio

The Jitterbit scripting language can be used in all types of scripts within Jitterbit, including scripts created as project items within operations as well as scripts within transformation mappings. See Create a script for instructions on creating a script and using the script builder in Jitterbit.

Syntax

Each Jitterbit script is always surrounded by a set of <trans>...</trans> tags. This applies to scripts created as project items, as well as to "Formula Builder" scripts created within transformation mappings.

Within the <trans>...</trans> tags, "//" marks the start of a comment, and affects the text to the end of that line. Comments will not be part of the script that is run or the transformed result.

For example, a comment on a single line might look like this:

<trans>
// This is a comment
DbLookup(...)
</trans>

You can also use a block-style comment:

<trans>
/* This is a multi-line comment
This line is also a comment
This is the final line of the comment */
DbLookup(...)
</trans>

In addition, Jitterbit supports regular expressions as means of specifying and recognizing strings of text, including particular characters, words, or character patterns. Jitterbit supports the regular expression syntax from the Perl 5 programming language.

<trans>
RegExMatch(<input>,<expression>,...)
</trans>

For more information, see http://www.boost.org/doc/libs/1_49_0/libs/regex/doc/html/boost_regex/syntax/perl_syntax.html.

Functions and data elements

A Jitterbit script consists of built-in functions, data elements, and logic to be executed, separated by a semi-colon (;) characters. The Jitterbit script language has few control structures, but has many functions specifically designed for data manipulation.

Functions

All of the built-in Jitterbit functions that are available under the Functions tab when creating a script are available for reference under Formula builder.

Global data elements

You can access all global data elements under the Data Elements tab when creating a script. These include global variables, project variables, and Jitterbit variables. See Global variables and Project variables for more information. Jitterbit variables are documented individually under Jitterbit variables.

Global data elements are accessed either by typing a $ sign before the element name or by using the Set and Get functions.

Variable names can be composed from the letters (a-z, A-Z), numbers (0-9), period, and underscore characters. Other characters are not permitted. (Note that in the case of local variables, the period character is not allowed.)

This example references a global data element using $ to create a global dictionary:

<trans>
$org.lookup.currency=Dict();
$org.lookup.currency[1]="USD";
$org.lookup.currency[2]="EUR";
</trans>

Here, a global data element to another global data element using the Set and Get functions:

<trans>
Set("op2", Get("op"));
</trans>

Note that for certain Jitterbit variables, because they describe data elements that include a hyphen, a character not allowed for variable names, you must reference them by using either the Set or Get functions:

<trans>
Get("jitterbit.source.http.response.header.Content-Type");
</trans>

Source data elements

If you are using a script created within the transformation, you also have access to source data elements. Source data elements are used to reference source data and are referred to by their paths in the source tree. More information is provided under Create a script.

This example assigns a source data element to a global data element:

<trans>
$org.calculate.operand1=root$transaction.request$body$Calculate$input.Operand1$;
</trans>

Local data elements

You can also use local data elements, also known as local variables, that you create and use within the same script. See Local variables for more information. Characteristics of local data elements are as follows:

• A local data element is defined and used only within the expression; once its value is assigned in the expression, the local data element is deleted. Therefore, conflict between a local data element's value in the present expression and those from other expressions is not a concern.
• The local data element cannot be defined or retrieved by the set()/get() functions.
• RunScript() can pass arguments to the script, e.g. RunScript(SCRIPT_ID, 5, "abc",...). Values in the script can be assigned to predefined local variables _1, _2... In this example _1 represents the integer value of 5, while _2 represents the string value "abc". The local variable must be defined before it can be referenced, except in the case of the input arguments _1, _2,... described above.
• The ArgumentList() function is available for redefinition of a list of local data elements as input arguments.

Data types

All source data elements and global data elements that are not null have a type associated with them. Jitterbit supports the following data types: Integer, Long, Float, Double, Date, String, Boolean, Timespan, Bit, Binary, and Array. Data element types can be changed using the functions in the Conversion category.

Arrays

An array is a collection of data elements. Each member in the collection can be of any supported type, including arrays. The members of an array can be accessed using the Get() and Set()methods or using the [] array syntax. Arrays are zero-indexed, and indices are numeric, sequential, and cannot be skipped.

You can also create arrays of global variables. An array global variable is an array of other global variables that in turn can be arrays.

Set an array

You can set values in an array using the Set() method with this syntax:

type Set(string name, type value [, int index1, int index2, ...])

This sets the value of the global variable with the given name to value, and returns the value. If the first argument is an array or the name of an array data element, you can set the value of an array element by specifying its index (or indices for multi-dimensional arrays) as the third argument.

Not all the items in an array have to be of the same type. For example, you can create an array that holds a date, an integer, and a string. You can even create arrays inside other arrays.

This example creates an array with three elements of different types where each entry represents the current date and time:

<trans>
$right_now = Now();
Set($now, $right_now, 0);
Set($now, Long($right_now), 1);
Set($now, String($right_now), 2);
</trans>

As arrays are zero-indexed, the first element is at index 0 and the last element is at index (size -1). To append data to an array, pass either a negative index value or the size of the array (Length($arr)). Setting an element with an index larger than the size of the array results in an index out-of-range error. Setting non-array data elements can also be done using the $de_name syntax.

Here are examples of setting values in an array:

// Set the n:th entry in an array to the string "value"
Set($arr, "value", n-1);

// Another way to set the n:th entry an array
Set($arr, "value", Length($arr));

// Sets the value to a new element at the end of the array
Set($arr, "value", -1);

// Set the n:th entry of the m:th array
Set($record_set, "value", m-1, n-1);

Access an array

You can access the items of an array using the Get() method:

type Get(string name[, int index1, int index2, ...])

This returns the value of the global variable with the given name. If the first argument is an array or the name of an array data element, you can get a specific element by specifying its index (or indices for a multi-dimensional array such as a record-set) as the second argument.

Arrays are zero-indexed. To access the n:th element of an array called "arr", use Get("arr", n-1). For multi-dimensional arrays you need to specify all the indices. To access the n:th column of the m:th row in an array called ResultSet you would use Get("ResultSet", m-1, n-1). Attempting to get an element beyond the end of the array will result in an array out-of-range error.

These are examples of retrieving values from an array:

// Return the third array element
Get($arr, 2);

// Another way to return the third array element
Get("arr", 2);

// Get the n:th element of the m:th array in arr
Get($arr, m-1, n-1);

This example shows how you can first create a script that builds and returns an array. The second block shows running that script and assigning its returned value to a variable.

<trans>
// Script to build and return an array
a = Array();
a[Length(a)] = "A";
a[Length(a)] = "B";
a[Length(a)] = "C";
a;
</trans>

<trans>
// Call the script to retrieve the array
$Arr = RunScript("<TAG>Scripts/Build Array</TAG>";
</trans>

Certain Formula builder functions return arrays. For example, SelectNodesFromXMLAny()returns the results of an XPath query as an array. The DbExecute() function returns a record set as a two-dimensional array: rows first, then columns. This example returns the selected data as an array of arrays (representing the selected rows and columns):

<trans>
$resultSet = DbExecute("Project Name/Sources/Database Name", "select Result from SimpleCalculatorResults");
$firstRow = Get($resultSet, 0);
$thirdColumnOfSecondRow = $resultSet[2][3];
$secondColumnOfThirdRow = Get($resultSet, 3, 2);
</trans>

Dictionaries

In Jitterbit, a dictionary is a special type of global variable array that holds key-value pairs. The steps and functions are:

1. Initialize the dictionary using the Dict function:

   $d = Dict();
   

2. Load data with a key and a value:

   $d['4011'] = 'Banana';
   $d['4063'] = 'Tomato';
   

3. Check if the key already exists in the dictionary using the Haskey function:

   HasKey($d,'4011'); // Returns true
   

   In the example, we have already loaded the key '4011' so HasKey would return true.

   HasKey($d,'4089'); // Returns false
   

   If the key were not already loaded, for example '4089' the HasKey would return false.

4. Look up the value in the dictionary by passing the key and getting back the value:

   $d['4011']; // Returns 'Banana'
   

   In this example, the value returned would be Banana.

With dictionaries, keep these characteristics in mind:

• The scope of dictionaries is limited to the workflow. For example, if an operation loads a dictionary with 10,000 records, only those operations that are linked using On Success or On Failure paths—or with the RunOperation() function—will have access to that dictionary. However, if an operation uses chunking and threading, and has a transformation that populates a dictionary, that dictionary will be inconsistent. This is because Jitterbit does not take values assigned to variables by multiple operation threads and concatenate them into a single value set. This is true for all global variables and arrays. To avoid this issue, use the default chunking/threading values when building an operation that populates dictionaries.
• Dictionaries, because they use a binary search, are very fast at finding keys and returning values. A key can usually be found within five to six tries. In contrast, compare that search to the work required to loop through a 10,000 record array to find a specific key.
• Dictionaries are written such that they do not materially impact available server memory for processing.

Operators

This is a summary of the operators supported by Jitterbit script. Jitterbit script will attempt to convert the arguments to enable the operation. If this is not possible, an error will be reported.

Arithmetic

Logical

Comparison

Use the following operators to compare the values of two arguments of the same data type:

Negation

!IsNull($org.workorder.id)

Array

\(a={"London","Paris","New York"};<br/>\)b={{"John",25},
    {"Steve",32},
    {"Daniel",26}
   };
$c={"\"quoted\"", '"quoted"'};

Escape sequences

Jitterbit recognizes these escape sequences when used in literal strings:

Literal strings have to be surrounded by double quotes (") or single quotes (').

The surrounding quote must be escaped using a backslash if it is used inside the string. Backslashes may need escaping if they are used with any of the escape sequence characters. For example:

$str = "String with line break.\nThat's the last line.";

$str = 'Tony "The Gun" Marcello';

$str = "Tony \"The Gun\" Marcello";

$path = "C:\\tacos"

Control structures

The Jitterbit Script language does not include control statements such as if or while loops. Instead, you can use Jitterbit functions to achieve the same functionality. See the Case, If, and While functions under Logical functions. The Eval function under General functions can be used as a "try-catch" statement.