My query returns rows , but while(gr.next()) does not loop through them ... why?

[IanGlencross]

var yearAgo = gs.daysAgo(365);

gr = new GlideRecord("sys_template");
gr.addQuery('u_pre_approved', true);
gr.addQuery("u_approving_rfc.cab_date", "<", yearAgo);
gr.query();

gs.print('** DEBUG ** ROWCOUNT :'+gr.getRowCount());

while(gr.next()) {
   gs.print('** DEACTIVATING ** '+gr.name);
   gr.active=false;
   gr.update();
}

The above code is the basis of a Scheduled job I want to run to deactivate PreApproved templates if they expire.

When run as Scripts - Background this is the result :

My query works fine, as the DEBUG gives the correct row count, however the while loop does not seem to run, and the test template remains active.

Moreover :  even if I simply the script to

gr = new GlideRecord('sys_template');
gr.addQuery('u_pre_approved', true);
gr.query();

gs.print('ROWCOUNT :'+gr.getRowCount());

while(gr.next()) {
  gs.print('** DEACTIVATING ** '+gr.name);
}

I get a similar result, the correct rowcount but no looping over the query results  :

Does anyone have any ideas why my glideRecord does not want to loop?

[Ankur Bawiskar]

Hi,

Can you try below script in background: check whether read is allowed or not?

If row count is 1 then it should go inside the if or while loop

var yearAgo = gs.daysAgo(365);

gr = new GlideRecord("sys_template");
gr.addQuery('u_pre_approved', true);
gr.addQuery("u_approving_rfc.cab_date", "<", yearAgo);
gr.query();

gs.print('** DEBUG ** ROWCOUNT :'+gr.getRowCount());
gs.print('Can Read: ' + gr.canRead());

if(gr.next()) {
gs.print('** DEACTIVATING ** '+gr.name);
gr.active=false;
gr.update();
}

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

[IanGlencross]

Output from above script  :

and the test template was not deactivated.

[Ankur Bawiskar]

Hi,

I found something interesting. Even I was unable to change the active field in my case. I debugged there is 1 query business rule that might be causing the issue so I used setWorkflow(false) before query and it updated the flag

update your code as below and try once:

var yearAgo = gs.daysAgo(365);

var gr = new GlideRecord("sys_template");
gr.addQuery('u_pre_approved', true);
gr.addQuery("u_approving_rfc.cab_date", "<", yearAgo);

gr.setWorkflow(false);

gr.query();

gs.print('** DEBUG ** ROWCOUNT :'+gr.getRowCount());

while(gr.next()) {
gs.print('** DEACTIVATING ** '+gr.name);
gr.active=false;
gr.update();
}

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

[IanGlencross]

UPdate

it's something about the sys_template table  as this code :

var tempgr = new GlideRecord('core_company');
tempgr.addActiveQuery();
tempgr.query();

gs.print('NEXT :'+tempgr.hasNext());

while(tempgr.next()) {
    gs.print('** DEACTIVATING ** '+tempgr.name);
}

runs exactly as expected : 

[Ankur Bawiskar]

Hi,

Can you check any acl on that table which blocks updating the record?

var yearAgo = gs.daysAgo(365);

gr = new GlideRecord("sys_template");
gr.addQuery('u_pre_approved', true);
gr.addQuery("u_approving_rfc.cab_date", "<", yearAgo);
gr.query();

gs.print('** DEBUG ** ROWCOUNT :'+gr.getRowCount());
gs.print('Can Read: ' + gr.canRead());

if(gr.next()) {
gs.print('** DEACTIVATING ** '+gr.name);

gs.print('Can Write: ' + gr.canWrite());

gr.active=false;

gr.setWorkflow(false);

gr.update();
}

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