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

Sironi · ‎02-01-2022

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 123abd

VAT_ID : field value is 123abc

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 · ‎02-02-2022

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();
}

}

View solution in original post

Sai Kumar B · ‎02-01-2022

@Sironi Try the below code

var UpdGr = new GlideRecord('x_billable_invoice_details');
UpdGr.addQuery('number','MBD0001021');
UpdGr.query();
if(UpdGr.hasNext()) { //If there is more than one record
UpdGr.setValue('type', 'billable');
UpdGr.update();
}

Sironi · ‎02-02-2022

Hi Sai,

kindly help me where we are checking VAT_ID?. same VAT_ID record set with billable, unique VAT_Id records set with non-biilable

There are around 20-records was there with number MBD0001021, some records has same VAT_ID, some records not .

Sai Kumar B · ‎02-02-2022

Try the below code

var UpdGr = new GlideAggregate('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())
{
var updateRec = new GlideRecord('x_billable_invoice_details');
if(updateRec.get(UpdGr.sys_id)){
updateRec.type = 'billable';
updateRec.update();
}
}

Sironi · ‎02-02-2022

Hi Sai,

Sorry, Not giving any response action. not updating any record.

var UpdGr = new GlideAggregate('x_billable_invoice_details');
UpdGr.addQuery('number','MBD0001017');
UpdGr.addAggregate('COUNT','vat_id');
UpdGr.groupBy('vat_id');
UpdGr.addHaving('COUNT','>',1);
UpdGr.query();
while(UpdGr.next())
{
gs.info("Gate 1");
var updateRec = new GlideRecord('x_billable_invoice_details');
if(updateRec.get(UpdGr.sys_id)){
gs.info("Gate 2");
updateRec.type= 'billable';
updateRec.update();
}
}

Entering into Gate1 only. not entering into Gate2