Dynamic filter for Change Requests between Friday and next Friday

[benaLi-medxc]

Hi everyone,

I’m trying to create a dynamic filter on the Change Request table based on the Planned Start Date.

The filter should always return changes where the Planned Start Date is between the upcoming Friday and the Friday after it.

This should work automatically every week (not for specific dates), so the report/dashboard always shows changes planned between two Fridays.

What would be the best way to implement this kind of dynamic Friday-to-Friday filter in ServiceNow?

Thanks.

[Tanushree Maiti]

Hi @benaLi-medxc  

My suggestion would be to create script include to return changes for this Friday to friday and call the script include function from your dynamic filter ->script  .

Then apply the dynamic filter to your report.

1.Sample script include (not tested ,modify as per your requirement)

PlannedStartDateFilter.prototype = {
getDateRange: function() {
var start = new GlideDateTime();
var end = new GlideDateTime();
// Calculate the upcoming Friday
var dayOfWeek = start.getDayOfWeek(); // Sunday=1, Friday=6, Saturday=7
var daysUntilNextFriday = (6 - dayOfWeek + 7) % 7;

if (daysUntilNextFriday === 0 && dayOfWeek !== 6) {
daysUntilNextFriday = 7;
}
start.addDays(daysUntilNextFriday);

start.setHourUTC(0);
start.setMinuteUTC(0);
start.setSecondUTC(0);

// Calculate the Friday after the upcoming Friday (add 7 more days)
end.addDays(daysUntilNextFriday + 7);
end.setHourUTC(23);
end.setMinuteUTC(59);
end.setSecondUTC(59);

// Return an encoded query for the date range
return "planned_start_dateBETWEENjavascript:gs.dateTimeStandardFormat('" + start.getDisplayValueInternal() + "')@javascript:gs.dateTimeStandardFormat('" + end.getDisplayValueInternal() + "')";
},

type: 'PlannedStartDateFilter'
};

2.In your dynamic filter script : new PlannedStartDateFilter().getDateRange();

refer : https://www.youtube.com/watch?v=cOdhEDbMgEg

[benaLi-medxc]

Hi Tanushree,

Thank you for your suggestion.

I tried the approach, but it does not seem to work for my case. The field I need to filter is Planned Start Date, which is a date/time field. Even after creating the dynamic filter and the Script Include, the filter option does not appear in the operator options for the date field in the condition builder.

Because of that, I am not able to select or apply this dynamic filter on the Planned Start Date field in the report or dashboard filter.

So it seems this approach may not work for date/time fields in this scenario.

Thanks again for your help.

[Ankur Bawiskar]

@benaLi-medxc 

are you creating report for this on CHG table?

If yes then you can update the report filter condition dynamically using scheduled job which runs every Friday end of day so that the report condition gets updated properly

I shared solution here earlier, check and enhance the script

Can we write script to change reports filter condition? 

💡 If my response helped, please mark it as correct ✅ and close the thread 🔒— this helps future readers find the solution faster! 🙏

[benaLi-medxc]

Yes, this report is on the Change Request (CHG) table.

Your suggestion about updating the report filter using a scheduled job is interesting and could work in some scenarios. However, in my case the dashboard needs to show real-time data.

My managers use the dashboard to monitor upcoming changes for CAB, so the data needs to update automatically based on the current date/time. If the filter condition is updated only by a scheduled job (for example every Friday), it may not always reflect the current situation in real time.

For this reason, I’m trying to find a solution where the report filter works dynamically on the Planned Start Date field, without needing to update the report condition periodically.

Thanks for sharing the idea and the reference.