Adding a new filter value for datetime fields

[diogo_ramos]

Hello guys does anyone know if it's possible to add an option to filters that use datetime fields ?

I would like to have an option here that would say Last 2 weeks (this has to be dynamic as we need all the data created over the last two weeks not counting the current one, this is why I'm not using the relative operator) this has to run every week and get data for those prior 2 weeks.

I tried to find something about this but wasn't able to, currently my approach is to run a script include in the filter and return the sys_id of the records that match the conditions I build in my script include query. If anyone knows a way of adding a new option in the image let me know as this would be more user friendly for users using reports.

Cheers,

Diogo

[Allen Andreas]

Hi,

You can use the relative operator for this, for example:

If screenshot is hard to see....essentially you'd choose your date/time field, then relative, then on or after, 14, days ago.

[Allen Andreas]

Hi,

This isn't easily done, per my research, but this should help:
https://community.servicenow.com/community?id=community_question&sys_id=43628beddb98dbc01dcaf3231f96...

[diogo_ramos]

Hey Allen,

Cheers, for that I knew I could follow the ui script route, but in the end I ended up just creating a script include and calling it from the reports such as 

sys id = javascript: getLast2WeeksTimeWorked(); 

+ script include

function getLast2WeeksTimeWorked() {
	
	var ids = [];
	var endDate = gs.endOfLastWeek(); // End date
	
	var startDate = new GlideDateTime(gs.beginningOfLastWeek());
	startDate.addDays(-7); // start date 1 week before last
	
	//gs.log("start of the date is " + startDate + " end of date is " + endDate);
	
	// setup the query and get the record ids
	var tw = new GlideRecord("task_time_worked");
	tw.addQuery("u_start_datetime",">=", startDate);
	tw.addQuery("u_start_datetime","<=", endDate);
	tw.query();
	
	while (tw.next())
		ids.push(tw.sys_id.toString()); // populate array with ids
	
	return ids;
	
}

Got me what I wanted.

Cheers,

Diogo

[Cosmo2b]

Yeah, we really need an option to increment, decrement, or set number of weeks. 1 week, 2 weeks, 3 weeks, etc.  Need it to work for those that are locked out of scripting.  It would be useful to overcome that limitation of the tool.

Ron