Scheduled email notification

[dharani2607]

Hello,

I have a requirement for email notification,

If task is in WIP state for more than 5 working days, Manager to be emailed & report to be available.

Ticket Numbers should be shared to manager in excel sheet.

How can I achieve this?

[Abhit]

@dharani2607 

1. Create a custom field to log the date when the ticket was moved to WIP and hide that field from the form.

2. Write a conditional scheduled report to check if any of the tickets were moved to WIP 5 days ago.

Script:  

answer = false;
var grTable = new GlideRecord('table');
grTable.addEncodedQuery("custome_date_fieldRELATIVELT@dayofweek@ago@5");
grTable.query();
if (grTable.next())
    answer = true;

This you will share all the reports with whichever user you want.

If your requirement is to send the ticket reports to individual users then you need to move the same code from the scheduled job and trigger the event from there and which should fulfill your ask.

~ Abhit

[dharani2607]

will this exclude the weekends?grTable.addEncodedQuery("custome_date_fieldRELATIVELT@dayofweek@ago@5");

[Abhit]

No, do you want to exclude weekends?

[Taha Habib · Accepted solution]

Hello Dharani, I hope you are doing well,

You can use Scheduled Script Execution to achieve the following requirement. 

1. The Scheduled script Execution can be run on daily basis, and in the condition part you can check if any record is in WIP for 5 working days or not.

2. After checking the condition, you can run server script. 

3. In Server script find the records push the sysid or number of the record to an array

4. Call to an event, pass the array.

5. From event trigger the notification.

I am putting in an example for the Scheduled Script Execution, in here I am using Incident table and instead of WIP, I am using new state. I am also using sys_history_line to get the date on which state changed to new for a record.

Also I am putting in the logic for getting working days count in the script.

Scheduled Script Execution :

Condition:

var answer = false;
var gr = new GlideRecord("incident");
gr.addQuery("state", "1");
gr.query();
while (gr.next()) {
    //gs.info(gr.sys_id);
    var history = new GlideRecord("sys_history_line");
    history.addQuery('set.id', gr.sys_id);
    history.query();
    while (history.next()) {
        if (history.field == "state" && history.new_value == "1") {
            //gs.info("hi"+history.set.id+"  "+history.sys_created_on);


            var olddate = new GlideDateTime(history.sys_created_on);
            var curdate = new GlideDateTime();
            var count = 0;
            var value = (olddate.getDate().compareTo(curdate.getDate()));

            if (value == -1) {
                while ((olddate.getDate().compareTo(curdate.getDate())) != 0) {

                    olddate.addDaysLocalTime(1);
                    if ((olddate.getDayOfWeekUTC() == 6) || (olddate.getDayOfWeekUTC() == 7)) {
                        count--;
                        //gs.info(olddate.getDayOfWeekLocalTime()+" "+ olddate.getDate());
                    }
                    count++;
                }
            }


          //  gs.info(count);
            if (count >= 5) {
                answer = true;
                break;
            }

        }
    }
}
answer==true;

Run this script:

var array=[];
var gr= new GlideRecord("incident");
gr.addQuery("state", "1");
gr.query();
while(gr.next())
{
   //gs.info(gr.sys_id);
   var history= new GlideRecord("sys_history_line");
   history.addQuery('set.id', gr.sys_id);
   history.query();
   while(history.next())
   {
    if(history.field=="state" && history.new_value=="1")
      {
          //gs.info("hi"+history.set.id+"  "+history.sys_created_on);


         var olddate= new GlideDateTime(history.sys_created_on); 
var curdate= new GlideDateTime(); 
var count=0; 
var value= (olddate.getDate().compareTo(curdate.getDate()));

 

if(value==-1)
{
  while( (olddate.getDate().compareTo(curdate.getDate()))!=0 )
   {

 

         olddate.addDaysLocalTime(1);
         if((olddate.getDayOfWeekUTC()==6) || (olddate.getDayOfWeekUTC()==7))
         {       
         count--;
         //gs.info(olddate.getDayOfWeekLocalTime()+" "+ olddate.getDate());
         }
       count++;
   }
}

 


gs.info(count);
if(count>=5)         
{
    array.push(history.set.id.toString());

}

 

      }
   }

 

}
gs.info(array);

You can extend server side script for step 4 & 5 mentioned above.

This is the script for working days part:

Explanation:

1. I am taking two dates

2. Comparing it with "compareTo" that gives 0 when two dates are equal and -1 when first date is before 2nd date.

3. I am using loop to move from past date to new date, day by day.

4. If in the loop I found the day to be sat or sun, I am skipping it from count.

var olddate= new GlideDateTime(history.sys_created_on); 
var curdate= new GlideDateTime(); 
var count=0; 
var value= (olddate.getDate().compareTo(curdate.getDate()));

 

if(value==-1)
{
  while( (olddate.getDate().compareTo(curdate.getDate()))!=0 )
   {

 

         olddate.addDaysLocalTime(1);
         if((olddate.getDayOfWeekUTC()==6) || (olddate.getDayOfWeekUTC()==7))
         {       
         count--;
         //gs.info(olddate.getDayOfWeekLocalTime()+" "+ olddate.getDate());
         }
       count++;
   }
}

Ss of scheduled script Execution:

I hope this information helps you, if you find this information helpful. Please mark this answer as helpful and correct. Thank you.