How to get records created on the week and current weeks

[DIVI_IT]

Hi, 
How to get records created on the last week and current week, and after that compare then to get the assigned_to difference value from these records? 

table: Incident 

[SN_Learn · Accepted solution]

Hi @DIVI_IT ,

Please have a look at the sample code below:

var lastweek = new GlideRecord('incident');
lastweek.addEncodedQuery("active=true^opened_atONLast week@javascript:gs.beginningOfLastWeek()@javascript:gs.endOfLastWeek()");
lastweek.query();
while(lastweek.next()){
	gs.info('Incident No. '+ lastweek.number + ','+ 'Assigned To : '+ lastweek.assigned_to.name);
}
var lastWeekCount = lastweek.getRowCount();
gs.info("Last week's Total Count: "+ lastWeekCount);


var currentWeek = new GlideRecord('incident');
currentWeek.addEncodedQuery("active=true^opened_atONThis week@javascript:gs.beginningOfThisWeek()@javascript:gs.endOfThisWeek()");
currentWeek.query();
while(currentWeek.next()){
	gs.info('Incident No. '+ currentWeek.number + ','+ 'Assigned To : '+ currentWeek.assigned_to.name);
}
var currentWeekCount = currentWeek.getRowCount();
gs.info("This week's Total Count: "+ currentWeekCount);

var getDiff = gs.info('Difference: '+ (parseInt(currentWeekCount) - parseInt(lastWeekCount)));

Output:

*** Script: Incident No. INC0009001,Assigned To : Bud Richman
*** Script: Incident No. INC0009005,Assigned To : Antony Alldis
*** Script: Last week's Total Count: 2

*** Script: Incident No. INC0007001,Assigned To : Arya Hajarha
*** Script: Incident No. INC0008001,Assigned To : Luke Wilson
*** Script: Incident No. INC0008111,Assigned To : Aqib Mushtaq
*** Script: This week's Total Count: 3

*** Script: Difference: 1

Please mark this as correct answer and helpful if it resolved, or mark this helpful if this help you to reach towards solution.

[AakashG2703 · Accepted solution]

Hi @DIVI_IT,

Hope you are doing well.

Proposed Solution

In order to achieve this task, I personally tried it on my Personal Developer Instance and used "Incident" Table so that you can get the solution as soon as possible. For this, you just need to create a "Background Script" and write script as mentioned below to get the difference and unique records from the last week and this week data.

Background Script:-

thisWeekInc = [];
lastWeekInc = [];
var thisWeekCount = 0;
var lastWeekCount = 0;
var grInc = new GlideRecord('incident');
grInc.addEncodedQuery('sys_created_onONThis week@javascript:gs.beginningOfThisWeek()@javascript:gs.endOfThisWeek()^assigned_toISNOTEMPTY');
grInc.query();
while(grInc.next()){
    thisWeekCount += 1;
	thisWeekInc.push(grInc.assigned_to.getDisplayValue());
    gs.print('Incident: ' + grInc.number + ' and ' + 'Assigned To: ' + grInc.assigned_to.getDisplayValue());
}
gs.print('This Week Inc Count: '+ thisWeekInc.length);
var grInc2 = new GlideRecord('incident');
grInc2.addEncodedQuery('sys_created_onONLast week@javascript:gs.beginningOfLastWeek()@javascript:gs.endOfLastWeek()^assigned_toISNOTEMPTY');
grInc2.query();
while(grInc2.next()){
    lastWeekCount += 1;
	lastWeekInc.push(grInc2.assigned_to.getDisplayValue());
    gs.print('Incident: ' + grInc2.number + ' and ' + 'Assigned To: ' + grInc2.assigned_to.getDisplayValue());
}
gs.print('Last Week Inc Count: '+ lastWeekInc.length);
var finalArr = [];
for (var i=0; i<thisWeekInc.length; i++){
    if (finalArr.indexOf(thisWeekInc[i])===-1){
        finalArr.push(thisWeekInc[i]);
    }
}
for (var j=0; j<lastWeekInc.length; j++){
    if (finalArr.indexOf(lastWeekInc[j]) === -1){
        finalArr.push(lastWeekInc[j]);
    }
}
gs.print('Unique/Difference: ' + finalArr.length);
gs.print('Final List of Assigned To Users without Duplicates: \n' + finalArr);

For your reference, also attaching screenshots of the outputs that will give you better insights of how the scripts are working or the best thing will be to follow the solution and execute the scripts on your instance.

If you find this information/knowledge/solution helpful, Please don't forget to mark my solution and reply as helpful and accepted.

Thanks 🙂

Aakash Garg

ServiceNow Developer

[AakashG2703]

Hi @DIVI_IT,

Hope you are doing well and Glad to see my solution as helpful and accepted.

Proposed Solution

