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.
Other Formula Editor Features
In addition to the formula editor, the Edit Formula dialog box allows
you to: