question Transform map should not update if one record is missing value. in ITSM forum

Transform map should not update if one record is missing value.

suryan — Wed, 20 Sep 2017 05:12:57 GMT

In Transform map iam updating the records(no inserting). If ST validation id is empty then that record is not updating, it is working fine. My requirement is if

1)I'am updating 1000 records if 999 records is having ST.validation_ID, then i don't want to update single record in transform map,(the transform map should not be happen for single record out of 1000 i.e if 1 record is missing validation.ID whole 1000 records should not update.) means the transform map option should not complete.

2)I'am getting messages in system log, is it possible to get the message while doing transform map so that user be able to know that records are not updating because of empty validation ID.

Re: Transform map should not update if one record is missing value.

Shishir Srivast — Wed, 20 Sep 2017 05:39:35 GMT

Can we try to get the count of validation.ID field using GlideAggregate with onStart transform scripts and then compare with the actual count, if record doesn't match then let's have the ignore = true;

http://wiki.servicenow.com/index.php?title=GlideAggregate#gsc.tab=0

http://wiki.servicenow.com/index.php?title=Transform_Map_Scripts#Transformation_script_variables

I didn't try but let's see if it helps in this way.

var actualcount = new GlideAggregate('Source_table_name');

actualcount.addAggregate('COUNT');

actualcount.query();

var num = 0;

var actualnum = 0;

if(actualcount.next()){

actualnum = actualcount.getAggregate('COUNT');

var count = new GlideAggregate('Source_table_name');

count.addQuery('validation_field_name', null);

count.addAggregate('COUNT');

if(count.next())

num = count.getAggregate('COUNT');

}

if(num != actualnum)

ignore = true;

Re: Transform map should not update if one record is missing value.

bernyalvarado — Wed, 20 Sep 2017 05:52:37 GMT

It feels like we responded this in a different thread some days ago .

1) yes. Using onStart script. Shishir is right in regards that you just need to validate if at least one of the records it's missing the value. A GlideAggregate is not really needed for this. You can even just do a query over a source table and see if there's at least one value where validation_ID is missing.

2) I don't think there's a way to show a message to the end user doing an import/tranform unless something like a gs.addInfoMessage works.

Thanks,

Berny

Re: Transform map should not update if one record is missing value.

bernyalvarado — Wed, 20 Sep 2017 05:53:54 GMT

Hope this helps!

Re: Transform map should not update if one record is missing value.

Deepak Kumar5 — Wed, 20 Sep 2017 05:54:04 GMT

You can use onBefore Transform map script to check the validation id. Below is an example code.

var id = source.u_name.toString();

var info = "Before the row is transformed, " + id;

log.info( info );

// Make sure a company name has been provided

var tid = new GlideRecord('Table name');

tid.addQuery('validation_field_name', "id");

itd.query();

if(tid.hasNext()){

ignore = true;

info ="Record already there, row ignored! " + id;

log.info( info );

}

Re: Transform map should not update if one record is missing value.

suryan — Wed, 20 Sep 2017 06:02:21 GMT

Hi berny,

If one record is missing whole update has to be aborted, but here only missing values are not updated, remaining it is updated. I want all records not to be updated still if one record is missing the value .

Re: Transform map should not update if one record is missing value.

Shishir Srivast — Wed, 20 Sep 2017 06:05:12 GMT

I think, that's why we have to use ignore script variable with onStart() script to skip the entire transformation.

ignore

Flag

A boolean variable ("true" or "false") where true in onStart scripts will cause the entire transformation process to be skipped. Setting it to true in onBefore scripts will cause the current row transformation to be skipped, and will continue to process the remaining rows. Default is "false".

if (source.u_user_name.nil()){ ignore = true; }

Re: Transform map should not update if one record is missing value.

bernyalvarado — Wed, 20 Sep 2017 06:10:28 GMT

Yeap ignore = true in the onStart script will halt all the transformation process.

Thanks,

Berny

Re: Transform map should not update if one record is missing value.

suryan — Wed, 20 Sep 2017 06:26:19 GMT

if(action=='insert'){

ignore = 'true';

}

