question Need help with this script, trying to delete records from a user table that fit a condition in Developer forum

Need help with this script, trying to delete records from a user table that fit a condition

nico2 — Wed, 24 Apr 2019 20:50:41 GMT

Good afternoon,

Need help with this script, trying to delete records from a user table that fit a condition. But record count displays zero.

Condition that I set is records that were created on 04/18/2019.

I have the following, I have not added the delete statement making sure I get the right count first.

Any ideas are welcome.

removechecksjan();

function removechecksjan() {
var gr = new GlideRecord("u_check_drawer");
gr.addEncodedQuery('sys_created_on=2019-04-18');
gr.query();
var deleteCount = 0;
while(gr.next()){
gr.setWorkflow(false);
deleteCount++;
}
gs.print('Records Deleted: '+ deleteCount);
}

Re: Need help with this script, trying to delete records from a user table that fit a condition

Prateek kumar — Wed, 24 Apr 2019 20:55:31 GMT

removechecksjan();


function removechecksjan() {
var gr = new GlideRecord("u_check_drawer");

gr.addEncodedQuery("sys_created_onON2019-04-18@javascript:gs.dateGenerate('2019-04-18','start')@javascript:gs.dateGenerate('2019-04-18','end')");

gr.query();
while(gr.next()){
gr.setWorkflow(false);
gr.deleteRecord();
gs.print('Records Deleted: '+ deleteCount);
}

 }

Re: Need help with this script, trying to delete records from a user table that fit a condition

vkachineni — Wed, 24 Apr 2019 21:07:35 GMT

First check the record count

removechecksjan();

function removechecksjan()

{

var deleteCount = 0;
var gr = new GlideRecord("u_check_drawer");
gr.addEncodedQuery('sys_created_on=2019-04-18');
gr.query();

var rec_count = gr.getRowCount();

gs.print('Records to be deleted : '+ rec_count );

while(gr.next())

{
gr.setWorkflow(false);
deleteCount++;

}
gs.print('Records Deleted: '+ deleteCount);
}

Re: Need help with this script, trying to delete records from a user table that fit a condition

BrianD502676804 — Wed, 24 Apr 2019 21:23:08 GMT

Hi nico,

Try using the table filter in List view to generate your query statement. The field sys_created_on is a GlideDateTime field, so I do not think it would be equal to a simple date. Using the table filter gives this result for your query statement:

sys_created_onON2019-04-18@javascript:gs.dateGenerate('2019-04-18','start')@javascript:gs.dateGenerate('2019-04-18','end')

Once you build the filter on a list, just right-click on the filter breadcrumbs to copy the underlying query statement. Give that a try.

Thanks,

-Brian

Re: Need help with this script, trying to delete records from a user table that fit a condition

A-N — Wed, 24 Apr 2019 21:24:19 GMT

I think problem is with your date check in the EncodedQuery

Try this out....

removechecksjan();

function removechecksjan() {
var gr = new GlideRecord("u_check_drawer");
//gr.addEncodedQuery('sys_created_on=2019-04-18');
gr.addEncodedQuery("sys_created_onON2019-04-18@javascript:gs.dateGenerate('2019-04-18','start')@javascript:gs.dateGenerate('2019-04-18','end')");
gr.query();
var deleteCount = 0;
while(gr.next()){
gr.setWorkflow(false);
deleteCount++;
}
gs.print('Records Deleted: '+ deleteCount);
}

Re: Need help with this script, trying to delete records from a user table that fit a condition

Prateek kumar — Wed, 24 Apr 2019 21:29:42 GMT

You are Absolutely spot on.

Re: Need help with this script, trying to delete records from a user table that fit a condition

nico2 — Fri, 26 Apr 2019 20:13:18 GMT

Thank you all for your help. I tested the final code that was recommended and I received the correct count that will be deleted.

Now for the final phase should I use deleteRecord() or deleteMultiple () ?

do I still use gr.next ?

Let me know your opinion

removechecksjan();

function removechecksjan() {
var gr = new GlideRecord("u_check_drawer");
//gr.addEncodedQuery('sys_created_on=2019-04-18');
gr.addEncodedQuery("sys_created_onON2019-04-18@javascript:gs.dateGenerate('2019-04-18','start')@javascript:gs.dateGenerate('2019-04-18','end')");
gr.query();
var deleteCount = 0;
while(gr.next()){
gr.setWorkflow(false);
deleteCount++;
}
gs.print('Records Deleted: '+ deleteCount);
}

Re: Need help with this script, trying to delete records from a user table that fit a condition

BrianD502676804 — Mon, 29 Apr 2019 18:17:06 GMT

Hi Nico,

Although I usually like to use 'deleteMultiple()' for efficiency's sake, in your case I would not. If you read through the reference on GlideRecord, there are a couple of counterindications:

Your table is named 'u_check_drawer', so I'm guessing there may be some currency values involved. From the reference they state:
"Do not use deleteMultiple() on tables with currency fields. Always delete each record individually"
Also, I noted you are employing 'setWorkflow(false)', from the ref this will not apply to cascade deletes:
"Note: The setWorkflow() method is ignored when subsequently using either the deleteProblem() or deleteMultiple() methods to cascade delete."

Thanks,

-Brian

Re: Need help with this script, trying to delete records from a user table that fit a condition

nico2 — Tue, 30 Apr 2019 22:04:16 GMT

Thank you all for your help, tested the script in my dev environment. Deployment/validation successful.