How to leave top 100 records and delete the remaining ?

[jay1111]

Hi Experts,

I got a requirement to leave top 100 records and delete the remaining.

var gr = new GlideRecord("custom table");
gr.orderByDesc('created');
gr.setLimit(100);
gr.query();
while(gr.next()){
gr.deleteRecord();
}

By using above script i am able to delete top 100 records. But, I want quite opposite to the above script.

How can i filter top 100 records and delete the remaining?

I really appreciate the help.

Thanks,

Jay

[sachin_namjoshi]

You can configure auto flush job to exclude top 100 records and remove remaining.

https://docs.servicenow.com/bundle/orlando-application-development/page/administer/auto-test-framewo...

Regards,

Sachin

[jay1111]

Thanks Sachin!. I like this idea, I will use it future.

Jay!

[asifnoor]

Hi,

If you want to do it thorugh script, here is how you can do it. First fetch the top 100 records by ascending order, put them in an array. Then query again excluding those 100 records and delete them.

var gr = new GlideRecord("custom table");
gr.orderByDesc('created');
gr.setLimit(100);
gr.query();
var sys_ids = [];
while(gr.next()){
sys_ids.push(gr.sys_id);
}
var gr1 = new GlideRecod("custom table");
gr1.addEncodedQuery("sys_idNOT IN"+sys_ids);
gr1.query();
while(gr1.next()) {
gr1.deleteRecord();
}

Kindly mark the comment as a correct answer and helpful if it helps to solve your problem.

Regards,
Asif
2020 ServiceNow Community MVP

[MrMuhammad · Accepted solution]

I am assuming this is one time code so I try to keep it simple. First I Get Total Count of table records and subtracted 100 so that we will get total number of record we need to delete. Then I glide Record the table again to setLimit at the remaining count and picked up old records using ascending order sort (to start from older record to move to the latest that way the remaining 100 will be leftover). Please try and let me know if you face any issues.

 var gr = new GlideRecord("custom table");
 gr.orderBy('created'); // make sure the created is correct.
 gr.query();
 
 var count = gr.getRowCount(); // get total record count;
 var leftHundred = count - 100;
 
 gs.print("Count after subtracting 100 - " + leftHundred);

 if(gr.next()){

    var grC = new GlideRecord("custom table");
    grC.orderBy('created'); // make sure the created name is correct.
    grC.setLimit(leftHundred);
    grC.query();

    gs.print("TOTAL COUNT AFTER SUBTRACTION - " + grC.getRowCount());
    grC.deleteMultiple();
}

Please mark this accepted and helpful if your question has been answered. Thanks!