i Need to exclude duplicates from database view and need to insert uique records in custom table.

[Hareesha]

I wrote a script to remove duplicated from database view and push into custom table.But it is pulling all records from database view to custom table.Appreciate your help.

var x=new GlideRecord("u_skills_updated");//database view
var y=new GlideRecord('u_skill_updated_last_180_days');//custom table
x.query();
var a,b,c,d,e,f,g,h;;
var count1=0
y.query();
var c1=0;
count1=y.getRowCount();
while(x.next())
{
a=x.getElement('grpmem_user.name');
b=x.getElement('grpmem_user.u_business');
c=x.getElement('grpmem_group.parent.name');
d=x.getElement('userskill_skill.name');
e=x.getElement('userskill_skill_level.name');
if(count1==0)//custom table is empty
{

y.setValue('u_user1',a);
y.setValue('u_business1',b);
y.setValue('u_parent',c);
y.setValue('u_skill1',d);
y.setValue('u_proficiency1',e);
y.insert();
count1=count1+1;
}
else //custom table is not empty
{
var y=new GlideRecord('u_skill_updated_last_180_days');//custom table
y.query();
while(y.next())
{
f=y.getElement('u_user1');
g=y.getElement('u_skill1');
h=y.getElement('u_parent');
if(a===f && c===h && d===g)
{
c1=1;
break;
}

}// Y while loop

if(c1!=1)
{
y.setValue('u_user1',a);
y.setValue('u_business1',b);
y.setValue('u_parent',c);
y.setValue('u_skill1',d);
y.setValue('u_proficiency1',e);
y.insert();
count1=count1+1;
c1=0;

}

}//else
}// X while loop

I need to get only 3 records

[Abhijit4]

Hi Hareesha,

You don't need to write such lengthy script, you could try something like below,

var grDatabaseView = new GlideRecord("u_skills_updated");
grDatabaseView.query();
while (grDatabaseView.next()) {
    var grSkillsCustom = new GlideRecord("u_skill_updated_last_180_days");
    grSkillsCustom.addEncodedQuery("u_user1=" + grDatabaseView.grpmem_user.name + "^u_parent=" + grDatabaseView.grpmem_group.parent.name + "^ u_skill1=" + grDatabaseView.userskill_skill.name);
    grSkillsCustom.query();
    if (!grSkillsCustom.hasNext()) {
        var grSkillCustomNewInsert = new GlideRecord("u_skill_updated_last_180_days");
        grSkillCustomNewInsert.initialize();
        grSkillCustomNewInsert.setValue('u_user1', grDatabaseView.grpmem_user.name);
        grSkillCustomNewInsert.setValue('u_business1', grDatabaseView.grpmem_user.u_business);
        grSkillCustomNewInsert.setValue('u_parent', grDatabaseView.grpmem_group.parent.name);
        grSkillCustomNewInsert.setValue('u_skill1', grDatabaseView.userskill_skill.name);
        grSkillCustomNewInsert.setValue('u_proficiency1', grDatabaseView.userskill_skill_level.name);
        grSkillCustomNewInsert.insert();
    }
}

I have used  field names from your script, you might need to change few names if required but above logic could help you to resolve your issue.

Let me know if you have any further queries.

Please mark this as Correct or Helpful if it helps.

Thanks and Regards,
Abhijit
Community Rising Star 2022

[Hareesha]

I think the script that you have used will fetch all records from databse view to custom table.But my requirement is i need to delete duplicate rows and need to insert.

In the below screenshot parent,user,skill(do not need to consider group) are same so i need to eliminate one record.

[Hareesha]

Hi Abhijit,

     First time when i run the script i got only 2197 unique records but the script need to get run daily through a job and i found that it is inserting 395 records when i am running second time and inserting duplicates.

[Abhijit4]

Please share some screenshots where you see duplicate records, make sure that all unique fields that we are using to find duplicate record are included in screenshot.

Hope you have made changes in field names if required, if you have done any changes in script then please share your updated script as well.