How functions work in transformations, variables and call conditions

You reach them through the Edit transformation of assigned value dialog, and the same engine is used everywhere transformations appear.

Every transformation runs in two phases, always in this order:

1. The primary expression is evaluated once, producing a value.
2. A chain of transformation actions is then applied to that value, one after another.

[data] is the placeholder for “the value currently being worked on”. After the expression runs, [data] holds its result. As each action runs, [data] is updated to that action’s output and handed to the next action.

What you can reference

Inside an expression (and inside most action fields) you can paste several kinds of things. The toolbar buttons Paste column, Paste variable and Paste value — or the right-click menu — insert the correct notation for you.

Notation	Refers to
[My field name]	A source column in DataETL and File Management transformations.
[<my variable>]	A Variable you have defined.
[<#my auto generated variable>]	An auto-generated Variable. Identical to a normal Variable — the # prefix just marks that it was generated rather than created by you.
[data]	The current value — type-independent, updated as the action chain runs.
[original]	The value returned by the primary expression, before any transformation action changed it.

[data] moves with the chain; [original] never changes once the expression has run. Use [original] in a later action when you need the pre-action value back.

Referencing columns inside a For Each

A ForEach component generates a Variable for each of its source columns at the moment you save the component. Inside the ForEach you can therefore reference a source column two ways:

• By its generated Variable — [<#my auto generated variable>]
• By the source-column notation — [#source columns]

Both resolve to the same value.

The destination decides the type

The result of a transformation is written into a destination whose datatype is fixed to one of Text, DateTime or Number. Your expression (and the final value after all actions) must produce that type. A Number destination will not accept a value the engine cannot read as a number, and so on.

[data] itself is type-independent — it simply carries whatever the current value is. If you assign a value to a destination column or a destination variable, [data] represents that value.

Phase 1 — the primary expression

The expression is where functions do their work: If(), Pos(), Dec(), Char() and the rest. The full list, with signatures and examples, is behind the Expression documentation button. Use Test expression to try an expression against a test value you type for the [data] placeholder before committing it.

An expression cannot contain a literal double quote ("). When you need to emit one, produce it with Char(34) — Char() can output the " character even though you cannot type it directly in the expression.

Right-click: paste, functions and comments

Right-clicking inside the expression editor opens a context menu that is often faster than the toolbar:

• Search and replace — find/replace within the expression text itself.
• Paste [data] — inserts the [data] placeholder.
• Paste function — a submenu of every built-in function, grouped by category: Logical, String, Numeric, Date and Time, File handling, Folder, Other, SQL and SharePoint. Picking one drops its skeleton into the editor with the argument names shown, e.g. Mid( 'string', start {, length } ) or IndexValue( 'sourcestring', 'delimiterstring', indexnumber ). Arguments shown in { } are optional.
• Paste Column… / Paste Variable… — same as the toolbar buttons.
• Comment / Uncomment — toggle comment markers on the selected line(s) so you can disable part of an expression without deleting it.
• Standard Copy, Cut, Paste, Delete.

A few String functions worth knowing early: Char(34) for a double quote, GenerateGUID(), JSONDecode() / JSONEncode(), ExtractMetadataColumn('metadata','column name','default value'), and SkipString('My reason') to skip the value with a logged reason. The complete, categorised list lives in the function reference.

Using a SQL SELECT as the expression

Tick SQL SELECT 1 row/column and the Manage Database Connections picker opens, where you choose any configured connection (SQL Server, MS Access, ODBC, or the Internal database). Your expression then becomes a single SQL SELECT that returns one column:

SELECT dbo.bindtest.col_customer_name FROM dbo.bindtest WHERE mycol = [data]

The only SQL keyword supported in expression is SELECT. And the SQL SELECT can only be used in the first primary expression.

Rules for the SQL SELECT form:

• The statement must return one column.
• If multiple rows come back, they are joined with %NewLine% and a maximum of 250 rows is processed.
• You can use [data], columns and Variables anywhere in the statement as arguments:

SELECT Max(MyCol) FROM MyTable WHERE abc = [<MyVar>] AND xyz = [data]

Phase 2 — transformation actions

Once the expression has produced a value, you add actions from the Add action menu. They run top to bottom, each receiving the previous action’s output as [data]. There are five:

Action	What it does
Data validation	Inspects the value and writes messages to the job session log; can halt the chain.
Expression	Runs another expression against the value.
Extract XML/JSON tag	Pulls the value(s) of named tags out of an XML/JSON string.
Lookup	Maps the value 1:1 through a Value Mapping List.
Replace occurrences	Search-and-replaces within the value using a Value Mapping List.

Data validation

Input type is String and the action must return a String beginning with one of these prefixes. Text after the colon is written to the job session log.

Prefix	Effect
Ok:	Value accepted. No row is added to the session log.
Warning:	Text after the colon is logged as a warning.
Error:	Text after the colon is logged as an error.
Ignore:	Text after the colon is logged; the value is ignored.
Return:	Stops further action evaluation.

Both [data] and [original] are available. Examples:

If( Dec('[data]') > 100.01 , 'Error:[data]>100.01' , 'Ok:' )
If( 'xyz' + [data] = 'xyz1' , 'Ok:' , 'Warning:[data]' )
If( Pos( '[data]' , 'Apple' ) > 0 , 'Ok:[data]' , 'Ignore:' )
If( Pos( '[data]' , 'Apple' ) > 0 , 'Ok:' , 'Return:[data]' )

Expression (action)

A second expression, but with one constraint the primary expression does not have: it must return the same data type it received. [data] is the current value, [original] the primary expression’s result.

Extract value from XML/JSON tag

Enter one or more tag names, separated by semicolons, and the action returns their value(s). Two options:

• When a tag is found, stop processing remaining actions.
• If tag is not found, then set value to empty string.

Lookup (Map 1:1)

Maps the current value through a Value Mapping List — a match in the list’s Search value column returns its Replacement value. Choose the list, or build one with New mapping list / Edit mapping values.

The If no mapping found setting controls the fallback:

Option	Behaviour when no match
<No mapping>	Leave the value unchanged.
<No mapping - Add to Mapping List>	Leave unchanged and add the value to the list.
<Map to blank>	Replace with an empty string.
Map to the value I type here	Replace with a fixed value you supply.

Tick When mapped, stop processing remaining actions to end the chain on a successful map.

Replace occurrences

Also driven by a Value Mapping List, but it replaces within the value rather than mapping the whole value. Two settings shape the behaviour:

Replacement scope — which matches are replaced:

• All occurrences
• All but first occurrence
• All but last occurrence
• Only first occurrence
• Only last occurrence

Value pairs — which replacement value is used when several list entries match:

• Use all matched replacement values
• Use first matched replacement value only
• Substitute with first matched replacement value
• Substitute with list of matched replacement values

Options: When replaced, stop processing actions and If no replacement, set the value to empty string.

Value Mapping Lists

Both Lookup and Replace occurrences draw on Value Mapping Lists, managed from Manage mapping lists. Each list holds Search value → Replacement value rows with an Order column.

You can maintain a list three ways:

• By hand — in the Edit Value Mapping List window (New, Copy, Delete, Save, Renumber sort order).
• From Excel — copy two columns (Search value and Replacement value) in Excel and use Paste from clipboard/Excel to load them in one go. Lists can also be exported to Excel or Notepad.
• From a job — DataETL can insert, update and delete mapping-list rows programmatically, so a list can be built or refreshed automatically as part of a run rather than only by hand.

Point DataETL at the Internal database to manipulate mapping-list values programmatically.

Expressions as call conditions in a Collection

The same expression engine also drives call conditions inside a Collection. A Collection groups multiple components (Steps) together as one — and a Collection can contain other Collections. Each component can carry a condition, set with Create Condition, that decides at run time whether that component is called.

A call condition is more restricted than a transformation. It:

• is a single expression — there is no chain of transformation actions, and no [data] placeholder;
• can reference Variables only — the [<MyVariable>] notation. There are no source columns in this context, so Paste Variable is the only paste button (alongside Test expression and Expression documentation).

The expression must return a string, and that string is a control keyword telling the job what to do with the component:

Return value	Effect
True	Run the component.
False	Skip the component.
ExitGroup	Skip this and the remaining components in the group.
ExitGroupWarning	Same as ExitGroup, but logs a warning.
StopJob	Stop the job now.
StopJobWarning	Stop the job now, logging a warning.

As with the Data validation prefixes, you can append a message after a colon and it is written to the job session log:

If( [<MyVariable>] > 0 , 'True' , 'False' )
If( [<MyVariable>] > 0 , 'True' , 'StopJob:Job cannot continue' )