Help - To remove duplicate records from database view and insert in a custom table via Schduled job

[MR1]

Hi All,

I need assistance with removing duplicate records from the database view using a scheduled job.

I made a custom table and copied only the records from the database view into it.
Although I wrote this background script, undefined records are being inserted into the custom table.
I'm not sure where I'm going wrong or why undefined values are being used.

The custom fields on the custom table (u hr) are string types (u user name, u first name). Date type and reference type were created on custom table yet.

var grDatabaseView = new GlideRecord("u_hr_data"); // database view
grDatabaseView.addEncodedQuery("tsk_u_access_grant_id.grant=233c6475db01bb40e613327e9d96195a^access_status=active"); // encodded query 
grDatabaseView.setLimit(100);
grDatabaseView.query();
while (grDatabaseView.next()) {
    var grSkillsCustom = new GlideRecord("u_hr"); // custom table
grSkillsCustom.addEncodedQuery("u_user_name=" + grDatabaseView.usr.usr_user_name + "^u_first_name=" + grDatabaseView.usr.usr_first_name);
//grSkillsCustom.setLimit(10);
    grSkillsCustom.query();
    gs.print(grSkillsCustom.getRowCount());
    if (!grSkillsCustom.hasNext()) {
        var grSkillCustomNewInsert = new GlideRecord("u_hr");
        grSkillCustomNewInsert.initialize();
        grSkillCustomNewInsert.setValue('u_user_name', grDatabaseView.usr.usr_user_name);
       grSkillCustomNewInsert.setValue('u_first_name', grDatabaseView.usr.usr_first_name);
        grSkillCustomNewInsert.insert();
       //  gs.print('usr_first_name');
    }
}

Thanks

[AnubhavRitolia]

Hi @MR1 

Database View are just virtual table and that won't that any values. It is just a view to show multiple tables record with a relationship.

You cannot get values from it.

If you want values from Database view, you need to GlideRecord their child tables and get values through them only.

[MR1]

Thanks for your response!

I found the similar thing over here and they were able to accomplish it.

https://www.servicenow.com/community/now-platform-forum/i-need-to-exclude-duplicates-from-database-v...