Help with glide aggregate script(s)

robhaas
Tera Contributor

‎04-21-2017 08:33 AM

I have the below glide aggregate script I am running in scripts background and need help with a few things.

1. Right now I am having to declare 2 variables before running the query. The reason for this is that the two fields I am attempting to match, one is a single reference to the sys_user table (openedby) and the other is a list reference to the sys_user table (receivers) - possible multi-user.   I am needing to know if it is possible to put the two variables inside of the aggregate or make it work without them being declared outside of the query.

var openedby = ar.opened_by.toString();
var receivers = ar.receivers.toString();
var ar = new GlideAggregate('x_access_request');
ar.addQuery('sys_created_on', '>=', gs.beginningOfToday());
ar.addOrCondition('sys_created_on', '<=', gs.endOfToday());
ar.addQuery(openedby, '=', receivers);
ar.addAggregate('COUNT');
ar.query();
var total = 0;
while (ar.next()) {
total = ar.getAggregate('COUNT');
var category = ar.getLabel();
}
gs.print('Total: ' + total);
gs.print('Category: ' + category);

2. The next two parts revolve around the same query. I need to run the same conditions but the 2 variations are:

-Where receivers contains openedby and more users

-Where receivers does not contain openedby but does contain other users

Any and all help is greatly appreciated

Labels:
0 Helpfuls
  • 1,169 Views
1 REPLY 1

phillipkeigley
Kilo Expert

‎04-21-2017 09:16 AM

Hi Robert,



I have worked on similar requirements.   I have two suggestions for you.



1)   No not possible, need 2 queries. (to my knowledge).


2)   Do not use glideAggregate.   It just make things more difficult.   Get your counts using GlideRecord / getRowCount() .


3)   Push your values to arrays and use arrayUtil   for advanced comparison.



I would head down this road:



var openedby = ar.opened_by.toString();   //single

var receivers = ar.receivers.toString();   //multiple


var rec= []; 

var op=[];


var target = new GlideRecord('x_access_request');   

target.addQuery('sys_created_on', '>=', gs.beginningOfToday());   

target.addOrCondition('sys_created_on', '<=', gs.endOfToday());   

target.addQuery(openedby, '=', receivers);   

target.query();

gs.log('Count: ' + target.getRowCount());

var how_many_recievers = target.getRowCount();

while(target.next()){

       rec.push(target.id.toString());

       

}


var target_2 = new GlideRecord('x_access_request');   

target_2.addQuery('sys_created_on', '>=', gs.beginningOfToday());   

target_2.addOrCondition('sys_created_on', '<=', gs.endOfToday());   

target_2.addQuery('openedby', 'IN', rec);   //opendby exists in the array of recievers   

target_2.query();

gs.log('Count: ' + target_2.getRowCount());

var how_many_recievers = target_2.getRowCount();

while(target_2.next()){

       op.push(target_2.id.toString());

       

}

0 Helpfuls