Entire transform map ignore if particular field is nil

[prasanthraj]

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.

[prasanthraj]

Hi Chris, 

Thanks a lot for your response!

I thing you did't get my point. Here i need to stop entire import set if field is nil in first occurrence (says any row)

if any thing possible?

[Cory CJ Wesley]

This may work - not sure though, haven't tested it but assuming that the map.sys_id variable maps to the import set that you're working it, it should work.

Explanation: As @Chris Sanford said, we're doing a GlideRecord call to your import set table but we're doing to do this as an onStart script rather than onBefore because you want to halt the transform if you there are any null values in the import set.  I took the base of his code and added the map.sys_id variable that hopefully corresponds to the current import set.  That gives us the proper data to check against.

Next we get the list of fields in that table for each record and we loop through them checking the values for null.  If we find any null we then set ignore to true which kills the transform and we exit the function.

Remember that this is now an onStart script rather than onBefore.

chkFields();

function chkFields(){
var gr = new GlideRecord('u_data_source');
gr.addQuery('sys_import_set', map.sys_id);
gr.query();
if(gr.next()) {
var gru = new GlideRecordUtil();
var fields = gru.getFields(gr)
for(i=0; i < fields.length; i++){
var chkValue = gr.getValue(fields[i]);
if (chkValue.nil()){
	ignore = true;
	return;

}

}


}
}

[Chris Sanford1 · Accepted solution]

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.

[Cory CJ Wesley]

"However you should use import_set.sys_id, not map.sys_id."

Thanks for that!  Never had to use that one before.

[Chris Sanford1]

Ah, I thought you meant only if it is null in the 9th row. In that case, removing the line 'gr.addQuery('sys_import_row', 9);' and replacing with 'gr.addQuery('sys_import_set', import_set.sys_id);' should do it. I failed to query only the specific import set being transformed before, as the table could contain data from other import sets.

Edit: Also run the script onStart rather than onBefore as cwesley suggested, to prevent unnecessarily running the check again on every row (although it would also work).