I need to populate the email address from the user table.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎12-29-2023 03:19 AM
I need to populate the email address in the custom table(u_test) from the sys_user table through the employee number as reference. Can anyone help me on the background or fix scripts to achieve the same.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎12-29-2023 03:27 AM
Hi,
Do you have employee number in your test table?
Give the field name.
Thanks and Regards,
Saurabh Gupta
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎12-29-2023 03:29 AM
Yes I have, Here it is 'GID'
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎12-29-2023 03:37 AM
Hi @Archana23
You can use below script.
var testGr = new GlideRecord('u_test');
testGr.addNotNullQuery('emp_number_field_on_custom_table ');
testGr.query();
while(testGr.next())
{
var userGr = new GlideRecord('sys_user');
userGr.addQuery('employee_number', testGr.emp_number_field_on_custom_table );
userGr.query();
if (userGr.next()) {
userGr.email_field_on_custom_table = userGr.email;
userGr.update();
}
}
Thanks and Regards,
Saurabh Gupta
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎12-29-2023 03:30 AM
Hi @Archana23 ,
Please try the below code , remember to change the field name for email field in u_test table
// Specify the table names
var customTable = 'u_test';
var userTable = 'sys_user';
// Query the records from the custom table where email is empty
var customTableGR = new GlideRecord(customTable);
customTableGR.addQuery('email', '');
customTableGR.query();
while (customTableGR.next()) {
// Get the employee number from the custom table
var employeeNumber = customTableGR.getValue('employee_number');
// Query the sys_user table based on the employee number
var userGR = new GlideRecord(userTable);
userGR.addQuery('employee_number', employeeNumber);
userGR.query();
// If a matching user is found, update the email in the custom table
if (userGR.next()) {
customTableGR.email = userGR.email;
customTableGR.update();
}
}
gs.print('Email addresses populated successfully.');
Thanks,
Danish