Hello,

INTRODUCTION

I would like to share with the community a way to use custom JavaScript when building query conditions for Date/Time fields. This is something that I have personally created 3 unanswered questions for and done lots of searching for over the last few months, so I am quite excited to share a solution for others to use. (Thank you to all who helped out along that adventure! Much appreciated.)

You might be asking "Why would I even care about this? I have the relative filter." And that's fair, I agree that relative is very useful. But there are times when it doesn't cut it.

For example, suppose you are building a time series column chart trending by opened per week. Your stakeholder wants a report to show the totals of the last 3 weeks grouped by whole weeks, Mon-Sun. So, suppose you add a "Opened > relative > on or after 21 days ago" condition. Now, suppose that the stakeholder using this report needs to be able to load it from a dashboard any given time of the week. Monday, Tuesday, Friday, you name it. We will run into a few problems, as shown in the image below.

1) Since the relative operator returns a rolling time period, it will most likely show 4 bars when ran, as shown above. Okay, so add a "Opened > not on > This week" condition. All done? No, because you'll still have the second problem. 2) The first bar - and the 4th bar if you don't add the condition I just mentioned - will be an impartial representation of its corresponding week. It will not be Mon 00:00:00 - Sun 23:59:59. This means that oftentimes it will result in false narratives, showing a significant downward trend on the tail ends of the report that do not actually exist in the time range the customer actually wants to examine, given that the customer wants the freedom of being able to load this report anytime he wants. (We're not even giving the customer what he actually wants, oops!)

Shown below is what that report is actually supposed to look like according to the stakeholder's requested time condition. However, this required the use of a hard-coded date/time condition. (I don't know about you, but those make me cringe...)

So, I don't know about you, but I don't see any difference. Nope, nada, both reports above are giving the exact same narrative, so why bother?! Hahaha, just kidding! There's a huge difference! This second one doesn't show that superficial significant downward trend on the tail ends, does it?

By the way, we apparently can't edit or add any extra choices to the condition builder's list of date/time filters. Apparently it's locked down.

Man... Um... Then how do we do this without using hard-coded date/timeconditions? What do we do? Give up?

WHERE THERE'S A WILL

The way I found to call your own custom JavaScript on a condition for a date/timefield is to place it in the query via URL manipulation. Load the incident table and quickly build & run a plain "Opened > on > Today" filter. You'll get the following URL.

nav_to.do?uri=%2Fincident_list.do%3Fsysparm_query%3Dopened_atONToday@javascript:gs.beginningOfToday()@javascript:gs.endOfToday()%26sysparm_first_row%3D1%26sysparm_view%3D

If you'll take a look at the highlighted sections of the URL, you'll notice:

1. the display name you see in the condition builder,
2. the script for the beginning date/time for the query, and
3. the script for the end date/time of the query.

In case you don't know, those scripts are GlideSystem GlideDateTime JavaScript scripts stored in ServiceNow. That's why they start with "gs." - gs stands for GlideSystem! Fortunately, the gs.[units]AgoStart() and gs.[units]AgoEnd() functions accept integers! This means we can call them with custom integers to make our own non-standard Date/Time scripted condition.

Here's our options:

gs.minutesAgoStart()gs.minutesAgoEnd()gs.hoursAgoStart()gs.hoursAgoEnd()gs.daysAgoStart()gs.daysAgoEnd()

gs.weeksAgoStart() - missing

gs.weeksAgoEnd() - missinggs.monthsAgoStart()gs.monthsAgoEnd()gs.quartersAgoStart()gs.quartersAgoEnd()gs.yearsAgoStart()

gs.yearsAgoEnd()

We'll make our own custom scripts for weeksAgoStart and weeksAgoEnd in a moment. We'll make them function the same way the gs variants do.

However, first, here's how they work.

weeksAgoStart(3) performs two steps. The first step is that it goes back 3 x 7 days. If it's 5/22/2020 10:49:30, it goes back to 5/1/2020 10:49:30. You might be thinking it's similar to relative at this point, but don't forget the second step, which relative doesn't provide. The second step is that, from that point, it then goes back to the Monday of that week at 00:00:00. That is the beginning of the week.

weeksAgoEnd(1) also performs two steps. The first step is the same: it goes back 1 x 7 days. The second step is that, from that point, it then goes forward to the Sunday of that week at 23:59:59. That is the end of the week.

A combination of the two JavaScript functions above would give us the entire last 3 weeks, Mon-Sun of each week.

LET'S MAKE A WEEK VARIANT

Here are two scripts I wrote that imitate what gs.weeksAgoStart and gs.weeksAgoEnd would do, if they existed in the first place.

