How to avoid create of duplicate record through the server side scripting(fix script) ?

[sujan0119]

Hi All,

I have configured the below Fix script to create the records in a table, if there is no existed record available with the same field values and its getting the initial field values from another table queried, However Its creating duplicate records even if the record existed in the table with same field values.

Can anyone help me on this ?

var rmchk = new GlideRecord('x_cls_clear_skye_i_group_operations');
rmchk.addEncodedQuery("active=false^roleISNOTEMPTY^u_environment_displayLIKEIPM^state=3");
rmchk.setLimit(2);
rmchk.query();
gs.info('Subhrajit:' + rmchk.getRowCount());
while (rmchk.next()) {
    gs.info('Subhrajit:');
    var ro = rmchk.role;
    var pro = rmchk.profile;
    var rm = new GlideRecord('x_cls_clear_skye_i_m2m_accounts_roles');
    rm.addQuery("roles", ro);
    rm.addQuery("profile", pro);
    if (!rm.next()) {
        gs.info('Subhrajit1:');
        var rmc = new GlideRecord('x_cls_clear_skye_i_m2m_accounts_roles');
        rmc.initialize();
        rmc.roles = ro;
        rmc.profile = pro;
        rmc.insert();
    }
}

Thanks  

[Peter Bodelier]

Hi @sujan0119 

You are forgetting to query:

var rmchk = new GlideRecord('x_cls_clear_skye_i_group_operations');
rmchk.addEncodedQuery("active=false^roleISNOTEMPTY^u_environment_displayLIKEIPM^state=3");
rmchk.setLimit(2);
rmchk.query();
gs.info('Subhrajit:' + rmchk.getRowCount());
while (rmchk.next()) {
    gs.info('Subhrajit:');
    var ro = rmchk.role;
    var pro = rmchk.profile;
    var rm = new GlideRecord('x_cls_clear_skye_i_m2m_accounts_roles');
    rm.addQuery("roles", ro);
    rm.addQuery("profile", pro);
    rm.query();
    if (!rm.next()) {
        gs.info('Subhrajit1:');
        var rmc = new GlideRecord('x_cls_clear_skye_i_m2m_accounts_roles');
        rmc.initialize();
        rmc.roles = ro;
        rmc.profile = pro;
        rmc.insert();
    }
}

[sujan0119 · Accepted solution]

Hi @Peter Bodelier ,

Thanks for the support, I want to do few advance logs to check the script, how many records would be created in the x_cls_clear_skye_i_m2m_accounts_roles table for which records(tickets) in the x_cls_clear_skye_i_group_operations tabke. Can you please guide me on this ?

I tried the below,

var rmchk = new GlideRecord('x_cls_clear_skye_i_group_operations');
rmchk.addEncodedQuery("active=false^roleISNOTEMPTY^u_environment_displayLIKEIPM^state=3^request_ref=9bfaa610db1e230001559447db961954");
rmchk.query();
gs.info('Subhrajit1:' + rmchk.getRowCount());
while (rmchk.next()) {
gs.info('Subhrajit:');
var ro = rmchk.role;
var pro = rmchk.profile;
var endDate = rmchk.end_date;
var rm = new GlideRecord('x_cls_clear_skye_i_m2m_accounts_roles');
rm.addQuery("roles", ro);
rm.addQuery("profile", pro);
rm.query();
if (!rm.next()) {
gs.info('Subhrajit2:' + !rm.getRowCount());
var rmc = new GlideRecord('x_cls_clear_skye_i_m2m_accounts_roles');
rmc.initialize();
rmc.roles = ro;
rmc.profile = pro;
rmc.u_end_date = endDate;
rmc.insert();
}
}

[Peter Bodelier]

Hi @sujan0119

Sure, just add a counter in there:

var count = 0;
var rmchk = new GlideRecord('x_cls_clear_skye_i_group_operations');
rmchk.addEncodedQuery("active=false^roleISNOTEMPTY^u_environment_displayLIKEIPM^state=3^request_ref=9bfaa610db1e230001559447db961954");
rmchk.query();
gs.info('Subhrajit1:' + rmchk.getRowCount());
while (rmchk.next()) {
gs.info('Subhrajit:');
var ro = rmchk.role;
var pro = rmchk.profile;
var endDate = rmchk.end_date;
var rm = new GlideRecord('x_cls_clear_skye_i_m2m_accounts_roles');
rm.addQuery("roles", ro);
rm.addQuery("profile", pro);
rm.query();
if (!rm.next()) {
count++;
gs.info('Subhrajit2:' + !rm.getRowCount());
var rmc = new GlideRecord('x_cls_clear_skye_i_m2m_accounts_roles');
rmc.initialize();
rmc.roles = ro;
rmc.profile = pro;
rmc.u_end_date = endDate;
rmc.insert();
}
}
gs.info('Subhrajit3:' + count);