Schedule job trigerring notification which contains a email script calculating wrong days

[Mohit Kaushik]

Hi @1_DipikaD ,

Try modifying your code in the mail script to use the OOB method as below:

var gdt1 = new GlideDateTime("2011-08-28 09:00:00"); // replace the gdt1 value with your first date
var gdt2 = new GlideDateTime("2011-08-31 08:00:00"); // replace the gdt2 value with your second date
 
var dur = GlideDateTime.subtract(gdt1, gdt2); // Difference between gdt1 and gdt2
gs.info(dur.getDisplayValue());

//Output:
// 2 Days 23 Hours

Let me know if you have any more issues.

[Ankur Bawiskar]

@1_DipikaD 

try this

scheduled job

var gr_user = new GlideRecord('u_capacity_reviews');
gr_user.addEncodedQuery('u_status=pending^active=true');
gr_user.query();
while (gr_user.next()) {
    var reviewdate = new GlideDateTime(gr_user.u_capacity_review_due_date.toString());
    var currentDate = new GlideDateTime();

    if (currentDate > reviewdate) {
        gr_user.u_status = 'overdue';
        gr_user.update();
        gs.eventQueue('overdue.capacity.reviewers', gr_user, gr_user.u_application_instance.u_support_owner, gr_user.u_application_instance.owned_by);
    }
}

Email Script:

(function runMailScript(current, template, email, email_action, event) {
    var gr = new GlideRecord('u_capacity_reviews');
    gr.addQuery('u_status', 'overdue');
    gr.addQuery('u_application_instance.u_support_owner', current.u_application_instance.u_support_owner);
    gr.query();

    var reviewdate = new GlideDateTime(current.getValue('u_capacity_review_due_date'));
    var reviewdateValue = reviewdate.getNumericValue();

    var gdt = new GlideDateTime();
    var gdt1 = gdt.getNumericValue();

    var dateDiff = gdt1 - reviewdateValue; // Still in milliseconds here
    var daysDiff = dateDiff / (24 * 60 * 60 * 1000); // Convert milliseconds to days
    var daysDiff1 = Math.floor(daysDiff);

    if (gr.hasNext()) {
        template.print('<table class ="grplist"><tr style="font-size: 9pt; background: rgb(193, 193, 193); border:1pt solid black; text-align: center;"><td style="font-size: 8.5pt; width: 10%;"><strong>Division</strong></td><td style="font-size: 8.5pt; width: 10%;"><strong>Subdivision</strong></td><td style="font-size: 8.5pt; width: 10%;"><strong>Number</strong></td><td style="font-size: 8.5pt; width: 10%;"><strong>Application Instance</strong></td><td style="font-size: 8.5pt; width: 10%;"><strong>Due Date</strong></td><td style="font-size: 8.5pt; width: 10%;"><strong>Minimum Review Cycle</strong></td><td style="font-size: 8.5pt; width: 10%;"><strong>Support Group</strong></td><td style="font-size: 8.5pt; width: 10%;"><strong>Days Overdue</strong></td></tr>');
    }
    while (gr.next()) {
        var capacity_number = '<a href="http://' + gs.getProperty("instance_name") + '.service-now.com/u_capacity_reviews.do?sys_id=' + gr.getValue('sys_id') + '">' + gr.u_number + '</a>';
        var appinstance = '<a href="http://' + gs.getProperty("instance_name") + '.service-now.com/u_cmdb_ci_app_instance.do?sys_id=' + current.u_application_instance.sys_id + '">' + gr.u_application_instance.name + '</a>';

        template.print('<tr><td style="font-size: 8.5pt; width: 10%;">' + gr.u_application_instance.u_division.name + '</td><td style="font-size: 8.5pt; width: 10%; " >' + gr.u_application_instance.u_subdivision.name + '</td><td style="font-size: 8.5pt; width: 10%; " >' + capacity_number + '</td><td style="font-size: 8.5pt; width: 10%;">' + appinstance + '</td><td style="font-size: 8.5pt; width: 10%;">' + gr.u_capacity_review_due_date + '</td><td style="font-size: 8.5pt; width: 10%;">' + gr.u_review_cycle + '</td><td style="font-size: 8.5pt; width: 10%;">' + gr.u_application_instance.u_support_group.name + '</td><td style="font-size: 8.5pt; width: 10%;">' + daysDiff1 + '</td></tr>');
    }
    template.print('</table>');
    template.print('<style>.grplist td{border-spacing: 0pt; padding: 0mm 0mm 0mm .5mm; border:1pt solid black;}</style>');
    template.print('<style>.grplist{border-spacing: 0px; border-collapse: collapse; border:1pt solid black;}</style>');

})(current, template, email, email_action, event);

