OnBefore Transform Script to ignore insert if no record found in gliderecord table:

[patricialynch]

I am trying to run an onbefore transform script to check the following

The source employee id to the sys_user employee_number

If no match found ignore the record and do not indert

This is not working

It is still creating records with no user

var empId = source.u_employee_id;

var gr = new GlideRecord('sys_user');

gr.addQuery('employee_number', empId);

gr.query();

while(!gr.next()){

  if(action == insert){

  ignore = true;

}

}

I have also tried

if(!gr.next()){

ignore = true;

}

That doesn't work either

What am I missing?

Thank you

[Anurag Tripathi]

Hi Patricia,

Try this

var empId = source.u_employee_id;

var gr = new GlideRecord('sys_user');

gr.addQuery('employee_number', empId);

gr.query();

if(!gr.next()){

  if(action == "insert"){

  ignore = true;

}

}

[Harish Murikina]

What your trying to do exactly . Are you trying to update user record based on emp id?

after mapping the fields form source to target it will update automatically you don't want any gliderecord here.

What you want do here is you can write before transform script with below two lines of code.

That is good enough to work.

if (action == "insert")

  ignore = true;

[Harish Murikina]

See below information for more undersnatnd.

1 Overview

In an import, coalescing on a field (or set of fields) means the field will be used as a unique key. If a match is found using the coalesce field, the existing record will be updated with the information being imported. If a match is not found, then a new record will be inserted into the database.

2 Updates Only

To only update records where a match is found, and skip records where a match is not found, specify a coalesce field and add the following script as an OnBefore script to the transform map.

if (action == 'insert') ignore = true;

[avinash kumar]

Hi Patricia,

If your source table is sys_user in the transfrom map, this is a classical case of update   only Using the Coalesce Field - ServiceNow Wiki

In your transform map add a Coalesce on the employee_number and put the below in the onbefore script.

if (action == 'insert') ignore = true.;.

Other than that if you still want to use this approach, I am not sure why are you putting

while(!gr.next) as while(!hasnext()) in some languages. You should put

while(gr.next()){

// your code here

}

gr.next() returns false if there is no record.

But if you have a source table other than sys_user than I can understand that your approach is the one which will be just fine.

Cheers,

Avinash