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

[Rajesh Chopade1]

hi @1_DipikaD 
You can modify your email script as bellow and try once:

(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();

    // Initialize dates
    var reviewdate = new GlideDateTime(current.getValue('u_capacity_review_due_date'));
    var currentDate = new GlideDateTime();

    // Calculate days difference (currentDate - reviewdate)
    var daysDiff = gs.dateDiff(currentDate.getDisplayValue(), reviewdate.getDisplayValue(), true);
    var daysDiff1 = Math.floor(parseInt(daysDiff) / (24 * 60 * 60)); // Convert seconds to days

    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()) {
        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>';
        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);

I hope my answer helps you to resolve your issue, if yes please mark my answer helpful and correct.

thank you

Rajesh

[1_DipikaD]

No, It's not working as expected .

[Vishal Jaswal]

Hello @1_DipikaD 

Move the overdue days calculation inside the loop and calculate it for each record (gr) instead of once for current.

var reviewdate = new GlideDateTime(gr.getValue('u_capacity_review_due_date'));
var now = new GlideDateTime();
var dateDiff = now.getNumericValue() - reviewdate.getNumericValue(); // Milliseconds
var daysDiff = Math.floor(dateDiff / (1000 * 60 * 60 * 24));

NOT: Best practice recommended is to use camel notation variable names instead of using gr.

[kedarnathghadiy]

Hello @1_DipikaD 

could you add logs to print values of gdt1 & reviewdate values? this will help to understand the issue. Also could you confirm if the date format in the instance?

[kedarnathghadiy]

Hello @1_DipikaD 

There is one fundamental difference observed with two GlideRecords in Schedule scirpt & emails.


schedule script is firing events for only overdue reviews where as there is no such check at Email script.

I would suggest to add the check to addQuery as it will improve performance greatly and will reduce while loop iterations.

[code] gr_user.addQuery('u_capacity_review_due_dateRELATIVELT@minute@ago@1); [/code]

add this to both GlideRecords will improve the performance & the extra check in Schedule script can be removed.