There are 7 type of transform map script which gets executed at different point of time while transformation,

1. onStart: executes at the start of an import before any rows are read.

2. onAfter: executes at the end of a row transformation and after the source row has been transformed into the target row and saved.

3. onBefore: executes at the start of a row transformation and before the row is transformed into the target row.

4. onChoiceCreate: executes at the start of a choice value creation before the new choice value is created.

5. onComplete: executes at the end of an import after all rows are read and transformed.

6. onForeignInsert: executes at the start of the creation of a related, referenced record before the record is created.

7. onReject: executes during foreign record or choice creation if the foreign record or choice is rejected. The entire transformation row is not saved.

Note : In addition to above types, we do have main transform map script (which is available when we click on run script checkbox) and Field Mapping script.

The correct sequence of transform map script is as follows:
1. onStart.
2. on Before.
3. Source Field script.
4. Run script, the script we can find after checking the 'Run Script' check box.
5. onAfter
6. onComplete

Below diagram represents graphical representation of execution order:

The coalesce option allows you to update existing target table records when transforming import data.

The coalesce option on a field map allows you to specify if the selected Target field should be used to coalesce on when import set records are transformed. If the field map Coalesce checkbox is selected, when the import set row is transformed the instance checks for an existing record in the target table that has the same value in the Target field as the import set row Source field. If an existing record with a matching value in the target table is found, that record is updated. If no matching record is found, then a new record is created in the target table.

Yes, we can apply coalesce on multiple fields. If we add coalesce for multiple field then all fields that are marked 'true' must match to do an update of an existing record.

In transform map main script, we can check for operation by using 'action' variable and set 'ignore' variable as true/false based on the value as shown in below script :

if(action=='insert'){
ignore=true;
}

1. source: Contains the import source record currently being transformed. Specify a specific field from the source record as an object property.

2. target : Contains the transformation map record currently being used for the transformation process. Specify a specific field from the transform map record with one of these properties.

3. log : Log information about the current import process. Each log level has it\'s own method.

4. ignore : When set to true, skips or aborts the current import action. In onStart scripts, this variable aborts the entire transformation process. In onBefore scripts, this variable only skips the current row being transformed.

5. error : When set to true, aborts the current import action and logs an error message in the Import Set Log.

6. error_message : When an error occurs, adds the specified error message to SOAP response.

7. status_message : Adds the specified status message to SOAP response.

A foreign record insert occurs when updating a reference field on a target table. If reference field doesn't exist then transform map creates new entry in reference table. This behaviour can be managed by 'Choice Action' field on field mapping record.