How to copy values from one field to another field with referring to different tables

[Divya57]

Hi Team,

Am try to copy the values from one list field to another list field. one list field is referencing to sys_user table and another list field referencing to custom table. The sys_user table and custom table having same records but different sys_ids.

The script i used was copying values correctly but not matching the sys_id. 
how to match sys_ids ?

[Ravi Gaurav]

Hi @Divya57 

I can just give you a sample logic of GlideRecord to update it.

// Initialize the mapping object
var userMapping = {};

// Query the custom table to map names to sys_ids
var customTableGR = new GlideRecord('your_custom_table');
customTableGR.query();
while (customTableGR.next()) {
userMapping[customTableGR.user_name.toString()] = customTableGR.sys_id.toString();
}

// Query the sys_user table and copy matching sys_ids to the new list field
var userGR = new GlideRecord('sys_user');
userGR.addQuery('sys_id', 'IN', sourceListField); // assuming sourceListField is the list of sys_user sys_ids
userGR.query();

var targetSysIds = [];
while (userGR.next()) {
var customSysId = userMapping[userGR.user_name.toString()];
if (customSysId) {
targetSysIds.push(customSysId);
}
}

// Assuming you have the target record initialized and loaded
var targetRecord = new GlideRecord('your_target_table');
if (targetRecord.get(targetRecordSysId)) { // assuming targetRecordSysId is the sys_id of the target record
// Set the target list field to the matching sys_ids in the custom table
targetRecord.setValue('targetListField', targetSysIds.join(',')); // setting the list field with comma-separated values
targetRecord.update(); // Save the changes
}