I need to populate the email address from the user table.

[Archana23]

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.

[Saurabh Gupta]

Hi,
Do you have employee number in your test table?
Give the field name.

[Archana23]

Yes I have, Here it is 'GID'

[Saurabh Gupta]

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();
    }
}

[Danish Bhairag2]

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