Using the Jitterbit Design Studio Formula Builder

Launch the Formula Builder

Launch the Formula Builder from inside a transformation mapping by either:

• selecting the Mapping to which you want to apply a formula, then double-click on the Box icon in the line showing the mapping relationship; or

• selecting an Existing Mapping to which you want to apply a formula, click once on the Box icon in the line showing the mapping relationship to activate the relationship, and then click on the Create Formula button in the bottom right toolbar.

The value of the mapped source data element that you selected is then loaded into the Formula Builder's upper pane:

Add a function or operator

To add a function, click in the script where you would like the function added, and then:

• Select the Function you want to use from the Functions tree in the left pane.

• Insert the selected function to your script by clicking the Insert button.

To add an operator (such as a logical or mathematical operator), click in the script where you would like the operator inserted, and then type the appropriate keys for the operator.

Modify expressions with source elements

You can use a source element's value to modify the script you are creating in the Formula Builder. For example, you may need to convert a currency value from one currency to another. You may have a source element called CurrentConversionRate that contains the most recent exchange rate between US dollars and Euros.

To use an additional source data element in your expression, select where the element should be added, select the desired source element (in this case, CurrentConversionRate) in the Source Tree pane, then click the Insert button. (Alternatively, you can double-click the desired source element as a shortcut to insert it into your script.)

The element will be added to your script at the insertion point. You can then add appropriate operators (such addition or multiplication) to complete the calculation you require.

Insert project items

You can insert project items—such as sources and targets of databases, file locations, LDAP locations, operations, scripts, email messages, Salesforce orgs, Fusion Lifecycle endpoints, and NetSuite endpoints—as arguments as required in a script through the Formula Builder.

In the desktop version of Design Studio, the available items are shown below the script area in the different folders of the Project Items tab, such as the Project Items > Databases > Source folder. For example, double-clicking on a source will insert the sourceId at the location of your cursor. The source ID is constructed as a string by wrapping the source path with opening and closing <TAG> ... </TAG> tags and opening and closing double-quotes, such as:

"<TAG>Sources/PostgreSQL</TAG>"

This can be used in a database lookup function, such as

<trans>
DBLookup("<TAG>Sources/PostgreSQL</TAG>",
  "SELECT id, company FROM customer WHERE state='AZ'");
</trans>

In a similar fashion, IDs for other project items and plugins (through the Plugins tab) can be added to your script as required. Only those plugins that can be run inside a script are displayed; plugins that run on Sources, Targets, and Web Service Calls are hidden.

Insert XML node paths

To enter an XML node path into a function, you can double-click on the desired XML node folder's label in the Source Tree to insert its qualified path at the location of your cursor.

Test the script

Click the Test button or Test tab to open the Formula Tester dialog. The Formula Tester is where you can test the results of your script by entering the values you would like to supply as input to the script. The resulting output will be displayed in the right pane of the dialog.

Breakpoints can be added to scripts by using the Jitterbit DebugBreak function as described in Formula builder debugging.

Save the script

When you have finished your script, click the OK button to accept the script and close the window.