How to get sys_id of a record using GlideAggregate

[Kalyan Gadde]

Hi,

My requirement is I need to count number of incidents with some assignment group and if there is only one incident with that perticular assignment group then I require that incident's sys_id . I don't want to use getRowCount(). I want to use only GlideAggregate. I have used the below script in my PDI but I did not get what I want.

Can Anybody help me on this.

var count = new GlideAggregate('incident');
count.addQuery('assignment_group','36c741fa731313005754660c4cf6a70d');  // Only one incident is there with this group 
count.addAggregate('COUNT');
count.query();
var incidents = 0;
if (count.next())
incidents = count.getAggregate('COUNT');
gs.info(incidents);       // 1
gs.info(count.getUniqueValue());    // null
gs.info(count.getValue('sys_id'));   // no result
gs.info(count.sys_id.toString());   // no result

[Deepshikha 3010]

Hii @Kalyan Gadde,

GlideAggregate enables you to easily create database aggregation queries.

The scoped GlideAggregate class is an extension of GlideRecord and provides database aggregation (COUNT, SUM, MIN, MAX, AVG) queries. This functionality can be helpful when creating customized reports or in calculations for calculated fields. The GlideAggregate class works only on number fields.

Note:Use GlideRecord for this

Regards

Deepshikha Pandey

[Alok Das]

Hi Kalyan,

As already said by others, it's not possible to get sys id using GlideAggregate. However you can use the below script to achieve the requirement.

var count = new GlideAggregate('incident');
count.addQuery('assignment_group', '36c741fa731313005754660c4cf6a70d'); // Only one incident is there with this group 
count.addAggregate('COUNT');
count.query();
var incidents = 0;
if (count.next())
incidents = count.getAggregate('COUNT');
gs.info(incidents); // 1
if (incidents == 1) //validate there is only one record
{
    var inc = new GlideRecord('incident');
    inc.addQuery('assignment_group', '36c741fa731313005754660c4cf6a70d');
    inc.query();
    if (inc.next()) {
        gs.info("SYS ID of record : " + inc.getUniqueValue());
    }
}

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

Regards,

Alok

[Alok Das · Accepted solution]

Well doing some more play around I was able to get the sys_id. Please find the code below:

var count = new GlideAggregate('incident');
count.addQuery('assignment_group', '36c741fa731313005754660c4cf6a70d'); // Only one incident is there with this group 
count.addAggregate('COUNT', 'sys_id');
count.query();
var incidents = 0;
if (count.next())
    incidents = count.getAggregate('COUNT', 'sys_id');
gs.info(incidents); // 1
if(incidents==1) //validate if there is only one incident
gs.info('Sys_ID : ' + count.sys_id.toString());

[Daniel Oderbolz]

Even if this thread is already a bit older, I would like to contribute some thoughts.

First of all, the GlideAggregate is not the same as GlideRecord, therefore, some features we know from GildeRecord are not available. 

The one that is relevant here is that in a GlideAggregate, the individual rows to not have a sys_id, because they are the result of an aggegration (in other words, these records are generated as the aggregate is calculated, they do not exist in a database table).

However, if you group by a column that carries a sys_id, you get the value of this record and it will contain the sys_id of that record - that is what the solution of Alok does.

What I do not like about the solution is that it does not loop (tomorrow there might be more incidents in the group) - the technique still works.

[Mayu]

We can write this Script on background Script we can get sys_id

var gr = new GlideAggregate('incident');
gr.addQuery('assignment_group' ,'36c741fa731313005754660c4cf6a70d');
gr.query();
if(gr.next()){
gs.print(gr.sys_id);
}

please mark helpful or correct