Transform Script to Create new record in different table.

Nirmala B · ‎05-21-2020

I have Transform Map to upload bulk data to TableB. There is a reference field in TableB which will hold unique number from TableA.

In OnStart : Create record to TableA and take unique number and map that number to TableB while transformation is happening (OnBefore)

Ankur Bawiskar · ‎06-01-2020

Hi Nirmala,

I think you cannot use global variables declared in onStart transform script to be used in the onBefore as per below link

https://hi.service-now.com/kb_view.do?sysparm_article=KB0676967

For your approach as all the imported records should have same value in Parent you can use below approach

1) use onComplete script so that it would run at the end when all transform happens

2) in that create a record in Table A

3) now query for all the target records from the import set table and update the field with the sys_id from step 2

Ensure you use setWorkflow(false) while updating to avoid triggering any business rule on Table B

Sample script below

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

var gr = new GlideRecord('TableA');
gr.initialize();
gr.u_name = false;
var qNumber =gr.insert();


var gr1 = new GlideRecord('import_set_table');
gr1.addQuery('sys_import_set', source.sys_import_set);
gr1.query();
while(gr1.next()){

var targetRecordSysId = gr1.sys_target_sys_id; // get the target record sys_id

var tableB = new GlideRecord('table B');
tableB.addQuery('sys_id', targetRecordSysId);
tableB.query();
if(tableB.next()){

tableB.<table_A_field> = qNumber;
tableB.setWorkflow(false);
tableB.update();
}		

}


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

Mark ✅ Correct if this solves your issue and also mark 👍 Helpful if you find my response worthy based on the impact.
Thanks
Ankur

Regards,
Ankur
✨ Certified Technical Architect || ✨ 9x ServiceNow MVP || ✨ ServiceNow Community Leader

View solution in original post

Brad Tilton · ‎05-21-2020

I believe onStart runs at the beginning of the overall import, but I think you want to run it as an onBefore, which will run before each row is inserted, assuming you want this to run for each row in the import. You can also have the import just auto-create the fields in the referenced table, but you wouldn't be able to set the u_name field.

Nirmala B · ‎05-21-2020

I need not want to create new record on each row insertion. If a file holds 10 records, for all 10 records there should be one qnumber created and updated in targetfield.

Nirmala B · ‎05-21-2020

Is there any chance of using scratchpad or session variables. I can create new record in OnStart and pass the qnumber to OnBefore event script as session variable.

Means passing or returning values from Onstart script to OnBefore script.

Community Alums · ‎05-21-2020

Hi Nirmala,

Amend your onStart code as below.

I am not sure if 'aqnumber' field on your 'TableB' is a reference field or a string, so I have written how to return both sys_id and number from 'TableA'. If reference field, map the sys_id. If string, map the number.

var qNumber = ''; //To store newly created record's number
var qSysID = ''; // To store newly created record's sys id

var gr = new GlideRecord('TableA');
gr.initialize();
gr.u_name = false;
var recSysID = gr.insert();  //Returns the sys_id of the newly created record

qSysID = recSysID; //This variable will hold the newly created record's sys_id

var getRec = new GlideRecord('TableA');
getRec.get(recSysID);
qNumber = getRec.number; //This variable will hold the newly created record's number