Fields

Extracted data are stored in fields in the Data Model (see The Data Model). Fields can be present on different levels: on the record level or in a detail table (see Detail tables).
They can be moved up or down and put in groups (see below).

Fields with data belong to an Extract step and are accessible via the Step Properties pane (see Extract step properties).
Location-based fields always contain extracted data: they read data from a certain location in the data source.
Other fields may contain the result of a JavaScript (expression-based fields, or fields filled by an Action step) or the value of a property (property-based fields).

Adding fields

Location-based field

Generally, location-based fields are added to a Data Model by extracting data; see Extracting data. Location-based fields in detail tables are created by extracting transactional data; see Extracting transactional data.

Alternatively, you can add fields and detail tables directly in the Data Model pane (see Adding empty fields via the Data Model pane).
After adding a field or detail table this way, you can drag & drop data into it to convert it into a location-based field. Without data it is not accessible via the Step properties pane.

Expression-based field

Expression-based fields are filled with the result of a (JavaScript) expression: the script provides a value. Note that the last value attribution to a variable is the one used as the result of the expression.

There is a number of ways to add an expression-based field.

Via the Steps pane

  1. Make sure there is no data selection in the Data Viewer.

  2. Right-click on an Extract step on the Steps pane and select Add a Step > Add Extract Field. (To add a new Extract step, select Add a Step > Add Extraction first.)

  3. On the Step properties pane, under Field Definition, enter the script in the Expression field.

Via the Step properties pane

  1. Select an Extract step on the Steps pane. (To add a new, empty Extract step, right-click the Steps pane and select Add a Step > Add Extraction.)

  2. On the Step properties pane, under Field Definition, click the Add JavaScript Field button next to the Field List.

  3. On the Step properties pane, under Field Definition, enter the script in the Expression field.

By changing a field's mode

Alternatively you can change a location-based field into a JavaScript-based field.

  1. Select the field in the Data Model.

  2. On the Step properties pane, under Field Definition, change its Mode to JavaScript.

  3. Enter the script in the Expression field.

Tip: The default extraction method for fields in a CSV or XLS(X) file is data.extract(columnName, rowOffset). Changing the expression to use the data.extractByIndex(index, rowOffset) method will allow the data mapping configuration to extract data from files that have the same structure, but different column names.

Property-based field

A property-based field is filled with the value of a property (see Properties and runtime parameters).

Custom properties can be added via the Preprocessor step; see Preprocessor step.

A property-based field cannot be added directly. To fill a field with the value of a property, you have to change an existing field's Mode to Properties.

  1. Select the field in the Data Model.

  2. On the Step properties pane, under Field Definition, change its Mode to Properties.

  3. Select the property from the Property drop-down list, or click the button to the right, to open a filter dialog that lets you find a property based on the first few letters that you type.

Another way to add the value of a property to a field is by setting the field's Mode to JavaScript and entering the corresponding property in the Expression field, e.g. data.properties.myProperty;.

Adding empty fields via the Data Model pane

You can add fields and detail tables directly in the Data Model pane. Right-click anywhere on the Data Model and a contextual menu will appear. Which menu items are available depends on where you've clicked. If you right-click inside the record itself, you can add a field, detail table or group.

For a field, you can add a default value (see Setting a default value).

A field will be added with no extraction, while a detail table will be added with no fields inside.

Here's how you can fill the field with data.

  • Drag & drop data into it. The field will be converted into a location-based field and is then accessible via the Step properties pane.

  • Fill the field with an Action step (see Extracting data with an Action step script). Fields that are filled this way are not accessible via the Step properties pane.

Adding fields dynamically

Outside of the DataMapper the Data Model cannot be changed. It isn't possible to add fields to it when using the data mapping configuration in an automated process. It is however possible to add data to existing fields via Workflow; see Adding fields and data via Workflow.

Editing fields

The list of fields that are included in the extraction, the order in which fields are extracted and the data format of each field, are all part of the Extract step's properties. These can be edited via the Step properties pane (see Extract step properties).

Tip: To change the name of a field quickly, right-click it in the Data Model and select Rename.

