How to leave top 100 records and delete the remaining ?

jay1111 · ‎06-15-2020

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

MrMuhammad · ‎06-15-2020

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!

Regards,
Muhammad

View solution in original post

DirkRedeker · ‎06-15-2020

Hi

Another simple solution is to GlideRecord the table, loop 100 times the"gr.next()", if there are even 100 records.

After that loop, go to the next loop, having a "while.next()" and delete record by record until the end of the table.

Let me know if that answered your question and mark my answer as correct and helpful.

BR Dirk