Why is getAggregate count returns null?

Go to solution
dalton1
Giga Expert

‎06-08-2015 02:53 AM

Hi,

I have the code below and I want to get the record count for the table with my simple query applied. But itemOptionCount always returns a null value. But when I place the itemOption object in a while loop, it returns atleast two records. What am I missing?

var itemOption = new GlideAggregate('sc_item_option');

itemOption.addAggregate('COUNT', null);

itemOption.addQuery('u_import_comments','STARTSWITH',importGUID);

itemOption.orderBy('u_import_comments');

itemOption.query();

var itemOptionCount = itemOption.getAggregate('COUNT');

  • 5,252 Views
1 ACCEPTED SOLUTION

Go to solution
poyntzj
Kilo Sage
In response to dalton1

‎06-08-2015 03:57 AM

You need the if(itemOption.next()) to return the aggregate value


without it, it will not work.


Likewise, it you try to add an orderBy, it will return just 1 record


If you do use the if(itemOption.next()) then you cannot use a while loop at the same time


If you want to do a query and return the number of records and then process them, you two choices seem to be


run the query as a GlideAggregate and get the count, then run the query as a GlideRecord and get the data


run the query as a GlideRecord and use getRowCount



The below are what I tested with


Works


var itemOption = new GlideAggregate('sc_item_option');   



itemOption.addQuery('value','STARTSWITH','Buyer');   



itemOption.addAggregate('COUNT');   



itemOption.query();   



if (itemOption.next())   



       gs.print(itemOption.getAggregate('COUNT'));  


Returns just 1 as the answer (not the value I had above)


var itemOption = new GlideAggregate('sc_item_option');   



itemOption.addQuery('value','STARTSWITH','Buyer');   



itemOption.orderBy('sys_created_on');



itemOption.addAggregate('COUNT');   



itemOption.query();   



if (itemOption.next())   



       gs.print(itemOption.getAggregate('COUNT'));  


Does Not work


var itemOption = new GlideAggregate('sc_item_option');   



itemOption.addQuery('value','STARTSWITH','Buyer');   



itemOption.addAggregate('COUNT');   



itemOption.query();   



gs.print(itemOption.getAggregate('COUNT'));  


Returns just the count


var itemOption = new GlideAggregate('sc_item_option');   



itemOption.addQuery('value','STARTSWITH','Buyer');   



itemOption.addAggregate('COUNT');   



itemOption.query();   



if (itemOption.next())   



{



       gs.print(itemOption.getAggregate('COUNT'));   



       var i=0;



       while(itemOption.next())



       {



               gs.print(itemOption.sys_created_on + ' : ' + itemOption.value);



               i++



               if(i > 10)



                       break;



       }       



}


Works using getRowCount


var itemOption = new GlideRecord('sc_item_option');   



itemOption.addQuery('value','STARTSWITH','Buyer');   



itemOption.orderBy('sys_created_on','desc');



itemOption.query();   



gs.print(itemOption.getRowCount());



var i=0;



while(itemOption.next())



{



       gs.print(itemOption.sys_created_on + ' : ' + itemOption.value);



       i++



       if(i > 10)



               break;



}


works using GlideAggregate and then GlideRecord


var itemOption = new GlideAggregate('sc_item_option');   



itemOption.addQuery('value','STARTSWITH','Buyer');   



itemOption.addAggregate('COUNT');   



itemOption.query();   



if (itemOption.next())   



       gs.print(itemOption.getAggregate('COUNT'));   






var itemOption = new GlideRecord('sc_item_option');   



itemOption.addQuery('value','STARTSWITH','Buyer');   



itemOption.orderBy('sys_created_on','desc');



itemOption.query();   



var i=0;



while(itemOption.next())



{



       gs.print(itemOption.sys_created_on + ' : ' + itemOption.value);



       i++



       if(i > 10)



               break;



}

View solution in original post

16 REPLIES 16

Go to solution
dalton1
Giga Expert

‎06-08-2015 02:54 AM

Updated code:



var itemOption = new GlideAggregate('sc_item_option');


itemOption.addAggregate('COUNT');


itemOption.addQuery('u_import_comments','STARTSWITH',importGUID);


itemOption.orderBy('u_import_comments');


itemOption.query();


var itemOptionCount = itemOption.getAggregate('COUNT');


0 Helpfuls

Go to solution
Daryll Conway
Giga Guru
In response to dalton1

‎06-08-2015 03:11 AM

Why do you need an orderBy() in a count?


Also try it in this order...



var itemOption = new GlideAggregate('sc_item_option');



itemOption.addQuery('u_import_comments','STARTSWITH',importGUID);



itemOption.addAggregate('COUNT');



itemOption.query();



if (itemOption.next())



var itemOptionCount = itemOption.getAggregate('COUNT');



Is importGUID a variable? If not it'll need quotes round it. It may need parsed to a string even if it is a variable.


0 Helpfuls

Go to solution
Daryll Conway
Giga Guru
In response to Daryll Conway

‎06-08-2015 03:14 AM

You may want to declare the itemOptionCount on a global level if you want to use the variable else where other wise it'll be out of scope


0 Helpfuls

Go to solution
dalton1
Giga Expert
In response to Daryll Conway

‎06-08-2015 03:20 AM

Hi Daryll Conway,



Here are my answers:



- I need to order it because I am taking items at a time based on association value I put in u_import_comments.


-   Yes importGUID is a variable.


- I have already tried that ordering of yours. I have addAggregate before the query() but to no avail.


- I have placed the itemOptionCount globally . I just edited the snippet just for the sake of the discussion thread,



Anymore points/alternative actions? I need the record count.


0 Helpfuls