Aggregate count not working

[Dead Blade]

Hello All, the aggregate count is not working as expected.  Please help.  The dot-walk addQuery should return a count of 100 for example, but I am getting 0 results.

		
var count = new GlideAggregate('alm_hardware');   
count.addQuery('model.u_field','1111111');
count.addAggregate('COUNT');  
count.query();

gs.log("This count is empty: "+count.getAggregate('COUNT'));
if(count.next()){

gs.log("This count is working: "  +count.getAggregate('COUNT'));
gs.log('count');
}

Results:

*** Script: This count is empty: null
*** Script: This count is working: 0
*** Script: count
*** Script: 
----------------------------------------------------------

The Second row should be a count of 100

[Willem]

Not sure what the result is you want? There are no models used that have the u_field set to 1111111 in the alm_hardware table as you confirmed.

So whatever code you use, will always return 0. Because that is the number of models that have u_field set to 1111111 in the alm_hardware table.

If you want to retrieve a count of all models that have u_field set to 1111111 , then you have to query/glide aggregate cmdb_model, like you did.

There is no way to count things in a table, that are not used in that table. It will always return 0.

[Dead Blade]

Hi Willem,

The field is dot.walked on the alm_hardware form.  But I cannot add it to the list view.

So, shouldn't I be able to get a count of how many alm_hardware records = model.u_field "111111"

[Willem]

is u_field a reference field? If so, you should use the sys_id of the reference value like so:

(replace the sys_id with the sys_id value)

		
var count = new GlideAggregate('alm_hardware');   
count.addQuery('model.u_field','the sys_id');
count.addAggregate('COUNT');  
count.query();

gs.log("This count is empty: "+count.getAggregate('COUNT'));
if(count.next()){

gs.log("This count is working: "  +count.getAggregate('COUNT'));
gs.log('count');
}

Or use the field that contains the name/display value:

		
var count = new GlideAggregate('alm_hardware');   
count.addQuery('model.u_field.name','1111111');
count.addAggregate('COUNT');  
count.query();

gs.log("This count is empty: "+count.getAggregate('COUNT'));
if(count.next()){

gs.log("This count is working: "  +count.getAggregate('COUNT'));
gs.log('count');
}

If it is not a reference, it should find the value if the u_field on the model is exactly matching the 1111111.  Are you sure it is an exact match?

[Dead Blade]

It is an exact match.  u_field is not a reference field, it is a string field on the cmdb_model table.  Of course "model" is a reference field on the alm_hardware table.

I just attempted to use the original rowCount code and it is not returning a value either.  So maybe something is wrong with the table reference.

I just tried to addQuery to just the model and 0 results.

var gr3 = new GlideAggregate('alm_hardware');  
gr3.addAggregate('COUNT'); 
gr3.addQuery('model','Precision 3541');
gr3.query();

gs.log("This count is empty: "+gr3.getAggregate('COUNT'));
while(gr3.next()){
gs.log("This count is working: "+gr3.getAggregate('COUNT'));
gs.log('gr3');
}

---

*** Script: This count is empty: null
*** Script: This count is working: 0
*** Script: gr3


 = (MORE THAN 0)

The script should have returned more than 0.

[Willem]

Can you share screenshot of the result when you filter the table?

So something like this:

(replace barcode with your field and 5555 with your value)

Do you get any results?