Nested GlideRecord is returning undefined

[LikeARabbit]

Use case:

I'm pulling together a list of users in the sysapproval_approver table using GlideAggregate based on a few conditions. Inside the while loop I'm unable to successfully pass the GlideRecord to the eventqueue, presumably because it's returning multiple records, the URI in the event record is shown as "sysapproval_approver.do?sys_id=null&sysparm_stack=sysapproval_approver_list.do?sysparm_query=active=true", I'm assuming the highlighted portion is why it's failing. Because of that I'm attempting to run a second GlideRecord query, inside the while loop, to identify the approver and return just their user record from sys_user.

What I have so far:

var gr = new GlideAggregate('sysapproval_approver');
gr.addAggregate('COUNT');
gr.addQuery('state' , 'Requested'); //Check that approval is still requested
gr.addQuery('sys_created_on', '<' ,gs.daysAgo(90)); // Check that approval was created atleast 1 day ago
gr.groupBy('approver');
gr.query();

while(gr.next()) {
		var approvername = gr.approver.name
		var gr2 = new GlideRecord('sys_user');
		gr2.addQuery('name',approvername);
		gr2.query();
		gs.print('Name from gr2:' + gr2.name);
		gs.print(approvername);
		gs.print('- - - - - - - - -')
		gs.eventQueue('approval.reminder',gr2,gr.approver.name);
}


// Which returns the following:
*** Script: Name from gr2:
*** Script: Name from gr:Steve Jobs
*** Script: - - - - - - - - -
*** Script: Name from gr2:
*** Script: Name from gr:Steve Wozniak
*** Script: - - - - - - - - -
*** Script: Name from gr2:
*** Script: Name from gr:Tim Cook
*** Script: - - - - - - - - -

So I can pull the original GlideRecord result within the loop, but the second GlideRecord query (that is defined only within the loop) continuously returns undefined. When I look at a log for the generated event(s) the URI shows as sys_user.do?sys_id=null&sysparm_stack=sys_user_list.do?sysparm_query=active=true, so it's still not passing a sys_id.

Why isn't the second/nested query working? What am I missing?

[Tony Chatfield1]

Hi, do you need to include gr2.next(); in your nested query so that the gliderecord is loaded for processing

 Also, I believe a query on approver.name would be better replaced by a query for the approver.sys_id or possibly approver.toString() as this value is guaranteed to be unique whereas a name may not be.

[LikeARabbit]

Thanks for the suggestions @Tony Chatfield. I tried adding gr2.next(); but it doesn't seem to make a difference. This is the updated script:

var gr = new GlideAggregate('sysapproval_approver');
gr.addAggregate('COUNT');
gr.addQuery('state' , 'Requested'); //Check that approval is still requested
gr.addQuery('sys_created_on', '<' ,gs.daysAgo(90)); // Check that approval was created atleast 1 day ago
gr.groupBy('approver');
gr.query();

while(gr.next()) {
		var approverid = gr.approver.sys_id
		var gr2 = new GlideRecord('sys_user');
		gr2.addQuery('sys_id','approverid');
		gr2.query();
		gr2.next();
		gs.print('Name from gr2:' + gr2.name);
		gs.print('Sys ID from gr:' + approverid);
		gs.print('- - - - - - - - -')
		gs.eventQueue('approval.reminder',gr2,gr.approver.name);
}

It DOES generate events but the events all show an error state similar to this: (notice the sys_id in the URI string is null)

Am I missing something obvious?

[Elijah Aromola]

It should be be:

var gr = new GlideAggregate('sysapproval_approver');
gr.addAggregate('COUNT');
gr.addQuery('state' , 'Requested'); //Check that approval is still requested
gr.addQuery('sys_created_on', '<' ,gs.daysAgo(90)); // Check that approval was created atleast 1 day ago
gr.groupBy('approver');
gr.query();

while(gr.next()) {
		var approverid = gr.approver.sys_id
		var gr2 = new GlideRecord('sys_user');
		gr2.addQuery('sys_id','approverid');
		gr2.query();
		while(gr2.next()) {
		    gs.print('Name from gr2:' + gr2.name);
		    gs.print('Sys ID from gr:' + approverid);
		    gs.print('- - - - - - - - -')
		    gs.eventQueue('approval.reminder',gr2,gr.approver.name);
      }
}

[LikeARabbit]

Thanks @Elijah Aromola - CloudPires , I should have mentioned that I tried wrapping it with while(){} but had even less success. When trying the script you posted as a background script it executes successfully but none of the gs.print statements are printed and no events are created.