Query to find records with the same field value and Update those record

[Sironi]

Hi All,

please help me on this issue,

this is in scoped application, i have used GlideAggreagte initially to get count and update records but as per Servicenow docs To update a record, it is necessary to use GlideRecord.

There are around 20-records was there with number MBD0001021, some records has same VAT_ID, some records not . So which records had same vAT_ID those records of type set with billable.rest of record  type should be non-billable.

if count >1 then update with "billable" / count=1 then update with "non-billable"

Example : 

VAT_ID : field value is 10023

VAT_ID  : field value is 10023

VAT_ID  : field value is 123abd

VAT_ID  : field value is 123abc

VAT_ID  : field value is abc321

 VAT_ID : field value is abc321

VAT_ID  : field value is abc921

var UpdGr = new GlideRecord('x_billable_invoice_details');
UpdGr.addQuery('number','MBD0001021');
UpdGr.addAggregate('COUNT','vat_id');
UpdGr.groupBy('vat_id');
UpdGr.addHaving('COUNT','>',1);
UpdGr.query();
while(UpdGr.next())
{
UpdGr.type='billable';
UpdGr.update();
}

[Jaspal Singh]

Hope above helps. If so, close the thread by marking suitable answer correct.

[Sironi]

Hi Jaspal,

when i applied your script in Backgroun script for number : 1024 it was applied sucessfully.

but when i tried to update 1025 records it was applied "Draft" for all the record 

var UpdGr = new GlideRecord('x_billable_invoice_details');
UpdGr.addQuery('number','MBD0001021');
UpdGr.query();
while(UpdGr.next())
{
var getvatidcount=new GlideRecord('x_billable_invoice_details');
getvatidcount.addQuery('vat_id',UpdGr.vat_id);
getvatidcount.query();
if(getvatidcount.getRowCount()>1)
{
UpdGr.type='Invoice';
UpdGr.update();
}else{
UpdGr.type='Draft';
UpdGr.update();
}
}

 

[Abhijit4]

Hi,

Try below,

var ga = new GlideAggregate("x_billable_invoice_details");
ga.addQuery('number', 'MBD0001021');
ga.groupBy("vat_id");
ga.addAggregate("COUNT", "vat_id");
ga.query();
while (ga.next()) {

    var gr = new GlideRecord("x_billable_invoice_details");
    gr.addQuery("number", "MBD0001021");
    gr.addQuery("vat_id", ga.vat_id);
    gr.query();
    if (ga.getAggregate("COUNT", "vat_id") > 1) {
        while (gr.next()) {
            gr.type = "Invoice";
            gr.update();
        }
    } else {
        if (gr.next()) {
            gr.type = "Draft";
            gr.update();
        }

    }

}

Let me know if you have any further queries.

Please mark this as Correct or Helpful if it helps.

Thanks and Regards,
Abhijit