User field updation in Task Table

ANUSHA S NAIR1 · ‎12-05-2023

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 · ‎12-05-2023

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

View solution in original post

Danish Bhairag2 · ‎12-05-2023

Hi @ANUSHA S NAIR1 ,

Here is a sample script for incident n request table plz ammend modify as per your requirement. Create a after update BR on user table.

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

    // Check if the user becomes inactive

    if (current.active == false && previous.active == true) {

        // Specify the new user to assign tasks to

        var newAssignee = 'NEW_USER_ID'; // Replace with the sys_id of the new user

        // Update tasks where the inactive user is referenced in any task table
        updateTasksForUserReference('incident', 'assigned_to', current.sys_id, newAssignee);
        updateTasksForUserReference('service_request', 'assigned_to', current.sys_id, newAssignee);
        // Add more updateTasksForUserReference calls for other task tables as needed
    }

})(current, previous);

function updateTasksForUserReference(tableName, referenceField, oldValue, newValue) {

    var taskGr = new GlideRecord(tableName);
    taskGr.addQuery(referenceField, oldValue);
    taskGr.query();
    while (taskGr.next()) {
        taskGr.setValue(referenceField, newValue);
        taskGr.setWorkflow(false);
        taskGr.update();

    }

}

Thanks,

Danish

ANUSHA S NAIR1 · ‎12-05-2023

Your solution is specific to only assigned to field and only two tables. I am looking for a solution where I dont to have mention all the task tables and its respective 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.

Anand Kumar P · ‎12-05-2023

Hi @ANUSHA S NAIR1 ,

You can write after update business rule in sys_user condition active changes

  if (current.active === false && previous.active === true) {
  var incidentGr = new GlideRecord('incident');
        incidentGr.addQuery('assigned_to', current.user_name);
        incidentGr.query();
        while (incidentGr.next()) {
            incidentGr.setValue('assigned_to', newUsersys_id);
            
        }
 var scRequestGr = new GlideRecord('sc_request');
        scRequestGr.addQuery('requested_for', current.user_name);
        scRequestGr.query();
        while (scRequestGr.next()) {
            scRequestGr.setValue('requested_for', newUsersys_id);
       
        }
    }

Mark it as helpful and solution proposed if it serves your purpose.

Thanks,

Anand

ANUSHA S NAIR1 · ‎12-05-2023

Your solution is specific to only assigned to and requested for field and only two tables. I am looking for a solution where I dont to have mention all the task tables and its respective 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.