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, "","");

[Shubha2]

Thank you for your reply. when I tried this with owner by group. But the part where I have to query for individual owner and listing their open cases still not working. Any suggestions would be helpful. Thank you.

[Sagar Pagar]

Hi,

Can you share your script here? it would be helpful for troubleshoot.

Thanks,

Sagar Pagar

[Shubha2]

Thanks again Sagar for your reply.  So far, with this script, email is being sent and it does lists all the open cases(this part works very well) but for the same owner, it is sending too many times. Requirement is to send one email to the owners of the open cases, listing all the open cases belonging to them. 

Problem with this script: Same owner (example: John) is getting 10 emails than getting one email with his open cases. Really appreciate your help. Thank you. Shubha.

This is the scheduled script:

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('case_owner');
gr.query();

while (gr.next()) {
    
    var caseOwner = null;
    //while (gr.next()) {
    if (caseOwner == gr.case_owner) // to ensure each owner only one time - This is Not working
    continue;
    caseOwner = gr.case_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, "","");

}

[Shubha2]

One more things, I forgot to add, I used the GROUPBY to test in my script, I could group the owners, I liked the output of it. But I am not sure how to utilize the GROUPBY for our requirement. orderBy seem to work here fine. Thanks again.