if(source.u_supplier_validation_result == ''){

ignore = 'true';

error=true;

error_message='Supplier Validation Results field is blank, Please update the field to complete the update';

}

this is the code i wrote if validation result is empty it is skipping only dat record, remaining it is updating. I want whole transform map to be aborted.

Re: Transform map should not update if one record is missing value.

Shishir Srivast — Wed, 20 Sep 2017 06:37:12 GMT

Can you please remove the quotes from below true keyword, hoping this is onStart() script?

ignore = 'true';

Re: Transform map should not update if one record is missing value.

suryan — Tue, 26 Sep 2017 10:41:40 GMT

This is my onstart script

(function transformRow(source,map, log, target) {

if(action == 'insert'){

ignore = true;

}

if(source.ST.validation_ID, == ''){

ignore = true;

error = true;

error_message='Supplier Validation Results field is blank, Please update the field to complete the update';

}

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

this wil only the row skip if validation id is empty, it is not aborting the whole insert if any one validation id is empty.

Re: Transform map should not update if one record is missing value.

suryan — Tue, 26 Sep 2017 10:42:26 GMT

This is my onstart script

(function transformRow(source,map, log, target) {

if(action == 'insert'){

ignore = true;

}

if(source.ST.validation_ID, == ''){

ignore = true;

error = true;

error_message='Supplier Validation Results field is blank, Please update the field to complete the update';

}

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

this will only skip the row if validation id is empty on that row, it is not aborting the whole insert if any one validation id is empty.

Re: Transform map should not update if one record is missing value.

Shishir Srivast — Wed, 27 Sep 2017 03:00:18 GMT

is the source field name is ST.validation_ID. Can we replace the dot after ST with underscore like ST_validation_ID and also in you if condition there is a comma after ST.validation_ID. Can you please remove that and try. Can we something like this with nil() function.

(function transformRow(source,map, log, target) {

if(action == 'insert'){

ignore = true;

}

if(source.ST_validation_ID.nil()){

ignore = true;

error = true;

var error_message='Supplier Validation Results field is blank, Please update the field to complete the update';

log.info(error_message);

}

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

Re: Transform map should not update if one record is missing value.

suryan — Wed, 27 Sep 2017 06:58:30 GMT

Replace the dot after ST with underscore like ST_validation_ID and replace the code like this -> source.ST_validation_ID.nil(),

it is onstart script, still it is not working

Re: Transform map should not update if one record is missing value.

Shishir Srivast — Wed, 27 Sep 2017 07:02:15 GMT

Can we change the field name in lower case and try, like: st_validation_id

Re: Transform map should not update if one record is missing value.

suryan — Wed, 27 Sep 2017 07:05:43 GMT

changed to lower case same issue

Re: Transform map should not update if one record is missing value.

suryan — Wed, 27 Sep 2017 07:09:14 GMT

Below is my onstart transform script. still it is not aborting the action if one row(st validation id) is empty.

(function transformRow(source,map, log, target) {

if(action == 'insert'){

ignore = true;

}

if(source.st_validation_id.nil()){

ignore = true;

error = true;

var error_message='Supplier Validation Results field is blank, Please update the field to complete the update';

log.info(error_message);

}

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

Re: Transform map should not update if one record is missing value.

Shishir Srivast — Wed, 27 Sep 2017 07:15:09 GMT

Please validate what is the column name in source table for st validation id, it might be a chance that field (column) name is u_st_validation_id. Please open the table (under system definition -> table) and verify the column name.

Re: Transform map should not update if one record is missing value.

suryan — Wed, 27 Sep 2017 07:21:49 GMT

correct it is u_st_validation_id, changed and run twice still it is allowing to update if validation id is empty.

checked

1)u_st_validation_id == '' and

2) u_st_validation_id.nill()

still same issue

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

ignore = true;

if(action == 'insert'){

ignore = true;

}

if(source.u_st_validation_id == ''){

ignore = true;

error = true;

error_message='Supplier Validation id field is blank, Please update the field to complete the update';

}

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

Re: Transform map should not update if one record is missing value.

Shishir Srivast — Wed, 27 Sep 2017 07:26:03 GMT

just try with below script and see.

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

if(source.u_st_validation_id.nil())

ignore = true;

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