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:

- Unicode characters
- All ASCII control characters, except:
**Vertical Tab****Form Feed**

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

- Right-click a field and select
**Edit Data Formula**. - In the target pane, highlight a link and click Formula.
- Double-click the
**Formula**cell on the**Data Formulas**tab. - Double-click the target field on the
**Links**tab.

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

**Decimal conversion**— Converting dollars in thousands to whole dollars**Case conversion**— Converting fields in all capitals to initial capitalization**Parsing**— Creating first_name and last_name from full_name**Field substitution**— Globally replacing field information in all records**Conditional conversion**— Converting only when specified criteria are met

To Create a formula

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

- 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. - Highlight a function to display its syntax and a brief description.
- 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. - 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.

- Use the Show Source Fields and Show Data Objects buttons to display current source and target data objects and fields for reference.
- 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

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

To delete a user formula

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

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

- View and select data objects and source fields to use in formulas and variables
- Create and manage user variables
- Save a calculated variable
- Save a user formula
- Add a comment to an existing link
- Create, import, and export custom functions

See also