Formula Editor Overview

In the Formula Editor use source fields and other information to search for or transform your data during an integration. Formulas can be simple or quite complex. Use the Formula Editor and the TIBCO Cloud™ Integration - Connect function library to create the formulas you need. The Formula Editor consists of four main panes.

Note: You can type simple formulas directly into Formula fields, however, for more complex formulas best practice is to use the Formula Editor.

Formula Editing Pane

Enter and edit formulas. Formulas can contain combinations of:

Use the and icons to expand and collapse the Formula Editing Pane.

Categories Pane

Displays entity names and categories of functions.

Tip: Select any property name, such as a field, entity, or function, to display the available description or help for that property.

Center Pane

The title of the center pane depends on the selection in the Categories pane:

For Source, Operation, or Function items:

Description Pane

Displays a detailed description of the item selected in the center pane.

Using The Formula Editor

Creating Or Editing A Formula

  1. Open the Formula Editor:
    • From Block Properties for a block in an On schedule flow, select the Fields tab.
    • In the Target pane, select a field or an existing formula.
    • Either type a formula into the field or select the Formula icon .
  2. The Formula Editor displays.
    • If you selected a linked field, the source field name displays in the formula editing pane.
    • If you selected an unlinked field, the formula editing pane is blank.
  3. In the Categories pane, you can select:
    • An entity name from the Source list.
    • All functions or a category of functions from the Functionslist.
  4. In the center pane, select the Insert icon next the name of the function or source entity field that you want to include in the formula. The source field or function displays in the editing pane.
    Note: Select any field name or function to display a detailed description.
  5. Enter the remaining parameters, operators, comments, and other functions as needed. See Formula Editor Operators and Adding Comments.
  6. Validate your formula:
    • After creating a formula, select Validate to ensure that the syntax is correct.
    • If needed, make changes to correct the formula and then select Validate again.
  7. When you are done, select OK to save your formula and close the Formula Editor.

Rules For Entering Formulas

Note: For information about any property, such as a field or formula name, within the Formula Editor workspace, select that property in the center pane to display a description or help for the property in the Description pane.

Adding Comments

When writing formulas, it can be useful to add comments to, for example, turn features on and off while testing or to provide information for other users.

Text in comments is not processed by TIBCO Cloud™ Integration - Connect and is ignored when you select Validate.

TIBCO Cloud™ Integration - Connect uses the following comment formats:

// Use two slashes for a one line comment.
/* Enclose multi-line comments within a slash
and an asterisk */

Using Quotation Marks In Formulas

Validating Formulas

Validating a formula checks the syntax of your formula.

Select Validate from the Formula Editor.

Note: Validation only checks the syntax; a formula that has passed validation may not return the expected results.

Entering Boolean Values

When entering a formula that uses Boolean values, use Null for null, True for Boolean true, and False for Boolean false.

Using Special Characters Within String Literals

When you use certain special characters in a literal string, you can specify how TIBCO Cloud™ Integration - Connect processes them.

To have TIBCO Cloud™ Integration - Connect process escape sequences, such as \t (tab), \r\n (newline), or a double quotation mark (") within a literal string, you must begin the string with a dollar sign ($).

For example, the following formula, where AddressLine1 and AddressLine2 contain an address:

MyEntity.AddressLine1 & $"\r\n" & MyEntity.AddressLine2

Returns:

150 Dow Street
Suite 543A

The same formula, without the $ in front of the escape sequence:

MyEntity.AddressLine1 & "\r\n" & MyEntity.AddressLine2

Returns:

150 Dow Street\r\nSuite 543A

To represent a double quote in a constant, you must preface the text with a $ and two double quotes ($"") rather than backslash quote (\"). For example:

$"""Eat at Joe's"" Restaurant" 

Returns:

"Eat at Joe's" Restaurant.