Notification to user manager with list of users who didnt submitted time sheet for the last week

[e__rajesh_badam]

Hi,

The requirement is need to send an notification to the Respective manager with the respective list of Reportees who didn't submitted time sheet last week.

This information need to send every week Monday morning.

For Example

manager A has 10 reportees. 5 Reportees not submitted time sheet. Those 5 people names need to send an email.

manager B has 15 reportees. 10 Reportees not submitted time sheet. Those 10 people names need to send an email.

Is this possible? If yes please help me to achieve it.

[Rafael Batistot]

Hi @e__rajesh_badam 

Yes, is possible via schedule job (run each Monday for exemple)  + notification (body content for managers) + mail script (to identify who not submitted the time sheet)

[e__rajesh_badam]

Hi @Rafael Batistot ,

1. Created event in event registry (on Time_sheet table).

2. Created a schedule JOB and mentioned monday 9AM BST and in the Script added below code

-----------------------------------------------------------------------

var gr = new GlideRecord('time_sheet');

gr.addEncodedQuery('stateINPending,Rejected,Recalled^week_starts_onONLast week@javascript:gs.beginningOfLastWeek()@javascript:gs.endOfLastWeek()^user.active=true')

gr.query();

while (gr.next()) {

gs.eventQueue("Timesheet.pending.notification", gr);

}

-------------------------------------------------------------------------------------------------------------

3. Created Notification (on Time_sheet table) and firing it based on event trigger basis. But it is triggering individual emails.

Could you please help me with the Mail Script to pull list of user who didnt submitted- how to achieve it , please.

[Rafael Batistot]

@e__rajesh_badam 

Try

// Mail script: "reportees_not_submitted"

var manager = current.manager;

var reporteeIds = (event.parm1 || '').split(',');

if (reporteeIds.length > 0 && reporteeIds[0] !== '') {
    gs.include("GlideRecord");
    var gr = new GlideRecord("sys_user");
    gr.addQuery("sys_id", "IN", reporteeIds.join(','));
    gr.query();

    template.print("<p>Dear " + manager.name + ",</p>");
    template.print("<p>The following reportees did not submit their timesheet last week:</p>");
    template.print("<ul>");
    while (gr.next()) {
        template.print("<li>" + gr.getDisplayValue("name") + "</li>");
    }
    template.print("</ul>");
    template.print("<p>Please follow up with them accordingly.</p>");
} else {
    template.print("<p>All reportees have submitted their timesheet.</p>");
}