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?

[Ashutosh Munot1 · Accepted solution]

Hi,

This works for me:

var gr = new GlideAggregate('sysapproval_approver');
gr.addQuery('state' , 'Requested');
gr.addQuery('sys_created_on', '<' ,gs.daysAgo(1));
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);// This approverid was in ' ' it should not be in  ' '
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);
}
}

Thanks,
Ashutosh

[Ashutosh Munot1]

Hi,

I changed the days to 1 instead of 90. Please change it as per your need.

Thanks,
Ashutosh

[LikeARabbit]

Good catch @Ashutosh Munot ! Removing the single quotes around approverid did the trick.

Thanks!