Every group of questions: IDE FAQ
How a transformation works
Section titled “How a transformation works”How does a transformation change a value?
In two phases. First the primary expression is evaluated once. Then the transformation actions run one after another on its result. [data] holds the current value as it moves through the actions, and [original] keeps the expression's result before any action changed it.
Read more: How functions work in transformations, variables and call conditions · Function reference
What can an expression refer to?
Source columns as [My field name], your Variables as [<my variable>], auto-generated Variables as [<#my auto generated variable>], and [data] and [original]. Paste column, Paste variable and Paste value, or the right-click menu, insert the right notation for you. Inside a ForEach, a source column is both its generated Variable and [#source columns].
Read more: How functions work in transformations, variables and call conditions
Which data type must a transformation return?
The type of its destination, which is fixed to Text, DateTime or Number: the expression, and the value after all actions, must produce it. An Expression action must also return the same type it received.
Read more: How functions work in transformations, variables and call conditions
How do I put a double quote in an expression?
With Char(34). An expression cannot contain a literal double quote, but Char() can output one.
Read more: How functions work in transformations, variables and call conditions · Char()
Can an expression look up a value in a database?
Yes: tick SQL SELECT 1 row/column and choose a connection — SQL Server, MS Access, ODBC or the Internal database. The expression is then one SELECT that returns one column, and can use [data], columns and Variables. Several rows are joined with %NewLine%, at most 250. It works only in the primary expression, and SELECT is the only SQL keyword allowed.
Read more: How functions work in transformations, variables and call conditions
Where do I find every function I can use?
In the function reference, grouped as Logical, Text, Date and Time, Numeric, File handling, JSON, Nextcloud App and SharePoint, each with its arguments and examples. In the expression editor, right-click → Paste function inserts any of them with its arguments named, and Test expression tries an expression on a value you type.
Read more: Function reference · How functions work in transformations, variables and call conditions
Validating and mapping values
Section titled “Validating and mapping values”How do I log a warning or an error for a value?
With a Data validation action. It returns a string that starts with Ok:, Warning:, Error:, Ignore: or Return:, and the text after the colon is written to the job session log — for example If( Dec('[data]') > 100.01 , 'Error:[data]>100.01' , 'Ok:' ). Ok: adds no row; Ignore: ignores the value; Return: stops the remaining actions.
Read more: How functions work in transformations, variables and call conditions
How do I map old values to new ones?
With a Value Mapping List of Search value → Replacement value rows. A Lookup action maps the whole value 1:1, and If no mapping found decides what happens otherwise; Replace occurrences replaces matches within the value. Lists are kept by hand, pasted from two Excel columns, or filled by a DataETL job through the Internal database.
Read more: How functions work in transformations, variables and call conditions
Call conditions
Section titled “Call conditions”How do I run a component only under a condition?
Give it a call condition in its Collection with Create Condition. It is a single expression that can refer to Variables only, and it returns True (run), False (skip), ExitGroup, ExitGroupWarning, StopJob or StopJobWarning. A message after a colon goes to the session log: If( [<MyVariable>] > 0 , 'True' , 'StopJob:Job cannot continue' ).
Read more: How functions work in transformations, variables and call conditions