Renaming and reordering fields in an extraction step

The names of fields, as well as the order of fields in an extraction step, can be changed via the properties of the Extract step that they belong to.

  1. Select the Extract step that contains the fields that you want to rename. To do this you could click on one of those fields in the Data Model, or on the step in the Steps pane.

  2. On the Step properties pane, under Field Definition, click the Order and rename fields button.

  3. Now you can rename and order the fields; see Order and rename fields dialog.

Note: About field names:

  • Fields cannot have the same name, unless they are located on a different level in the record (e.g. root level, detail table).
  • It is recommended that you do not use spaces in field names.
    When a data field is dragged into a template, the data field name is used in the selector of the script that is created, but any spaces will be removed.
    Also, data fields may be used as Metadata in a Workflow process, and spaces are not permitted in Metadata field names.
  • It is recommended that you do not use special characters (" \ / ' £ $ & @ ~ #) in data field names. Special characters are not supported in a placeholder (e.g. @fieldname@) that is used as a selector for a script in a Designer template.

Note: The order of fields in an extraction step may be different from their order in the Data Model. To make the Data Model clearer and more manageable, you can change the order of the fields in it and create groups of fields. See also: Ordering and grouping fields in the Data Model.

Setting the data type

Fields store extracted data as a String by default. The data type of a field can be changed via the properties of the Extract step that the field belongs to.

  1. Select the Extract step that contains the field. You can do this by clicking on the field in the Data Model, or on the step in the Steps pane that contains the field.

  2. On the Step properties pane, under Field Definition, set the Type to the desired data type. See Data types for a list of available types.

Changing the type does not only set the data type inside the record. In the case of dates, numbers and currencies, it also means that the DataMapper will expect the data in the data source to be formatted in a certain way. If the actual data doesn't match the format that the DataMapper expects, it cannot interpret the date, number or currency as such. If for example a date in the data source is formatted as "yyyy-mm-dd" but the default format adds the time, the date cannot be read and the DataMapper will stop with an error.

The default format for dates, numbers and currencies can be set in the user preferences (DataMapper preferences), in the data source settings () and per data field (in the Extract step properties, see Data Format).

Setting a default value

You may want to set a default value for a field, in case no extraction can be made. Make sure to set the data type of the field via the step properties (see above). Then right-click the field and select Default Value.
The default value must match the selected data type. If the data type of the field is set to Integer, for example, you cannot enter a value of 2,3. A default date must be formatted as a DateTime object ("year-month-day" plus the time stamp, for example: 2012-04-11 12.00 AM); see Date. The default value of a JSON field can be an object or an array; see JSON.

Modifying extracted data

To modify extracted data - the contents of a field - you have to write a script. The script can be entered as a Post function in a location-based field or as an Expression in a JavaScript-based field.

Post function

On the Step properties pane, under Field Definition, you can enter a script in the Post function field to be run after the extraction. (Click the Use JavaScript Editor button to open the boundaries dialog if you need more space.)
A Post function script operates directly on the extracted data. Its results replace the extracted data. For example, the Post function script replace("-", ""); replaces the first dash character that occurs inside the extracted string. The code toLowerCase(); converts the string to lowercase letters.
Note that the function must be appropriate for the field's data type.

Tip: Click and drag theResize Post function icon Resize icon at the bottom right of the Post function area to make it larger.

JavaScript Expression

Alternatively you can change a field's Mode from Location to Javascript:

  1. Select the field in the Data Model.

  2. On the Step properties pane, under Field Definition, change its Mode to JavaScript.

You will see that the JavaScript Expression field is not empty; it contains the code that was used to extract data from the location. This code can be used or deleted.

Note: The last value attribution to a variable is the one used as the result of the expression.

Deleting a field

The list of fields that are included in an extraction is one of the properties of an Extract step. To delete a field:

  1. Select the field: click on the field in the Data Model, or select the Extract step that contains the field that you want to delete, and in the Step properties pane, under Field Definition, select the field from the Field List.

  2. In the Step properties pane, under Field Definition, click the Remove Extract Field button next to the Field List drop-down.