duplicate entry when adding group-user in the sys_user_grmember table using transform map script

[shiva_gupta]

I have a source excel file on below format :

Group:   Contain the group name.

User: Contain User Full Name

User ID: Contain user id(unique for each user ex:-A123)

Mapping details are below :

Also user the onAfter transform script as mention below:     -   But if the user is already present in the respective group this transform map is adding the same user twice in the respective group, can anyone please suggest avoiding this duplicate entry.

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

      // get the user

      var usr = new GlideRecord('sys_user');

      usr.get('user_name',source.u_user_id.toString());

      // get the group

      var grp = new GlideRecord('sys_user_group');

      grp.get('name',source.u_group_id.toString());

      // insert group membership and also check wether it is present

      var gr = new GlideRecord('sys_user_grmember');

      gr.addQuery('user', usr.sys_id);

      gr.addQuery('group', grp.sys_id);

      gr.query();

      if (gr.next()){

      //no action required

      }

    else {

    var grMember = new GlideRecord('sys_user_grmember');

      grMember.initialize();

      grMember.user = usr.sys_id;

      grMember.group = grp.sys_id;

      grMember.insert();      

      }

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

[Raju Koyagura]

In Excel I don't see a group id however in the field maps I can see source field as "u_group_id".

In script, you are comparing name with u_group_id

// get the group

      var grp = new GlideRecord('sys_user_group');

      grp.get('name',source.u_group_id.toString());

Better verify the elements and mappping fields.

[gtk]

can you help with this  

[Deepak Kumar5 · Accepted solution]

Make "User" and "Group" as coalesce = ture. If both the fields will match in target table,then it will update record else insert new record.

[shiva_gupta]

thanks for the suggestion, it worked