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

Swapnil Soni1 · ‎05-21-2020

Hi,

You can try this

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

var a= source.u_product_name.toString().split('\r'); //source data, multiple lines within one string field

for (var i = 0; i < a.length; i++){

var productStore = new GlideRecord('x_custom_product_table');

productStore.initialize();

productStore.product_name = a[i];

productStore.insert();

}

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

Please mark correct or helpful if this helps.

Thanks

Swapnil

DirkRedeker · ‎05-22-2020

Hi

Just put your script into the "Table Transform Map" Script (see screenshot):

BR

Dirk

DirkRedeker · ‎05-25-2020

Hi

Do you have an update on this?

If your issue was solved, please mark the correct answer as correct/helpful.

Thanks for marking.

BR

Dirk

Nirmala B · ‎05-26-2020

Hi Dirk,

I have tried but its creating records multiple time as per the data count in uploaded file.

If file has 10 records its creating 10 new record in Table A.

I need only one record has to be created in Table A and map that unique number as Parent in Table B for all the 10 records which is uploaded through this transform.

DirkRedeker · ‎05-26-2020

Ah, ok. Now I got the point. Let me try to elaborate...

I just had a try and have a solution proposal for you:

In the "OnStart" Event Script, I put the value "Hallo" to the "g_scratchpad" variable.

This variable is then available in the "OnBefore" Script:

The screenshot below proofs, that the value "Hallo" is still in the "g_scratchpad" variable within the "OnBefore" script:

Having this in mind, you can put the SysId of the newly created record (from your "OnStart" Script) to the "g_scratchpad" variable, and use this as the reference for each transformed record in the "OnBefore" Script. The variable remains until the "OnComplete" (I tried out).

So, this should also work for you.

Let me know, if that solves your question and mark my answer as correct and helpful.

BR

Dirk

Have fun

Dirk