Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

Launching the Formula Builder

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

  • Span
    classinline-image
    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
  • Span
    classinline-image

    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:

Image Modified

Notes: 

  • The Source Tree appears in the right pane of the Formula Builder only when you are mapping a transformation, not when you are scripting an operation.
  • Caution: In order for a Jitterbit script to run and the data to be transformed correctly, the script must be wrapped with <trans> ... </trans> tags. Make sure that these tags are preserved as you manipulate your expression(s).

Adding 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.
  • Span
    classinline-image

    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.

Note: If you are in Business Analyst mode, clicking the Insert button will invoke a wizard for the function, if available.

Modifying 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.

Note: The Source Tree appears in the right pane of the Formula Builder only when you are mapping a transformation, not when you are scripting an operation.

Inserting 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:

Code Block
"<TAG>Sources/PostgreSQL</TAG>"

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

Code Block
<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.

Inserting 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.

Note: The Source Tree appears in the right pane of the Formula Builder only when you are mapping a transformation, not when you are scripting an operation.

Testing 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.

Saving the Script

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

Panel
borderColor#8C9CB4
titleColor#FFFFFF
titleBGColor#8C9CB4
titleSearch in This Topic
Page Tree Search
rootPageFormula Builder
Panel
borderColor#65379B
titleColor#FFFFFF
titleBGColor#65379B
titleOn This Page
Table of Content Zone

Table of Contents
maxLevel3
minLevel2
indent20px

Panel
borderColor#00B886
titleColor#FFFFFF
titleBGColor#00B886
titleRelated Topics
Content by Label
showLabelsfalse
max1000
showSpacefalse
sorttitle
cqllabel = "formula-builder-overview" and space = currentSpace()

HideElements
metastrue

Last updated: 

Lastmodifieddate