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 · Accepted solution]

Hi,

Looks like your loop is terminating too soon and you swapped the "gr" query with the "user" query. Try the following the extra alm_hardware query was removed:

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();
while(item.next()){

  gs.log('The asset: '+ item.variables.asset.getDisplayValue() + ' ' + item.number + ' ' + item.u_requested_for.getDisplayValue() );
  gs.log('Requests: ' + item.getRowCount());

  }

}

[Lucy10]

Thank you very much:)

[DScroggins]

You are welcome. Glad that I could help. 🙂

[Lucy10]

Hi,

In the while loop in the item query I dont want to trigger an event if there was a return.

But trigger an event if there was no return and that sends out one email

Can someone tell me what im doing wrong?

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();
while(item.next()){

   if(item.next(){

//Do nothing

}

else

{

gs.eventQueue(****);

}

}