Entire transform map ignore if particular field is nil

prasanthraj · ‎02-28-2018

Hi Experts,

I have requirement entire import set need to ignore if particular field is nil.

Eg - Excel sheet have 10 rows. At row 9th field x==nil then entire transaction need to ignore/skip/kill

How to achieve this scenario?

I have search other community result but not able find exact result.

Chris Sanford1 · ‎03-01-2018

Ah ok, onStart might be a better call as the script will halt the entire transform map, rather than running the same script on every row. I didn't realize setting ignore to true in an onStart script did that.

If I'm not mistaken I think he does want it to be a specific field that's null rather than any field, but it should halt if null in any row. I did forget to query for the specific import set in my previous example. However you should use import_set.sys_id, not map.sys_id. So here's the edited solution:

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

var gr = new GlideRecord('u_data_source');

gr.addQuery('sys_import_set', import_set.sys_id);
gr.addNullQuery('field_x');
gr.query();
if(gr.next()) {
   ignore = true;
}

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

Run this onStart, and it should work.

View solution in original post

Cory CJ Wesley · ‎03-01-2018

I don't think you can do this out of the box and since a transform proceeds line by line, I struggle to find a way that you can achieve this during the transform without writing a custom rollback script which, depending on the operation, might not prevent the unintended consequences of creating/modifying data.

That said, you might be able to achieve this on the import set table itself. You'd first have to import sample data to get the import set table created. Then have a script action run on every 'import.finished' event. That script action checks the import set for records with a null value and, if finding any, sets the import set to an error state which would prevent the transform from occurring.

Doesn't stop the import in-flight, stops it from occurring in the first place.

prasanthraj · ‎03-01-2018

Hi cwesley,

I believe your approach is good, could you please explain way code it might help not only me but also who have same question.

additional to that if first occurrence transaction need to stop with some error message content.

Chris Sanford1 · ‎03-01-2018

In an onBefore script you could do a GlideRecord call into your data source table, look for the specific record/field, and set ignore=true if it is blank. Although the script does still run row-by-row, it should set every row to ignore when the condition you speak of is met..

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

var gr = new GlideRecord('u_data_source');

gr.addQuery('sys_import_row', 9);
gr.addNullQuery('field_x');
gr.query();
if(gr.next()) {
   ignore = true;
}

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

Replace 'u_data_source' with the name of the import set data source table, and 'field_x' with the field you are wanting to check.

Cory CJ Wesley · ‎03-01-2018

Nice, I didn't think of that. Also misread that he just wants to check the 9th row and one particular field rather than every field in every row.