Transform Script to ignore inactive record but update active record

[tricial]

I need help with the below use case:

I have to cleanup user records, there are duplicates but no general uniqiue identifier except the full name

I have tried a number of onbefore transforms and cannot get the active record to be updated

It ignores the inactive record fine, but it does not continue to find the active record and update that;

So I have a user

Full name:   James Brown

Multiple user_ids

Multiple email addresses (not always)

If I add this to the OnBefore Script

// if(!target.active){

// ignore = true;

// }

It finds the first record and if its inactive it does ignore it, however I need it to find all instances of this record and update the active one.

I tried this to see if I can lookup the users full name and if it finds the user inactive ignore it, if it finds it active update it.

Still not working.

Unfortunately the data in the sys_user table has duplicates that I am struggling with getting to update the correct record.

Since there are 90+k users (both active and inactive) I cannot easily clean this up until I have been able to update the active record.

I have a field that I am setting a value on after the record is updated that will allow me to cleanup all records that do not have this field value set, but in the meantime, I need help getting the active record updated.

var gr = new GlideRecord('sys_user');

gr.addQuery('name='+source.u_full_name.toString());

gr.query();

while(gr.next()){

if(!gr.addActiveQuery()){

ignore=true;

}

else{

target = gr;

}

}

// if(!target.active){

// ignore = true;

// }

Thank you

Tricia

[Joel Dias]

When you set the "ignore = true;" you are actually ignoring the input record and nothing is updated.

I think you should add a coalesce field to your transform map.

target field is active and source is "true" (you can use a source script, answer=true).

Create a field map

Mapping options

[VaranAwesomenow]

Dear Patricia,

Is your coalesce on Name column ? Can you try having coalesce on both first name and last name and see if it makes any difference as name is a calculated field.

[VaranAwesomenow]

Dear Patricia,

After furthe analysis, I figured that the coalesce that we identify should be unique, if servicenow finds more than one record with coalesce then it updates the first record and ignores the rest of them, so in order to solve this you need to first try to identify a field in the user table that is unique and make that as coalesce in the transform map. Since in your case you wanted to skip updating the ones that are not active, One place to start would be, to mark the user_name field of all the inactive records with a suffix as _old or _inactive and make user_name as coalesce instead of name. Hope that makes sense.

You can use a simple script to set the user_name field suffix,

var gr = new GlideRecord('sys_user');

gr.addQuery('active', false);

gr.query();

while(gr.next()) {

gr.user_name +='_old';

gr.setWorkflow(false);

gr.update();

}

Thanks
Anil

[VaranAwesomenow · Accepted solution]

One more option would be .. to include active in the transform map and coalesce on it, but this needs the source data to have active field on it and it should be set to true, this will ensure that only active records are picked up and updated and rest of the ones are ignored. I just tried and it worked. I have updated the country code for a user record which was United kindom earlier to Brazil.. Below are screenshots

Import spreadsheet

Transform Map

Before Data load screenshot

After Data load and transform -screenshot

Hope this helps !

Thanks
Anil