Unable to retrieve value of field using GlideAggregate

[reginabautista]

Hi guys

There's something weird going on and I couldn't figure out why. I am trying to get the value of the Classification field but for some reason it always return as blank. I checked the dictionary and see that it has this attribute   => edge_encryption_enabled=true. Could this be the reason why?

Here's the code I was testing using Background Script:

var shopStr = '';

var agg = new GlideAggregate('incident');

agg.addEncodedQuery('active=true^u_channel=545fc64037baaa0084135ad843990ed0^opened_atRELATIVEGE@dayofweek@ago@7');

agg.addAggregate('COUNT', 'u_classification');

agg.groupBy('u_requester');

agg.query();

while (agg.next()) {

requester = agg.u_requester;

var classification = agg.u_classification;

var classificationCount = agg.getAggregate('COUNT', 'u_classification');

if(parseInt(classificationCount)>=3){

gs.log('Shop:' + requester.getDisplayValue() + ' Classification =[' + classification + '] #tickets=' + classificationCount);

}

}

[bernyalvarado]

Hi Regina,

For the sake of troubleshooting, would you mind trying without doing an aggregate on u_classification?

Thanks,

Berny

[reginabautista]

Hi Benny

Hmm I can get the classification when aggregate is removed...This is basically a copy of the sample code in the WIKI but for some reason I couldn't get it to work

http://wiki.servicenow.com/index.php?title=GlideAggregate#gsc.tab=0

To get a count of all of the open incidents by category the code is:

var count = new GlideAggregate('incident');count.addQuery('active', 'true');count.addAggregate('COUNT', 'category');count.query();     while (count.next()) {     var category = count.category;     var categoryCount = count.getAggregate('COUNT', 'category');     gs.log("The are currently " + categoryCount + " incidents with a category of " + category);}

The output is:

             *** Script: The are currently 1.0 incidents with a category of Data                   *** Script: The are currently 11.0 incidents with a category of Enhancement               *** Script: The are currently 1.0 incidents with a category of Implementation               *** Script: The are currently 197.0 incidents with a category of inquiry               *** Script: The are currently 13.0 incidents with a category of Issue               *** Script: The are currently 1.0 incidents with a category of                 *** Script: The are currently 47.0 incidents with a category of request

[bernyalvarado · Accepted solution]

Hi Regina,

I created an example using OOB fields so that you can get an idea of how it needs to be done to accomplish what you're looking for. I believe that perhaps what you're missing in your original code is to include another groupBy by u_classification

Here goes a sample code so that you can see a working version:

var shopStr = '';
var agg = new GlideAggregate('incident');
agg.addEncodedQuery('active=true');
agg.addAggregate('COUNT', 'category');
agg.groupBy('category');
agg.groupBy('caller_id');
agg.query();
while (agg.next()){
   var callMan = agg.caller_id;
   var category = agg.category;
   var categoryCount = agg.getAggregate('COUNT', 'category');
   gs.print('Caller:' + callMan.getDisplayValue() + ' Classification =[' + category + '] #tickets=' + categoryCount);
}

Output:

*** Script: Caller:Carol Coughlin Classification =[] #tickets=1
*** Script: Caller:Joe Employee Classification =[] #tickets=1
*** Script: Caller:Luke Wilson Classification =[] #tickets=1
*** Script: Caller:Taylor Vreeland Classification =[] #tickets=1
*** Script: Caller:Joe Employee Classification =[database] #tickets=1
*** Script: Caller:Bow Ruggeri Classification =[hardware] #tickets=2
*** Script: Caller:Don Goodliffe Classification =[hardware] #tickets=1
*** Script: Caller:Jerrod Bennett Classification =[hardware] #tickets=1
*** Script: Caller:Bud Richman Classification =[inquiry] #tickets=1
*** Script: Caller:Charlie Whitherspoon Classification =[inquiry] #tickets=1
*** Script: Caller:Fred Luddy Classification =[inquiry] #tickets=1
*** Script: Caller:Joe Employee Classification =[inquiry] #tickets=3
*** Script: Caller:Margaret Grey Classification =[inquiry] #tickets=1
*** Script: Caller:Sam Sorokin Classification =[inquiry] #tickets=1
*** Script: Caller:System Administrator Classification =[inquiry] #tickets=4
*** Script: Caller:Beth Anglin Classification =[network] #tickets=1
*** Script: Caller:Bud Richman Classification =[network] #tickets=1
*** Script: Caller:Fred Luddy Classification =[network] #tickets=1
*** Script: Caller:Joe Employee Classification =[network] #tickets=1
*** Script: Caller:Bud Richman Classification =[software] #tickets=2
*** Script: Caller:Christen Mitchell Classification =[software] #tickets=1
*** Script: Caller:Fred Luddy Classification =[software] #tickets=3
*** Script: Caller:Joe Employee Classification =[software] #tickets=1

I hope this helps!

[reginabautista]

Awesome, worked like a charm. Thanks Berny!