Glide query 2 tables

[Lucy10]

Hi,

I need assistance with querying one table and using the results to query on another table.

The 1st query is on the hardware table and it working and it returns multiple users. 

Then I need to query the sc_req_item to check if they got active request item open , but its only returning one results but im expecting a lot more.

 Code:

var asset_query = "*****";

var ast = new GlideAggregate('alm_hardware');
ast.addEncodedQuery(asset_query);
ast.groupBy('assigned_to');
ast.query();
while(ast.next()){
var gr = new GlideRecord('alm_hardware');
gr.get('assigned_to', ast.assigned_to);

}

var users_query = 'active=true^cat_item=be1dae433768ef793d35616043990e88^ORcat_item=1610da8b37a4ef793d35616043990e2f';
var user = new GlideRecord('sc_req_item');
user.addEncodedQuery(users_query);
user.addQuery('u_requested_for', gr.assigned_to);

gr.query();

while(gr.next())

{

gs.log('The asset: '+ gr.variable_pool.asset.getDisplayValue() + ' ' + gr.number + ' ' + gr.u_requested_for.getDisplayValue() );
gs.log('Requests: ' + gr.getRowCount());
}

Thanks,

L

[DScroggins]

Try this instead:

var asset_query = "*****";


var ast = new GlideAggregate('alm_hardware');
ast.addEncodedQuery(asset_query);
ast.groupBy('assigned_to');
ast.query();
while(ast.next()){


var users_query = 'active=true^cat_item=be1dae433768ef793d35616043990e88^ORcat_item=1610da8b37a4ef793d35616043990e2f';
var item = new GlideRecord('sc_req_item');
item.addEncodedQuery(users_query);
item.addQuery('u_requested_for', ast.assigned_to);
item.query();
if(!item.hasNext()){

  gs.eventQueue(****);

   }

[Lucy10]

Thanks for your assistance but I still cant get it to work.

if(!item.hasNext()){

  gs.eventQueue(****);

   }

This part of the script is still triggering the event that fires the emails. Users who have an open active request item shouldn’t be receiving the email.

Thanks,

L

[DScroggins]

That tells me the following script is not returning results when you are expecting it to i.e. when it finds no open RITM's for the specified user:

var users_query = 'active=true^cat_item=be1dae433768ef793d35616043990e88^ORcat_item=1610da8b37a4ef793d35616043990e2f';
var item = new GlideRecord('sc_req_item');
item.addEncodedQuery(users_query);
item.addQuery('u_requested_for', ast.assigned_to);
item.query();

Are you able to run that script through a background script or Xplore and see what the output is? Try using a user which should have an active open RITM. From there you can dig down into what the underlying issue is.