update group info using data source

VSN
Tera Expert

i have a requirement to update the existing groups and group members, and roles by using the transform map and data source..

 

all this information was stores in a Excel, i have to upload that file using data source and update the existing data.

VSN_0-1747066998693.png

Do  i need to create data source and transform maps for different tables like one for Group table, and another for group members table, and another  for Roles table.

1 ACCEPTED SOLUTION

Robert H
Mega Sage

Hello @VSN ,

 

You can do this using a single Data Source with two Transform Maps.

 

The 1st Transform Map would target the Group [sys_user_group] table, and would have field maps for the Name (coalesce enabled) and Manager columns.

 

To add the roles to the groups you need an "onAfter" Transform Script on the first Transform Map:

 

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

	var roleNames = source.getValue('u_roles').split(',');
	var groupId = target.getUniqueValue();

	roleNames.forEach(roleName => {
		var gr = new GlideRecord('sys_group_has_role');
		gr.addQuery('group', groupId);
		gr.addQuery('role.name', roleName);
		gr.query();
		if (gr.hasNext()) return;
		gr.initialize();
		gr.setValue('group', groupId);
		gr.setDisplayValue('role', roleName);
		gr.insert();
	});
	

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

 

The 2nd Transform Map would target the Group Member [sys_user_grmember] table, and would map the Name and User ID columns (both with coalesce enabled).

 

Regards,

Robert

View solution in original post

9 REPLIES 9

nayanmule
Tera Expert

I think you need to achieve this by using two data sources.

1. For Group - we have sys_user_group table where you can update the Name of the Group , Manager & Roles.

(You can create a transform map or simply upload the file from the list view using 'Import excel').

 

Thankyou!

2. For Group members - we have sys_user_grmember table where you can update the Group name & Group Members.

nayanmule_0-1747069437310.png

 

Robert H
Mega Sage

Hello @VSN ,

 

You can do this using a single Data Source with two Transform Maps.

 

The 1st Transform Map would target the Group [sys_user_group] table, and would have field maps for the Name (coalesce enabled) and Manager columns.

 

To add the roles to the groups you need an "onAfter" Transform Script on the first Transform Map:

 

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

	var roleNames = source.getValue('u_roles').split(',');
	var groupId = target.getUniqueValue();

	roleNames.forEach(roleName => {
		var gr = new GlideRecord('sys_group_has_role');
		gr.addQuery('group', groupId);
		gr.addQuery('role.name', roleName);
		gr.query();
		if (gr.hasNext()) return;
		gr.initialize();
		gr.setValue('group', groupId);
		gr.setDisplayValue('role', roleName);
		gr.insert();
	});
	

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

 

The 2nd Transform Map would target the Group Member [sys_user_grmember] table, and would map the Name and User ID columns (both with coalesce enabled).

 

Regards,

Robert

Can you help me with script?

Hello @VSN ,

 

Sure, I have updated my original reply. And I also reduced the number of required Transform Maps from 3 to 2.

 

Regards,

Robert