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

[Hareesha]

I didn't change any code. I have used exactly as yours.

First time execution of your code.

 Second timeexecution of your code

[Abhijit4]

Hi Hareesha,

Script looks good to me, we would need to debug further to understand the issue.

I would suggest try testing for only one record for now, something like below,

var grDatabaseView = new GlideRecord("u_skills_updated");
grDatabaseView.addEncodedQuery("grpmem_user.name"+testing_user_name);//use here user name that you have sent me.
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();
gs.print(grSkillsCustom.getRowCount());//make sure here we get count as greater than 0 if record already exist in 'u_skill_updated_last_180_days'
    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();
    }
}

If count is coming as 0 then there must be some problem with below query,

grSkillsCustom.addEncodedQuery("u_user1=" + grDatabaseView.grpmem_user.name + "^u_parent=" + grDatabaseView.grpmem_group.parent.name + "^ u_skill1=" + grDatabaseView.userskill_skill.name);

Please debug further to get to solution.

Please mark this as Correct or Helpful if it helps.

Thanks and Regards,
Abhijit
Community Rising Star 2022

[Hareesha]

Hi Abhijit,

I am getting perfectly.Your code is working fyn. My custom table user is accepting only 10 length.Thank you so much for your help

[MR1]

@Abhijit4- I tired to implement your solution. But it was creating undefined records.
Could you please look into it?

https://www.servicenow.com/community/itsm-forum/help-to-remove-duplicate-records-from-database-view-...

[Hareesha]

I have one more question Abhijit ,What if a user or parent group or skill is deleted from database view.It wont get deleted from custom table right.Can you help me with that implementation as well.