Transformation script variables & Scripting in Transform Maps.

[Sanket Khabiya]

Scripting in Transform Maps :

Transform Scripts are just like Business Rules, there is no condition field, instead just a simple Type field that chooses when it should run:

• Before (onBefore) or after (onAfter) a record is inserted or updated in the target table.
• When the transform map starts processing (onStart) or when it stops (onComplete).
• When choices or reference values are created (onChoiceCreate, onForeignInsert, onReject). These are rarely used.

1. onStart : The onStart event script is processed at the start of an import run, before any data rows are read. 
2. onComplete : The onComplete event script is processed at the end of an import run, after all data rows are read and transformed.
3. onBefore : The onBefore event script is processed at the start of a row transformation, before the source row is transformed into the target row.
4. onAfter : The onAfter event script is processed at the end of a row transformation, after the source row has been transformed into the target row and saved.
5. onForeignInsert : The onForeignInsert event script is processed at the start of the creation of a related, referenced record, before the record is created.
6. onChoiceCreate : The onChoiceCreate event script is processed at the start of a choice value creation, before the new choice value is created.
7. onReject : The onReject event script is processed during the occurrence of a foreign record or choice creation, and the foreign record or choice is rejected, the entire transformation row is not saved.

Transformation script variables :

• source : Contains the import source record currently being transformed. Specify a specific field from the source record as an object property.
• target : Contains the import target record currently being inserted. Specify a specific field from the target record as an object property.
• map : 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.
• log : Log information about the current import process. Each log level has its own method.
• action : Specify the transformation action occurring on the target record. This value can be either "insert" or "update".
• 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.
• error : When set to true, aborts the current import action and logs an error message in the Import Set Log.
• error_message :When an error occurs, adds the specified error message to SOAP response.
• status_message : Adds the specified status message to SOAP response.

[AbhishekGardade]

Hello Sanket,

Thanks for elaborating about transform maps. I recommend you to add somthing about COALESCE.

Thanks,
Abhishek

[Rahul Mishra1]

@AbhishekGardade  

NO COALESCE

If no coalesce is set in the Transform Map, all data is inserted and there are no updates. This method is often used for one-time imports of data.

SINGLE FIELD COALESCE

Coalesce is set in the Transform Map.  It is just a true/false value between an source Import Set field and a target table field.  Coalesce on a single field, and ServiceNow will attempt to match source values to existing target values.  If there is a match, ServiceNow will update that record. If there is not a match, a new record is inserted.

This is the most common method to coalesce and should be used often to update records and not create duplicates.

MULTIPLE FIELD COALESCE

If multiple fields are set to coalesce, all coalesce values are used to match an existing record. If two fields are set for coalescing and a matching value is found on one of the coalescing fields but not on the other, a new record is inserted.

The important detail about this type of coalesce is that all coalesce fields must match in order to update a record.  

[HugoFirst]

Great article and also a great suggestion to add  details about Coalesce.

If you added an example of each type of script along with an explanation of its use-case, this article would belong on the top tier of all articles.   I know I've done several transform scripts but I struggled with onBefore scripts.  Your article makes me want to try it again.

[Hriday Modi]

Do we have any other values for action variable

like create or cancel or close