How do I add a query for GROUPBY owner in the scheduled script

[Shubha2]

I need your help please!! we have setup an email notification to be sent for open cases. I need to send it to Case Owner and send them an list of Open cases. To achieve this, I have setup an scheduled job weekly and Event registry and Notification and email script The list of open cases should be sent to the "Owner's" and listing them all their cases. There are around 28 (example) owners who has open cases, SO, 28 separate emails (in One notification) should be sent with their respective open cases. Schedule job script is not working I guess, I could not figure this out. Would definitely appreciate your help. Thank you, Shubha

var gdt = new GlideDateTime();
var gdCreatedOn;
var gdurOpenDays;
var intOpenDays = 0;
var gdtDateCreated = GlideDateTime('sys_created_on');        //Created field value (GlideDateTime).
var gdtNow = new GlideDateTime();

var gr = new GlideRecord("x_case");
gr.addEncodedQuery("stateNOT IN3,4");
gr.orderBy('owner');
gr.query();

var Owner = null;

while (gr.next()) {
    if (Owner == gr.owner) // to ensure each owner only one time
        continue;
    Owner = gr.owner;
    
gdurOpenDays = GlideDateTime.subtract(gdtDateCreated, gdtNow);
intOpenDays = gdurOpenDays.getRoundedDayPart();

if (gdurOpenDays == 90) { // 90 days
}
}
gs.info("Case Numbers:"+gr.number);
gs.eventQueue("x_open.cases", gr, "","");

[Valmik Patil1]

Hi , 

GROUPBY can be done using GlideAggregate 

Please refer below example

var count = new GlideAggregate('incident');
count.addAggregate('MIN', 'sys_mod_count');
count.addAggregate('MAX', 'sys_mod_count');
count.addAggregate('AVG', 'sys_mod_count');
count.groupBy('category');
count.query();   
while (count.next()) {  
  var min = count.getAggregate('MIN', 'sys_mod_count');
  var max = count.getAggregate('MAX', 'sys_mod_count');
  var avg = count.getAggregate('AVG', 'sys_mod_count');
  var category = count.category.getDisplayValue();
  gs.log(category + " Update counts: MIN = " + min + " MAX = " + max + " AVG = " + avg);
}

Thanks,

Valmik

[Shubha2]

Thank you so much. Very kind of you. Still trying to figure out the querying for the individual owner. thank you. 

[RynoY]

Thank you, this was the solution to my problem!

Here is an example of how I did it:

var platformGR = new GlideAggregate('my_table');
platformGR.addNotNullQuery('platform');
platformGR.groupBy('platform');
platformGR.query();  

while (platformGR.next()) {
	gs.info("platform: " + platformGR.platform);
}

[Sagar Pagar]

Hi,

You have to use the GlideAggregate for group by owner field as suggested by Valmik.

In GlideRecord group is not available, even if you applied in encoddedQuery it will not apply. It will show all count.

Thanks,

Sagar Pagar