User field updation in Task Table

[ANUSHA S NAIR1]

Hi,

Can somebody help me with a script that needs to be written in a business rule where if a user goes inactive then all the records where the user is associated with, in all the corresponding task table should be assigned to another user. 

For E.g. If a user is having a open Incident, assigned to him Incident, Open Service Request, assigned to him Service Request. All the task tables which is having a user reference field should be updated to another user.

[Tai Vu]

Hi @ANUSHA S NAIR1 

You can query to the Task [task] table with current User record that Active changes to False.

Afterward, update the Assigned to [assigned_to] field to the new user.

Sample below.

(function executeRule(current, previous /*null when async*/) {
	
	var newAssigneeID = ''; //the sys_id of new assignee
	var grTask = new GlideRecord('task');
	grTask.addActiveQuery();
	grTask.addQuery('assigned_to', current.getUniqueValue());
	grTask.query();
	grTask.assigned_to = newAssigneeID;
	grTask.updateMultiple();

})(current, previous);

You need to make sure the new Assignee is a member of the Assignment Group as well.

Cheers,

Tai Vu

[ANUSHA S NAIR1]

Your solution is specific to only assigned to field. I am looking for a solution where I dont have to mention the user reference fields in them. Lets take an example of Incident table there are many user reference fields like caller, opened by, assigned to, resolved by etc.

[Tai Vu · Accepted solution]

Hi @ANUSHA S NAIR1 

So if this is related to all the User Reference fields, you would need to query the Dictionary [sys_dictionary] first to retrieve the list of Table and Field Name (Element) first.

NOTE: It's crucial to note that there could be more than thousands of fields referencing the User table, and updating the fields per different tables is NOT a straightforward process.

What is your specific business requirement?

Sample below.

	var newAssigneeID = ''; //the sys_id of new assignee
	var grDictionary = new GlideRecord('sys_dictionary');
	grDictionary.addQuery('internal_type', 'reference');
	grDictionary.addQuery('reference', 'sys_user');
	grDictionary.addActiveQuery();
	grDictionary.query();
	while(grDictionary.next()){
		var tableName = grDictionary.getValue('name');
		var fieldName = grDictionary.getValue('element');
		updateNewUser(tableName, fieldName, current.getUniqueValue(), newAssigneeID);
	}

	function updateNewUser(table_name, field_name, current_user, new_user){
		var gr = new GlideRecord(table_name);
		gr.addQuery(field_name, current_user);
		gr.query();
		while(gr.next()){
			gr[field_name] = new_user;
			gr.update();
		}
	}

Cheers,

Tai Vu

[ANUSHA S NAIR1]

Yes, this solution helps. This is the script I wanted.