What is the correct method to query Schedule Date/time fields in cmn_schedule_span?

[sid31]

Hi all,

I'm trying to write a query that I thought should be relatively simple but is proving challenging. I would like to write a query that filters entries in the cmn_schedule_span based on the start_date_time field (e.g. show all entries where start_date_time >= 7 days from now). However because start_date_time is of type "Schedule Date/time" I don't get the regular date filter options:

What is the correct way query this field type? Ultimately I'm trying to build an encoded query to use in a reference qualifier where users can select a particular schedule entry.

Thanks

Sid

[Gurpreet07]

Hi Siddharth,

First apply 'is' filter and then in address bar change the operator to '>' and you should be able to use the filter. Below should work fine

/cmn_schedule_span_list.do?sysparm_query=start_date_time>javascript:gs.daysAgo(-7)

[sid31]

Thanks Gurpreet. I thought of that too but wasn't sure how robust that method is and wanted to see if there is a better way to do the comparison.

[Aditya Mallik]

Siddharth,

Please note that the date part that is stored in Start Date Time and End Date Time fields of cmn_schedule_span are only representative dates, not the real values. cmn_schedule_span will have the "schedule" represented as a record. So essentially you have to look at the record as a whole, and take into consideration the other fields on the record like Schedule, Repeat on, Type, Repeat until, etc. to find out if that specific schedule record is active on a given date or not.

If you try to use a simple query on Start Date Time and End Date Time fields of cmn_schedule_span, you are most likely to get wrong results.

https://developer.servicenow.com/app.do#!/api_doc?v=istanbul&id=r_OCR-spansOverlap_AL_AL_S

to understand how to query for the schedule span records, that have a overlap (or are active) on a given date/time range.

In your case, maybe you have all the Schedule Span Entries as single date entries, and not as repeating entries, and hence you might be looking for this simple query approach. I am afraid it is not possible to achieve it in a simple list query. If your users are spread across geographies even a simple "is" operator would give wrong results, because it would not consider the user's timezone before executing the query.

If your interest is to make users aware of the upcoming schedules, I would recommend that you direct them to the Show Schedule UI action on the corresponding Schedules. You can create a left side module to point users to the Show Schedule UI action. The URL would look like:

show_schedule.do?sysparm_stack=no&sysparm_sys_id=08fcd0830a0a0b2600079f56b1adb9ae&sysparm_domain_restore=false&sysparm_stack=no

Where sys_id is pointing to the Schedule (cmn_schedule) sys_id. It will take care of representing the Span entries appropriately.

Thanks,

Aditya.

[sid31]

Thanks for the detailed response Aditya. You are correct that my current use-case is relatively simple (i.e. all users use a single timezone tied to the HQ, user does not currently use the Repeat until function), which is why I considered this path.

That said, can I do the comparison properly programmatically? So if i wrote a function that had the following logic:

1. Loop through each schedule span record

2. Compare it to my exclusion span (2016, Now+7 days) using the spansOverlap method //The 2016 date to exclude any historical spans

3. Build a list of sys_ids where the compare == false

4. Pass the list of sys_ids to my reference qualifer

Would this be a better method? Is there a way to accommodate the 'Repeat until' functionality if the client does use that in the future?

The purpose of this is to show a list of possible schedules for users to pick from for a change request. Once they pick a particular entry, we set the CR start and end date using this.