Extracting data with an Action step script

To extract data you normally use an Extract step. This is described in Extracting data, Extracting transactional data and Extracting data of variable length.
However, data is not always structured in a way that it can be extracted with an Extract step. For example, rows may not all have the same number of columns, transactional data may not always start at the same point in the line, etc.. In cases like that, it may be very well possible to extract the data with an Action step, in other words: with a script. This topic explains how.

Preparation

An extraction script in an Action step can fill one or more fields at the root of the record and/or in detail tables, but the script cannot change the Data Model. The required fields and detail tables - and fields therein - must therefore first be added to the Data Model manually.

Empty fields and data tables can be added via the contextual menu; see Adding empty fields via the Data Model pane.
Alternatively you could open a template, switch to the template and use the JSON Sample Data button (see Adding JSON sample data). Then switch back to the Data Mapping Configuration and click the Synchronize Fields and Structure button.
If a saved Data Model has all the required fields and tables, you can import it (see Importing/exporting a Data Model).

The data mapping workflow

Theoretically an Action step could fill all fields in the Data Model, but the ideal data mapping workflow will probably have more steps, like a Repeat step and a Goto step. It all depends on the data.
In any case you will need an Action step. Add it to the data mapping workflow and select it. Then, on the Step properties pane, click the Use JavaScript Editor button and you can start writing the script.

The script

To extract data and fill one or more fields with those data, the script in the Action step will make use of the data object and the record object.

The data object allows to extract data from the data file using the data.extract() method that matches the data file type; see extract().

The record object has methods to put the extracted data into fields. The methods to use are:

record.set().
This method sets values of fields at the root level in the current record; see set(record).
record.tables.<table name>.addRow().
This method adds a record to an existing detail table and returns the index of the new record; see addRow(record).
record.tables.<table name>[index].set().
This method sets field values in one of the records in a detail table; see set(record).

All three methods allow you to pass an object. The object must consist of properties that match field names in the Data Model, and values. For example:

{customerName : "John Doe", customerAddress : "123 Test Road"}

The properties of the object - here: customerName and customerAddress - can be a subset of the fields in the record or detail table.
The values of all properties present in the object are extracted to the respective fields in the Data Model, at the root level or in a detail table record, depending on the method used.

If a field or detail table doesn't exist, the script fails with an error.

With the addRow() function, passing an object is optional. When no object is passed, the function adds an empty record to the detail table.
This function always returns the index of the new record. The returned index can be used to retrieve the detail table record and set values in it.

Examples

Setting values at the root level

Here's an example of a script that sets the value of fields at the root level:

var myObject = {
   ContactFirstName: "John",
   ContactLastName: "Doe"
}
record.set(myObject);

(Of course, in reality the object will need to be filled using the data.extract() function.)

Adding a record with values to a detail table

The following script creates a new record in a detail table and sets the value of fields in it.

var index = record.tables.items.addRow(myObject);

Setting values in an existing detail table record

This script sets the value of the "Number" field in a certain detail table record. The record is retrieved using the index that was returned by the addRow() method.

record.tables.products[index].set({Number:42})

Extracting data from a Text file

This is a record in a Text file:

01|INV1867748|411350|CU63047838|2021-02-24
02|53674|Thule Railing M450|199.95|3|3|0|599.85
02|364678|Fischer Ski Boots|111.2|3|3|0|333.6

The data are separated with a "|" symbol, but cannot be matched per column with a field in the Data Model because the type of information and the number of columns differ per line.
Each line starts with a number indicating the type of information that it holds.
The line that starts with 01 needs to be extracted to data fields at the root level.
Lines starting with 02 hold transactional data that should be extracted to the 'products' detail table.
An Action step, combined with a Goto step (that moves the cursor down one line) in a Repeat step, could extract these data with a script like this:

var oneLine = data.extract(1,200,0,1,"").split("|");
switch (oneLine[0]){
	case "01":
		record.set({OrderNumber:oneLine[1],CustomerPO:oneLine[2],CustomerNumber:oneLine[3],Date:oneLine[4]}); 
		break;
	case "02":
		record.tables.products.addRow({Number:oneLine[1],Description:oneLine[2],UnitPrice:oneLine[3],Ordered:oneLine[4],Shipped:oneLine[5],BackOrder:oneLine[6],Total:oneLine[7]});
}