On Complete Transform Script

[Sachin Shivaku1]

Hi All,

I have written an On complete Transform Script for updating a field in parent table after importing the data(via excel) to the Related list table.

Please let me know where I am going wrong.

CODE:

(function runTransformScript(source, map, log, target /*undefined onStart*/ ) {
try{
var count_complete=0;
var count=0;
var gr = new GlideRecord('u_program');
//gr.addQuery('u_case_id',source.u_program_id);
//gr.addQuery('u_implementation','!=','N/A');
gr.addEncodedQuery("u_case_id="+target.u_case_id+"^u_implementation!=N/A");
gr.addNotNullQuery('u_case_id');
gr.query();
gs.log("NoT Entering while-onAfter:");

while(gr.next()){
count++;
gs.log("Entering while-onAfter:");
if(gr.u_implementation=='Complete'){
count_complete++;
}
}

var percent = (count_complete/count)*100;
percent=percent.toFixed(2);
var gr1 = new GlideRecord('u_parent_program');
gr1.addQuery('sys_id',target.u_case_id);
gr1.query();

if(gr1.next()){
gr1.u_penetration=percent;
gr1.update();
}
gs.log("Percent-onAfter:"+percent);
}
catch(e){
gs.log("Exception in Penetration Calculation"+e);
}
})(source, map, log, target);

Thanks in Advance.

Regards,

Sachin

[andrew_venables]

Hi Sachin, 

Well what results are you getting? Is the script working or just giving an incorrect number?

Remember that a onComplete Transform Map Script runs "When: The onComplete event script is processed at the end of an import run, after all data rows are read and transformed." and that the variable "target" is set to the last row of the target table.

So in your script it will use the u_case_id of the last transformed row in your dataset to do both queries, is this correct? Looking at your script maybe you wanted to use an onAfter transform script instead which runs "When: 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."

Also you can make your script much more efficient by switching the first GlideRecord to be GlideAggregate.

Check out this page for the differences: https://docs.servicenow.com/bundle/jakarta-platform-administration/page/script/server-scripting/reference/r_MapWithTransformationEventScripts.html

[Sachin Shivaku1]

Hi Andrew,

You are right, I think using On-complete it gets only the last row data (this might be the reason i am getting NaN as the answer for percent when I tested via logs)

I need to get the data value(u_implementation) of the all rows being imported and then calculate the percent.

Please let me know how this can achieved?

Thanks,

Sachin

[andrew_venables]

Well you might also be getting “NaN” (which stands for not a number) if you are dividing by zero. In your percentage calculation it is possible that your “count” variable is zero. Try adding some more debug to log what count_complete and count are before your percent calculation.

[Sachin Shivaku1]

Hi Andrew,

I tried On-after and it worked fine.

Modified the script for percent as below,

--->> gr1.u_penetration=percent;

Only concern is that while I am importing bulk data (4K records), the browser tab loads and freezes up, later when I check all the data would have uploaded perfectly.

Can you let me know how to overcome performance issues???

Thanks in advance.

Regards,

Sachin