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

jay1111 · ‎06-16-2020

Thanks Muhammad!

You got me so accurate.

Do i need to do any change to the script if i want to add this to a schedule job and run it every 30 days?

Thanks,

Jay

MrMuhammad · ‎06-16-2020

The same code is reuseable in Scheduled Job, Background Script and Fix Script. Let me know if you face any issues, I am here to help.

Regards,
Muhammad

jay1111 · ‎06-16-2020

Thanks much!. I appreciate your help.

MrMuhammad · ‎06-16-2020

Glad to be help. Happy learning!

Regards,
Muhammad

Ankur Bawiskar · ‎06-16-2020

Hi,

below should work fine; no need to query table 2 times

var count = 200;

var gr = new GlideRecord("custom table");
gr.orderByDesc('sys_created_on');
gr.setLimit(count);
gr.query();
while(gr.next()){

if(count <= 100){
gr.deleteRecord();
}

count--;
}

Mark ✅ Correct if this solves your issue and also mark 👍 Helpful if you find my response worthy based on the impact.
Thanks
Ankur

Regards,
Ankur
✨ Certified Technical Architect || ✨ 9x ServiceNow MVP || ✨ ServiceNow Community Leader