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);

[gtk]

can you help with this  

[shiva_gupta]

Please share mapping details for :

Task 2. create a custom table to store group members (similar to sys_grpmemers table)

[Ujjawal Vishnoi]

Hi Shiva,

In the above scenario, your target table is sys_user_grmember then you need not to use create any onafter script, because onAfter event script is processed at the end of a row transformation, after the source row has been transformed into the target row and saved. It does not have ignore flag as well.

Solution: You can mark coalesce user and group both as true. If you do so then it will make a combination of both as unique as per your requirement. Means every user and group will be unique.

Hope this helps.

Regards

Ujjawal

[shiva_gupta]

thanks for the solution, coalesce = true for both the column has worked

[sakshi23]

Hi Shiva,

One small query why you have written a transform script for uploading the data into the group membership table?

If you have user and group fields containing the User Id and the Group Id respectively and it is the display Value in your instance you can directly map the field maps and transform map by applying coalesce on both the field.

If you don't have the user Id and group Id as the the display Value then you can you write the script in the field map to get the sys_id and then transform the mapping with the coalesce marked as true for both the field.

Mark Correct /Helpful /Like based on the effect of response.

Regards

Sakshi