From my previous code, we are storing the list of users after finding differences and removing the duplicated users into an array named as "finalArr". Now, in order to create an event or notification, there are some point you need to follow: -

1. "finalArr" is the list of a unique users/assigned_to. 
2. Create an "Event Registry" to trigger the Notification.
3. Loop an array through the length of an array and make an "eventQueue" to call Event Registry in the same script. Sample code as mentioned below.
4. Create a Notification that will trigger based on the Event Registry and all the users within "finalArr" will receive the notification.

Sample Script: -

for (var i=0; i<finalArr.length; i++){
gs.eventQueue(<Event Registry Name>, gr, finalArr[i]); // Replace <Event Registry Name> with the actual name of your event registry, gr is the GlideRecord object, and finalArr[i] will fetch the user one by one and trigger the notificaiton.
}

If you find this information/knowledge/solution helpful, please don't forget to mark my solution and reply as helpful and accepted.

Thanks ‌‌:)

Aakash Garg

ServiceNow Developer

[DIVI_IT]

yeah but it's sending the notification to users which are duplicated for both conditions, I want to sent notification only for users which are not duplicated, what do you think about it ?

I mean if there we have a users in the last week condition and they are not in the current week condition then I want to sent the notification.

[DIVI_IT]

That's how it look like right now, I have a custom table, and it's working properly but unfortunately the notification is sending to users which are included with both conditions, and I want to send a notification to users which are included in last week and are not in current yet 

thisWeek = [];
lastWeek = [];
var thisWeekCount = 0;
var lastWeekCount = 0;
var grWeek = new GlideRecord('u_calendar_events');
grWeek.addEncodedQuery('sys_created_onONThis week@javascript:gs.beginningOfThisWeek()@javascript:gs.endOfThisWeek()');
grWeek.query();
while(grWeek.next()){
thisWeekCount += 1;
thisWeek.push(grWeek.u_user);
gs.print('Current week Assigned To: ' + grWeek.u_user.getDisplayValue());
}
gs.print('This Week Inc Count: '+ thisWeek.length);
var grWeek2 = new GlideRecord('u_calendar_events');
grWeek2.addEncodedQuery('sys_created_onONLast week@javascript:gs.beginningOfLastWeek()@javascript:gs.endOfLastWeek()');
grWeek2.query();
while(grWeek2.next()){
lastWeekCount += 1;
lastWeek.push(grWeek2.u_user);
gs.print('Last week Assigned To: ' + grWeek2.u_user.getDisplayValue());
}
gs.print('Last Week Inc Count: '+ lastWeek.length);
var finalArr = [];
for (var i=0; i<thisWeek.length; i++){
if (finalArr.indexOf(thisWeek[i]) === -1){
finalArr.push(thisWeek[i]);
}
}
for (var j=0; j<lastWeek.length; j++){
if (finalArr.indexOf(lastWeek[j]) === -1){
finalArr.push(lastWeek[j]);
}
}
for (var i=0; i<finalArr.length; i++){
gs.eventQueue('calendarEvents.reminder', grWeek, finalArr[i]);
}
gs.print('Unique/Difference: ' + finalArr.length);
gs.print('Final List of Assigned To Users without Duplicates: \n' + finalArr);

[AakashG2703]

Hi @DIVI_IT,

Hope you are doing well.

Proposed Solution

I got your requirement and tried to implement it on my Browser's console by taking an example of 2 arrays like we are having in our code "lastweek" and "currentWeek". Attaching screenshot for the reference and kindly refer below script. Hope this will work for you now. 

• Here, "arr1" is referring to "lastWeek" and "arr2" is referring to "currentWeek" and need to declare a new array or can use an existing array as "finalArr". If using existing array "finalArr", clear it first.
• As an output, you can have a look at above screenshot and consider "1" as a user which is present in "lastWeek" array but not in "currentWeek" array.

Sample Script: -

for (var i=0; i<<New Array Name>.length; i++){ // Replace <New Array> name with an array
gs.eventQueue(<Event Registry Name>, gr, finalArr[i]); // Replace <Event Registry Name> with the actual name of your event registry, gr is the GlideRecord object, and finalArr[i] will fetch the user one by one and trigger the notificaiton.
}

If you find this information/knowledge/solution helpful, please don't forget to mark my solution and reply as helpful and accepted.

Thanks ‌‌:)

Aakash Garg

ServiceNow Developer

[DIVI_IT]

Unfortunately it's not working like on your screen, 
somehow this script is losing data about the full record users.

for example: 

currentweek : I have a 4 records different 4 users

last week: I have a 2 records, both are too in the current week 

and when I want to run: 

gs.print('Final List of Assigned To Users without Duplicates: \n' + finalArr);

from your script I've got those 2 users which are in the currentweek and lastweek too.

and here is the problem, I want to send notification to users which are not included in one of those condition currentweek or lastweek.