Update user sys_id in Scheduled Reports

[EricG]

I need help in updating a users ID in the sysauto_report.user_list field.

I have about 500 users that are getting new AD Records due to company name change.  Why they are not updating the existing AD record is beyond my paygrade.  LOL.

So I need to be able to find the old user sys_id in the sysauto_report table and update it with the new one, while retaining all others in the list.  My workflow that is doing this for other tables/references is submitting a request for a single user.  Which is how these have been coming in so far.  I've reviewed several posts, but List collectors are still alluding me.

has anyone done this before and can share a script to follow.

[Tushar · Accepted solution]

Hi @EricG 

Please use the following sample script - 

// Specify the old user's sys_id and the corresponding new sys_id
var oldUserId = 'old_user_sys_id';
var newUserId = 'new_user_sys_id';

// Specify table name and field name where you want to make the update
var tableName = 'sysauto_report';
var listCollectorField = 'user_list';

// Query record in the table
var gr = new GlideRecord(tableName);
gr.addQuery(listCollectorField, oldUserId); // Find records containing the old user sys_id
gr.query();

while (gr.next()) {
    // Update the list collector field by removing the old user and adding the new user
    var userList = gr.getValue(listCollectorField);
    var updatedUserList = userList.replace(oldUserId, newUserId);
    
    // Set the updated list back to the record
    gr.setValue(listCollectorField, updatedUserList);
    
    // Save record
    gr.update();
}

// Optionally, you can also log the records that were updated
gs.info('Updated records in ' + tableName + ' for old user ' + oldUserId);

Please, don't forget to mark my answer as correct if it solves your issue or mark it as helpful if it is relevant for you!

Regards,
Tushar

[EricG]

Thanks @Tushar  I will try this next and let you know.