(I'm not sure why they omitted the week variants. I'm guessing that this family of scripts was created with the relative filter in mind and, since relative omits weeks as a time unit, no weeks variant of these scripts was created. Or maybe relative omitted weeks because the scripts were missing? Or maybe they thought they would be too week? (haha, get it?) Anyways, it's not important right now.)

So, these will allow us to build conditions based on week start and end points. Just create these as client callable Script Includes.

(These have been updated to honor the logged in user's timezone the same way the OOTB date/time filters do.) -06/05/2020

Object:	Script Include
Name:	weeksAgoStart
Application:	Global
Accessible from:	This application scope only
Client callable:	TRUE
Description:	Script include for use in query time conditions via list-view URL manipulation. Finds the start of n number of weeks ago. gs.weeksAgoStart is not available OOTB.
Script:	function weeksAgoStart(n){ var gdt = new GlideDateTime(); //SET THE DATE gdt.addWeeksLocalTime(n * -1); gdt.addDaysLocalTime( ( gdt.getDayOfWeekLocalTime() - 1 ) * -1 ); //CORRECT THE TIME gdt.setDisplayValueInternal(gdt.getDisplayValueInternal().split(' ')[0] + " 00:00:00"); //RETURN THE DATE return gdt;}

Object:	Script Include
Name:	weeksAgoEnd
Application:	Global
Accessible from:	This application scope only
Client callable:	TRUE
Description:	Script include for use in query time conditions via list-view URL manipulation. Finds the end of n number of weeks ago. gs.weeksAgoEnd is not available OOTB.
Script:	function weeksAgoEnd(n){ var gdt = new GlideDateTime(); //SET THE DATE gdt.addWeeksLocalTime(n * -1); gdt.addDaysLocalTime( 7 - gdt.getDayOfWeekLocalTime() ); //CORRECT THE TIME gdt.setDisplayValueInternal(gdt.getDisplayValueInternal().split(' ')[0] + " 23:59:59"); //RETURN THE DATE return gdt;}

UMM, WHAT NOW?

So, let's run that simple incident query again. The "Opened > on > Today". We'll get the same URL.

nav_to.do?uri=%2Fincident_list.do%3Fsysparm_query%3Dopened_atONToday@javascript:gs.beginningOfToday()@javascript:gs.endOfToday()%26sysparm_first_row%3D1%26sysparm_view%3D

This time, let's manipulate the URL. Change it to this:

nav_to.do?uri=%2Fincident_list.do%3Fsysparm_query%3Dopened_atONLast 3 weeks@javascript:weeksAgoStart(3)@javascript:weeksAgoEnd(1)%26sysparm_first_row%3D1%26sysparm_view%3D

(Since we're using our own custom script include - not the GlideSystem library - we don't use the gs prefix here!)

After you finish typing that in, press Enter to navigate to your edited URL. This will import your new custom date/time condition into the condition builder, and even apply the custom label you gave it!

I think I might know what you're thinking. "Okay, so how do I do this for reports?" Well, first we have to build the list-view query. After that, we import the query into the report designer via the context menu. Just choose "Bar Chart" or "Pie Chart" to pull the query into the report designer.

Here's what you'll end up with.
(I changed my query to "Activity due > on > Last 3 weeks " just to get results in my PDI.)

Now just Save the report to store that condition for later use. You will have to build all your custom-script Date/Time conditions from the list-view and import them into the report designer via this method. Save them to serve as templates for future reports using the same custom condition. Not ideal - pretty technical for some users - but this is really useful!

Take it a step further. Save your report as a report source! This accomplishes one small task and a second much larger task. First, it makes it easier to use these in reports in the future, and second, it makes these usable from Datasets and PA Indicators!-06/07/2020, 06/18/2020

( I've noticed that these queries are successfully preserved as 1) ServiceNow & browser favorites, 2) copy & pasted URLs, 3) imported into reports, and 4) saved as report sources. However, they don't work as saved filters [sys_filter]. The filter distorts the custom Date/Time condition by replacing the __ section of "javascript:_____()" with gs.dateGenerate, and also places the label in the (). Minor detail, just don't try to save these as filters. Save them as favorites, reports, report sources, or copy & pasted URLs instead. )

BACK TO THE EXAMPLE

So, back to the example from the introduction.

Armed with these new custom scripted date/timeconditions, this means that now we can make the "Last 3 weeks" - a.k.a. last 3 full weeks, Sun-Mon - report that our stakeholder requested without using hard-coded dates in the date/timecondition! That means we won't have to rebuild the report's date/timecondition every week if we put it on a dashboard, nor will we possibly have to strip it from the dashboard & instead schedule the report to run on Sunday at 23:59:59. Now we can place this puppy on the dashboard with confidence it will work no matter when he loads it! High-five!

I think it is so much more convenient this way.

MORE EXAMPLES

Not only that, but so many new options are available now, too.

• 2nd to Last Week
• 3rd to Last Week
• 4th to Last Week
• Last 2 Weeks
• Last 3 Weeks
• Last 4 Weeks
• 2nd to Last Month
• 3rd to Last Month
• 4th to Last Month
• 5th to Last Month
• 6th to Last Month
• Last 2 Months
• the list goes on and on, but those are among the ones I plan to use

To demonstrate the bold items in the above list, here's a visual.

Shown above is a dashboard that has monthly score cards for the last 6 months. Except for the top-right single-score report titled "1st Month", soft-coding these report's conditions is not possible OOTB using just the condition builder. That's right, the reports with highlighted titles are all using URL-manipulated custom date/time conditions that would likely otherwise have to be hard-coded dates. (Stop saying that word, Joseph. "hard-coded dates". Stop!)

Here is a convenience sample query for the "2nd Month" report:

nav_to.do?uri=%2Fincident_list.do%3Fsysparm_query%3Dopened_atON2nd to Last Month@javascript:gs.monthsAgoStart(2)@javascript:gs.monthsAgoEnd(2)%26sysparm_first_row%3D1%26sysparm_view%3D

Following this pattern, you can create the queries used for the other 4 reports. Combine these reports with some interactive filters and you get some very flexible score widgets.

ANOTHER EXAMPLE

In fact, by utilizing another custom script, we can even have a Last Business Day condition now! I'll throw those scripts in free, too, as I don't think they are available OOTB. (Are they?) Here they are, they operate on whatever GlideSchedule that you pass to them. This is useful if you need to be able to report on Friday when you load the report on Sat-Mon, but switch to reporting on yesterday when you load the report on Tues-Friday.

(These have been updated to 1) work based on GlideSchedules, 2) work with any # of bdays, 3) work for past or future dates, 4 ) not count holidays as bdays.) -06/02/2020
(Updated again to 5) honor the user's timezone, and 6) have a better infinite loop fail safe.) -06/05/2020
(Updated again to 7) honor the system's date/time format and the user's time zone, to remain compatible with the system no matter the user's date/time format.) -09/17/2020
(Updated again to work from all application scopes since users will likely want to use this filter across multiple scopes.) -09/18/2020

Object:	Script Include
Name:	bdaysAgoStart
Application:	Global
Accessible from:	All application scopes
Client callable:	TRUE
Description:	Script include for use in query time conditions in order to find & set the start (00:00:00) of n number of "Business Days" ago.A current goal is to return the "business day" in the user's time zone, to provide expanded compatibility for service desks to query based on their service desk's time zone.
Script:	function bdaysAgoStart(n){ var gdt = new GlideDateTime(); var sched = new GlideSchedule('090eecae0a0a0b260077e1dfa71da828'); //8-5 Excluding Holidays, floating timezone var i = n > 0 ? -1 : 1; //if positive n subtract days, otherwise add var lfs = 170; //Loop Fail Safe: prevents massive loops while allowing up to ~1 year query //CALIBRATE TO A BUSINESS HOUR gdt.setDisplayValueInternal(gdt.getDisplayValueInternal().split(' ')[0] + ' 11:00:00'); //PROCESS THE DAYS while( n != 0 && lfs != 0 ){ gdt.addDaysLocalTime(i); if (sched.isInSchedule(gdt)) //defaults to user's time zone n = n + i; else lfs = lfs - 1; } //CORRECT THE TIME gdt.setDisplayValueInternal(gdt.getDisplayValueInternal().split(' ')[0] + " 00:00:00"); //RETURN THE DATE return gdt;}

Object:	Script Include
Name:	bdaysAgoEnd
Application:	Global
Accessible from:	All application scopes
Client callable:	TRUE
Description:	Script include for use in query time conditions in order to find & set the end (23:59:59) of n number of "Business Days" ago.A current goal is to return the "business day" in the user's time zone, to provide expanded compatibility for service desks to query based on their service desk's time zone.
Script:	function bdaysAgoEnd(n){var gdt = new GlideDateTime();var sched = new GlideSchedule('090eecae0a0a0b260077e1dfa71da828'); //8-5 Excluding Holidays, floating timezonevar i = n > 0 ? -1 : 1; //if positive n subtract days, otherwise addvar lfs = 170; //Loop Fail Safe: prevents massive loops while allowing up to ~1 year query//CALIBRATE TO A BUSINESS HOURgdt.setDisplayValueInternal(gdt.getDisplayValueInternal().split(' ')[0] + ' 11:00:00');//PROCESS THE DAYSwhile( n != 0 && lfs != 0 ){gdt.addDaysLocalTime(i);if (sched.isInSchedule(gdt)) //defaults to user's time zonen = n + i;elselfs = lfs - 1;} //CORRECT THE TIMEgdt.setDisplayValueInternal(gdt.getDisplayValueInternal().split(' ')[0] + " 23:59:59"); //RETURN THE DATEreturn gdt;}

You call these in the same fashion. But here is a sample query, in case it helps:

nav_to.do?uri=%2Fincident_list.do%3Fsysparm_query%3Dopened_atONLast Business Day@javascript:bdaysAgoStart(1)@javascript:bdaysAgoEnd(1)%26sysparm_first_row%3D1%26sysparm_view%3D

Now we have a lot of additional options again.

• Last Business Day
• Last 5 Business Days
• Next 3 Business Days
• this list could go on and on...

ENJOY!

It is my hope that you find this article useful. I apologize if this is common knowledge, but I had a hard time finding it personally. This is my personal result of a fair amount of digging & determination. Please feel free to use this knowledge and contribute your own. Thanks!

Kind Regards,

Joseph

Labels:

• Dashboard
• Multiple Versions
• Reporting