Query/Script to add user to group based on field in sys_user table

[Robert T]

Hello,

In our sys_user table, the department field changes frequently.

I'd like to have a user be added to a group if department = x, but also have a user be removed from a group if their department changes from x to something else (y).

I am fairly new to SN, and have experimented with a business rule on the sys_user table that interacts with the sys_user_grmember table.  This has had limited success with some buggy behavior.

I'd like to be able to run 1 clean script that updates all users based on their department being x or y, then have an active rule that looks for updates to the department field and associates or removes that group going forward.

Many thanks for any and all help,

--Robert

[Robert T]

These responses have been helpful.  Here is the current BR I'm working with:

(function executeRule(current, previous /*null when async*/) {

var gr = new GlideRecord('sys_user_grmember');
gr.initialize();
    
if (current.department != '033bca1d0a0a3d2') {
gr.group = '6030759f1b14f8d40c';
gr.user = current.sys_id;
gr.deleteRecord();
} else if (current.department == '033bca1d0a0a3d2') {
gr.group = '6030759f1b14f8d40c';
gr.user = current.sys_id;
gr.insert();
}
    
})(current, previous);

The logic goal here was:

• after any update to department field
• if the new department is not X
• then delete user from Y group
• if the new department is X
• then add user to Y group

A few problems I had with this logic:

• It doesn't check whether user is in Y group before deleting (so what is it doing?)
• This does not apply to the whole set (guessing I need a client script for this)

[Tony Chatfield1]

It doesn't check whether user is in Y group before deleting (so what is it doing?)
Your glide query has no filtering to find the specific record that you want to delete, I assume it deletes the first record it finds (either the one with the lowest or highest sys_id)

This does not apply to the whole set (guessing I need a client script for this)
Can you clarify exactly what you mean here?
I would expect the BR to run for an sys_user record updated from list or form where the trigger conditions are met. It should also run for any sys_user record updated from an import when the transform map is flagged run BR’s = true.

In your example script the sys_id’s are not valid values, I assume that you have truncated or added incorrect values intentionally?

I would configure 2 separate Glide queries (like my earlier example)
1 to delete any existing record, filtering specifically for a match and 1 to add any new record.

After update BR on sys_user
Trigger conditions
current.deparment.changes() && (current.department == ‘sys_idOfDepartmentY’ || previous.department == ‘sys_idOfDepartmentY’)
//We only need to run if the new or old department is Y
//The script does not allow or consider any user record that is manually associated to GroupX

var myGroup = 'sys_idOfGroupY');
var myDepartment = 'sys_idOfDepartmentX');

//When the previous department is X, delete any relationship for GroupY
if(previous.department == myDepartment) {
var myOldRec = new GlideRecord('sys_user_grmember');
myOldRec.addQuery('user', current.sys_id);
myOldRec.addQuery('group', myGroup);
myOldRec.query();

if(myOldRec.next()) { //We check to confirm that the glidequery did return a record
myOldRec.deleterecord();
}
}

//Add a new relationship if needed
if(current.department == myDepartment) {
var myNewRec = new GlideRecord('sys_user_grmember');
myNewRec.initialise();
myNewRec.user = current.sys_id;
myNewRec.group = myGroup;

myNewRec.insert();
}

[Robert T]

Thanks for the careful and thoughtful response.  I appreciate the attention to detail in my questions as I'm learning something new with each reply.

I update the BR on sys_user as follows, and am noticing that simply nothing is happening when I update a user's department to / from department x.

Condition is:

current.deparment.changes() && (current.department == '033bca1d0a0a3d2900ae09459a7ae887' || previous.department == '033bca1d0a0a3d2900ae09459a7ae887')

Script is:

(function executeRule(current, previous /*null when async*/) {
	
var myGroup = '6030759f1b14f8d40c0877be034bcb8e';
var myDepartment = '033bca1d0a0a3d2900ae09459a7ae887';

//When the previous department is X, delete any relationship for GroupY
if(previous.department == myDepartment) {
var myOldRec = new GlideRecord('sys_user_grmember');
myOldRec.addQuery('user', current.sys_id);
myOldRec.addQuery('group', myGroup);
myOldRec.query();

if(myOldRec.next()) { //We check to confirm that the glidequery did return a record
myOldRec.deleterecord();
}
}

//Add a new relationship if needed
if(current.department == myDepartment) {
var myNewRec = new GlideRecord('sys_user_grmember');
myNewRec.initialise();
myNewRec.user = current.sys_id;
myNewRec.group = myGroup;

myNewRec.insert();
}

})(current, previous);

[Kyle Kennedy]

Hello Robert,

I understand this is an older question and I'm sure you no longer need this information, but I recently solved a similar issue so I wanted to put my solution on here for anyone else who might come across it.

My scenario: We have Application Services that have a user populated in the 'owned_by' field. We also have a group that contains all of the 'Business Owners' (users populated in the 'owned_by' field), which is used for sending out reports. We wanted this group to be automatically managed when an owner changed for an Application Service. So the below script for my business rule does a few things: 

1. It populates variables containing the new and the old owners.

2. It queries the {table} for any other application services that the OLD owner may also be the owner for. 

3. If the OLD owner is the owner for other services, DO NOT remove them from the group.

4. If they are NOT the owner for other services, REMOVE them from the group.

5. Next, it queries the 'sys_user_grmember' table to see if the NEW owner is already in the group.

6. If they are in the group, they are not added again.

7. If they are NOT in the group, then they are added.