Email reminder when time sheet is not submitted

[Julia Allen1]

Hello -

I can't find my thread on this but here is what I am trying to do and getting stuck on step 4 and 5.

I need to have automated emails sent to users at 9:05 AM every Monday who have not submitted their time sheets for review. 

1. Create an Email Notification for the time_sheet table that is sent when an Event is fired (I did this)
2. Add Conditions to make it where State is either Pending/Rejected/Recalled (I did this)
3. In the Who will Receive section, add the User option to the "Users/Groups in fields" field (I did this)
4. Now create a Scheduled Job that runs Daily (I did this)
5. Have the Job create the custom event that we added to the Notification for each time_sheet record that is in one of those states for the current week (this is where I am stuck - I created an event, but I don't know how to tie it to the scheduled job)

Any additional steps to clarify #4 ad #5 would be very helpful. It also needs to keep sending emails to the users until they submit it (so I am thinking that is a second job set to run or a simple script)

[SanjivMeher]

You need to create an event registry record and in the notification select that event. 

Then you need to create a scripted scheduled job, where you need to call that event.

So query all the timesheet not submitted and trigger the event using

gs.eventQueue('event name', glide object);

[SanjivMeher]

Here is a very well described steps, which could help you achieve this

https://community.servicenow.com/community?id=community_question&sys_id=01f17314dbd7a7802e8c2183ca96...

[CHANDU2]

Hi Julia,

I had the same requirement and got stuck at 4th point can you help me with the code if you have implemented it??

[Julia Allen1]

@CHANDU - sure no problem happy to help. 

#4 - Now create a Scheduled Job that runs Daily (I did this)

This is located in the 'scheduled job' area 

• Click on 'New' and choose 'Automatically run a script of your choosing'

• Set the top part (schedule part) up how you wish, this is what mine looks like:

• For the script, you will want to think about the job. I have 2 jobs.. 1 will run BEFORE the time the time sheet is due (saying 'hey go submit the time sheet' and then I have a second email when the time sheet is past due. The setup for the script is the same - you just change the query filter. Here is the code I used for when the email is sent saying your time sheet is late. 

Everything in Blue is what you will change to be whatever it is for you.

In this instance, the blue is a query you can pull from the time sheet view > set the filter up how you want > put the query in to the code keeping the pieces in black (note there are ' ' around the query inside the parenthesis -- they are small and easy to miss)

You will then replace the event in blue with the name of YOUR event you created, in my case I just called it submit.timesheet.late

- CODE I USED: 

var gr = new GlideRecord('time_sheet');
gr.addEncodedQuery('state=Pending^ORstate=Rejected^ORstate=Recalled^week_starts_onONLast week@javascript:gs.beginningOfLastWeek()@javascript:gs.endOfLastWeek()^user!=1af704bedb136b002394c443059619da^ORuser!=5af704bedb136b002394c4430596196a^ORuser!=42f7c0bedb136b002394c44305961940^ORuser!=56f704bedb136b002394c4430596199c^ORuser!=22f744bedb136b002394c4430596193e^ORuser!=eef744bedb136b002394c443059619ad^ORuser!=c2f784b2db176b00f20916994b9619cc');

gr.query();

while (gr.next()) {
gs.eventQueue("submit.timesheet.late", gr);
}

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

How to pull the query to use from the system if you don't code:

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

Let me know if this helps, if it does - please mark it helpful! 🙂 

Also, check out my blog - I outline this process in more detail: https://howtoservicenow.com/automatic-email-notification/