Transform map to update sys_created_on

[rambo1]

Hi Team, 

I want to update 'sys_created_on' and 'state' field on vulnerable item table from transform map.

excel sheet has 'vulnerability'(string field), state, ci(string field), created(date field)

There is vulnerability and cmdb_ci fields on vulnerabile item record, if source vulnerability and ci matches with any of the records in vulnerable item table, state and created should be update.

So I created a import set table and transform map.

I could not add field map for created as I was not able to select sys_created_on in target.

So I have mapped, vulnerability and ci fields and made them coaelesce .

For created, I checked run script option und using  below script:

target.sys_created_on = new GlideDateTime(source.u_created);

When I am running transform map, iits creating records.I want to avoid creating new records,, if ci and vulnerability matches it should update sys_created_on and state from source.

How can I achieve  this ?

[Tony Chatfield1]

Hi, I suspect a couple of issues here.
If your transform is creating a new record then your coalesce configuration is incorrect, and the result is that the target field is not being identified\mapped so a new record is created, and you will need to resolve this first.

Then if you want to update system fields
Global default fields (servicenow.com)

For a globally scoped record you would need to utilize autoSysFields(false); in a before transform script
Server Global API | ServiceNow Developers

Something like this

	target.sys_updated_on = source.u_updated;
	target.setWorkflow(false);
	target.autoSysFields(false);

If you are trying to update a scoped table then it's a little more complicated and you would need to ensure your transform map is created in the same scope and then utilize GlideQuery in order to set values in system fields.
GlideQuery - Scoped, Global | ServiceNow Developers

[Community Alums]

Hi @rambo1 ,

It looks coalesce fields are not properly mapped, please double check it.

Also in your script you need to ensure that if no existing records found then you should abort the transaction for that particular record query by using setAbortAction(true);

Your Code snippet should look like this-

// Before transform script
(function runTransformScript(source, map, log, target) {
    // Disable auto system fields to allow updating sys_created_on
    target.autoSysFields(false);
    
    // Query to find matching records based on coalesced fields
    var gr = new GlideRecord('vulnerable_item');
    gr.addQuery('vulnerability', source.u_vulnerability);
    gr.addQuery('cmdb_ci', source.u_ci);
    gr.query();
    
    if (gr.next()) {
        // Update existing record
        target.sys_id = gr.sys_id;
        target.sys_created_on = new GlideDateTime(source.u_created);
        target.state = source.u_state;
    } else {
        // Prevent creation of new record if no match found
        target.setAbortAction(true);
    }
})(source, map, log, target);

If my response has resolved your query, please consider giving it a thumbs up ‌‌ and marking it as the correct answer‌‌!

Thanks & Regards,

Sanjay Kumar

[rambo1]

hi, target field is reference .. vulnerability anf cmdb_ci.

source fields are string fields. is this causing issue?