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]

I would simplfy it as below.

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

[Sironi]

Hi Jaspal,

line no3: check same VAT_ID ?

UpdGr.addQuery('vat_id','>',1);

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

[Jaspal Singh]

Try below

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='billable';
UpdGr.update();
}

}

[Sironi]

Thanks Jaspal, it is NOT working .

I have a doubt here , don't we need to query sys_id ???

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('sys_id',UpdGr.sys_id); // is this step not required ?

getvatidcount.addQuery('vat_id',UpdGr.vat_id);
getvatidcount.query();
if(getvatidcount.getRowCount()>1)
{
UpdGr.type='billable';
UpdGr.update();
}else{
UpdGr.type='non-billable';
UpdGr.update();
}
}

please tell me how it is working.

[Jaspal Singh · Accepted solution]

No its not required as all we need is to check the vat_id & get the count.

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); //This gives the vat_id of UpdGr gliderecorded function above

getvatidcount.query();
if(getvatidcount.getRowCount()>1) //we check the count for vatids here that we compare in above line to check if count is more than 1. If yes do below else do nothing

{
UpdGr.type='billable';
UpdGr.update();
}

}