Formula Editor

The Formula Editor enables you to define formulas to apply to values generated through defined links. Formulas enable you to reformat and transform both source and target values. For example, you can compare two values to determine if the values are exact matches, or you can identify blank or empty values.

The Formula Editor supports:

To open the Formula Editor, in the Workbench, do one of the following:

A formula allows you to transform and reformat data during an integration. Examples include:

To Create a formula

  1. Enter or edit the formula in the edit box using any combination of values, functions and operators. .

    If this function acts on a source field, enter the source reference number (such as S1) of the field you want to transform into the formula editing pane.

    Note:  
    • Any connection that you use in a formula and that is neither a Source nor in the Rejected Row table must be added to the DTS as a Target.
    • If a connection is used as both a Source and a Target, the source connection displays in the Data Objects explorer as MyConnection:SOURCE. In this case, Insight creates separate connections for the source and target; Insight acts on the target connection by default. Labeling the source allows you to be sure that your function is acting against the expected data. To have a function act on a source field, select it from the Data Objects explorer.
  2. In the Function Browser, scroll through the available functions. You can view functions either by category or alphabetically by name.

    Note: If you do not see your custom functions listed, verify the associated DLLs are in the Scribe working folder.

  3. Highlight a function to display its syntax and a brief description.
  4. Double-click the function to insert it in the editing pane. If you entered a source reference, the function displays in the editing pane with the source reference number in the correct location in the formula.

    Note: You can create compound formulas that include more than one function.

  5. Enter the remaining parameters and other functions as needed. Click an operator underneath the editing pane to insert it into the formula editing pane.

    If the formula contains a field reference value, hovering your mouse over the field reference value displays the source field name as a tooltip.

  6. Use the Show Source Fields and Show Data Objects buttons to display current source and target data objects and fields for reference.
  7. When you are done, click OK to save your formula and close the formula editor.

Note: Alternatively, you can type a formula in the Formula Editor workspace, up to 1,024 characters.
For information about correctly formatting formulas, see the Formula syntax examples.

To modify a user formula

  1. In the Formula Editor, highlight the formula you want to modify.
  2. Click Edit User Formula. The User Defined Formula dialog box appears.

To delete a user formula

  1. In the Formula Editor, highlight the formula you want to delete.
  2. Click Delete User Formula. A message prompting you to confirm that you want to delete the selected formula.
  3. Click Yes.

Other Formula Editor Features

In addition to the formula editor, the Edit Formula dialog box allows you to:

See also

Creating Custom Functions

Editing Functions

Editing Multiple Formulas

Formula Syntax Examples

Functions

Operators and Functions

System Variables

User Variables