If my response helped please mark it correct and close the thread so that it benefits future readers.

[1_DipikaD]

Still getting the same no. -27. 

[Ankur Bawiskar]

@1_DipikaD 

try this email script

(function runMailScript(current, template, email, email_action, event) {
    var gr = new GlideRecord('u_capacity_reviews');
    gr.addQuery('u_status', 'overdue');
    gr.addQuery('u_application_instance.u_support_owner', current.u_application_instance.u_support_owner);
    gr.query();

    if (gr.hasNext()) {
        template.print('<table class="grplist"><tr style="font-size: 9pt; background: rgb(193, 193, 193); border:1pt solid black; text-align: center;"><td style="font-size: 8.5pt; width: 10%;"><strong>Division</strong></td><td style="font-size: 8.5pt; width: 10%;"><strong>Subdivision</strong></td><td style="font-size: 8.5pt; width: 10%;"><strong>Number</strong></td><td style="font-size: 8.5pt; width: 10%;"><strong>Application Instance</strong></td><td style="font-size: 8.5pt; width: 10%;"><strong>Due Date</strong></td><td style="font-size: 8.5pt; width: 10%;"><strong>Minimum Review Cycle</strong></td><td style="font-size: 8.5pt; width: 10%;"><strong>Support Group</strong></td><td style="font-size: 8.5pt; width: 10%;"><strong>Days Overdue</strong></td></tr>');
        
        while (gr.next()) {
            // Calculate days overdue FOR EACH RECORD
            var dueDate = new GlideDateTime(gr.getValue('u_capacity_review_due_date'));
            var today = new GlideDateTime();
            var daysOverdue = GlideDateTime.subtract(today, dueDate).getDayPart(); 

            // Generate table row with record-specific data
            var capacity_number = '<a href="http://' + gs.getProperty("instance_name") + '.service-now.com/u_capacity_reviews.do?sys_id=' + gr.getValue('sys_id') + '">' + gr.u_number + '</a>';
            var appinstance = '<a href="http://' + gs.getProperty("instance_name") + '.service-now.com/u_cmdb_ci_app_instance.do?sys_id=' + gr.u_application_instance.sys_id + '">' + gr.u_application_instance.name + '</a>';

            template.print('<tr><td style="font-size: 8.5pt; width: 10%;">' + gr.u_application_instance.u_division.name + '</td><td style="font-size: 8.5pt; width: 10%;">' + gr.u_application_instance.u_subdivision.name + '</td><td style="font-size: 8.5pt; width: 10%;">' + capacity_number + '</td><td style="font-size: 8.5pt; width: 10%;">' + appinstance + '</td><td style="font-size: 8.5pt; width: 10%;">' + gr.u_capacity_review_due_date + '</td><td style="font-size: 8.5pt; width: 10%;">' + gr.u_review_cycle + '</td><td style="font-size: 8.5pt; width: 10%;">' + gr.u_application_instance.u_support_group.name + '</td><td style="font-size: 8.5pt; width: 10%;">' + daysOverdue + '</td></tr>');
        }
        
        template.print('</table>');
        template.print('<style>.grplist td{border-spacing: 0pt; padding: 0mm 0mm 0mm .5mm; border:1pt solid black;}</style>');
        template.print('<style>.grplist{border-spacing: 0px; border-collapse: collapse; border:1pt solid black;}</style>');
    }
})(current, template, email, email_action, event);

If my response helped please mark it correct and close the thread so that it benefits future readers.