Aggregate count not working

Dead Blade · ‎09-30-2020

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 · ‎10-06-2020

To help you, you can run this code:

var usedModels = 0;
var grModel = new GlideRecord('cmdb_model');
grModel.addQuery('u_field', '1111111');
grModel.query();
while (grModel.next()) {
    var grHardware = new GlideRecord('alm_hardware');
    grHardware.addQuery('model', grModel.getUniqueValue());
    grHardware.query();
    if (grHardware.next()) {
        gs.log("The model: " + grModel.getDisplayValue() +  " is used in Hardware");
        usedModels++;
    }

}
gs.log("There are: " + grModel.getRowCount() + ' Models available with 111111');
gs.log("There are: " + usedModels + ' Models used for Hardware with 111111');

Please share the log results.

View solution in original post

Dead Blade · ‎10-06-2020

Ok, you have been super helpful. I tried another value from the u_field that has positive results in the list view of alm_hardware. So my query is working. But, going through this practice helped to clear up a few things. I will mark your answer as correct answer. Thank you so much Willem.

Willem · ‎10-06-2020

You are welcome! Please mark answers as helpful as well. That will help me a lot! 🙂

Alok Das · ‎09-30-2020

Hi,

Could you please give a try with below script:

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');
}

If above script also doesn't work then I would request you to validate whether field 'u_field' do exists on the cmdb_model table or on some other table.

Also, going ahead I tested your logic in my PDI with the oob field name and result was as expected, so your logic is correct. Only thing I suspect is the u_field might exists on the some other table and you are not getting to the correct field to dot walk.

Kindly mark my answer as Correct and helpful based on the Impact.

Regards,

Alok

Dead Blade · ‎10-01-2020

The u_field does exist on the cmdb_model table as a string field.

Results:

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

DirkRedeker · ‎10-01-2020

Hi

As you are querying your "alm_hardware" (Hardware Asset) table, you need to know that the Reference from the "model" field is to the table "Product Model" (see screenshot below - of the table column "model" in the "alm_hardware" table).

The Product Model table is called "cmdb_model". Maybe you added the "u_field" field NOT to that base table "cmdb_model" (see screenshot below):

If you added the "u_field" field to one of the child tables of the "Product Model" (e.g. to the "Hardware Model" table), you cannot dot-walk that way, because the fields of the child tables will not be available in that dot-walk.

Below, see the "Hardware Model" table, which is a Child of the Product Model Table:

Please review your table / field structure and let me know on whcih table exactly you defined the "u_field" column.

If that answers your question, feel free to mark my answer as correct and helpful.

Enjou &

BR

Dirk