Check duplicate user records

[Monica Chakrabo]

I have created a Schedule job which generates 1000 records under a custom table user table(which I created). It basically stores selective information(columns) of users from sys_user table. The custom table has fields like UID, Name, active, email and Phone no. While adding the user data to the custom table from sys_user table, I want to check if the record already exists in custom user table, if the record exist, it should abort adding the same user data again. How do I achieve that?

[Ankur Bawiskar]

@Monica Chakrabo 

before insert you can query the custom table with UID which is possibly storing user_name from sys_user

If not found then only insert, if found don't do anything

please share your job script here

If my response helped please mark it correct and close the thread so that it benefits future readers.

[Ankur Bawiskar · Accepted solution]

@Monica Chakrabo 

something like this, but please enhance

// Scheduled job script to add user data to custom table

// GlideRecord to fetch user data from sys_user table
var userGR = new GlideRecord('sys_user');
userGR.query();

while (userGR.next()) {
    // Get user details
    var uid = userGR.getValue('user_name'); // Assuming 'user_name' is used as UID
    var name = userGR.getValue('name');
    var active = userGR.getValue('active');
    var email = userGR.getValue('email');
    var phone = userGR.getValue('phone');

    // GlideRecord to check if the user already exists in the custom table
    var customUserGR = new GlideRecord('u_custom_user_table'); // Replace with your custom table name
    customUserGR.addQuery('u_uid', uid); // Assuming 'u_uid' is the field name in the custom table
    customUserGR.query();

    if (!customUserGR.hasNext()) {
        // User does not exist, insert new record
        var newCustomUserGR = new GlideRecord('u_custom_user_table');
        newCustomUserGR.initialize();
        newCustomUserGR.setValue('u_uid', uid);
        newCustomUserGR.setValue('u_name', name);
        newCustomUserGR.setValue('u_active', active);
        newCustomUserGR.setValue('u_email', email);
        newCustomUserGR.setValue('u_phone', phone);
        newCustomUserGR.insert();
    }
}

If my response helped please mark it correct and close the thread so that it benefits future readers.

[Monica Chakrabo]

It worked, I did some changes as per my requirement. Thanks a lot.