Transform map field value updates need restricting

[thrsdy1512]

Hi, I have some issues with a transform map I have created. The transform is being used to update existing records in a table with new data.

1. It lets me update fields with invalid values, eg if a field has a choice list, the transform will add my invalid data value to the choice list and input it to the field. I need there to be some sort of validation checks so that if the data value is NOT a valid choice for that field, it aborts the transform.

2. Where a record does not exist in the table, the transform is creating a new record. I want it to check if there is an existing record, if there is then continue with the update. If the record doesnt exist it should NOT create one.

[AbinC]

Hi @thrsdy1512 ,

By default, ServiceNow’s import process will add any new value to a choice field if it doesn’t already exist. To enforce validation and prevent invalid values from being added:

A. Use a Transform Script for Validation

• In your transform map, add an "onBefore" transform script to validate the incoming value against the allowed choices.
• If the value is not valid, you can abort the transform for that row.

Sample Script:

// Example for a field named 'u_status' var validChoices = []; var grChoice = new GlideRecord('sys_choice'); grChoice.addQuery('name', 'your_table_name'); // Replace with your table name grChoice.addQuery('element', 'u_status'); // Replace with your field name grChoice.query(); while (grChoice.next()) { validChoices.push(grChoice.value.toString()); } if (validChoices.indexOf(source.u_status.toString()) == -1) { // Invalid value, abort this row ignore = true; error_message = 'Invalid value for Status: ' + source.u_status; }

• This script checks if the incoming value exists in the list of valid choices. If not, it skips the row.

B. Set the Choice Field to "Create choice = false"

• In the dictionary entry for your choice field, set "Create choice = false".
• This prevents the import from automatically adding new choices.

---

2. Preventing Creation of New Records (Update Only)

By default, if a matching record is not found, ServiceNow will create a new one during a transform. To change this behavior:

A. Set the Transform Map to "Coalesce" on a Unique Field

• Ensure you have set a field (or fields) as Coalesce in your transform map. This tells ServiceNow to update existing records where the coalesce field matches, and only create new records if no match is found.

B. Prevent Insert When No Match is Found

• In your transform map, set the "Run business rules" option to false (optional, for performance).
• Add an "onBefore" transform script to check if the target record exists. If not, abort the insert.

Sample Script:

// Example for a coalesce field 'u_number' var gr = new GlideRecord('your_table_name'); gr.addQuery('u_number', source.u_number); // Replace with your coalesce field gr.query(); if (!gr.next()) { // No matching record, skip insert ignore = true; error_message = 'Record does not exist for update: ' + source.u_number; }

• This script checks if a record exists with the coalesce value. If not, it skips the row and does not create a new record.                                                                                                                                                                                            if you found this helpful please do mark it as helpful and accept as soloution.                  

[Bhuvan]

@thrsdy1512 

1. For choice field, when you do the mapping in transform map select ignore so that it will not create a new choice in sys_choice field

2. Create a onBefore transform script and use condition to check if operation is insert, ignore it

(function runTransformScript(source, map, log, target /*undefined onStart*/ ) {

// Add your code here
if (action == "insert")
ignore = true;

})(source, map, log, target);

https://developer.servicenow.com/dev.do#!/learn/courses/zurich/app_store_learnv2_importingdata_zuric...

https://www.servicenow.com/community/developer-forum/what-is-choice-action-field-in-transform-maps-u...

I hope you appreciate the efforts to provide you with detailed information. If my response helped to guide you or answer your query, please mark it helpful & accept the solution.

Thanks,

Bhuvan

[Ankur Bawiskar]

@thrsdy1512 

My thoughts

1) for the field map where you are mapping choice field set Choice Action = Ignore, what it does is if incoming choice doesn't match the target choices it doesn't override and create a new choice on target field

Choice action field for Field Map record in Transform Maps 

2) for your 2nd requirement you can use onBefore transform script and avoid insertion

(function runTransformScript(source, map, log, target /*undefined onStart*/ ) {

// Add your code here
if (action == "insert")
  ignore = true;

})(source, map, log, target);

If my response helped please mark it correct and close the thread so that it benefits future readers.