On-Call Calendar breaking My Calendar and Resource Management anyone have a workaround?

akaupisch · ‎09-19-2016

I've already submitted a ticket with SN, but I'm doubtful we'll see a resolution anytime soon. The only workaround they suggested is through Delegates, but even that doesn't solve our issue. I'm hoping someone on here can point me in the right direction...

Background:

We have Calendars (schedules) setup for each user in our system following a typical 40 hour work week. When they click on My Calendar it all looks great. When you go through Resource Management, it shows 40 hours for that user, again, great. We also use the On-Call Calendar and have automatic rotations setup (e.g. You are in a group with 2 other people...so 3 total people, every 3 weeks you are on call and it's a 24x7 on call that week). If you never touch anything, it works perfectly.

Issue:

Say you need someone to cover your on call. There are 2 ways of doing this and thus 2 issues as neither works properly.

1) Go to the on-call calendar and create a new entry for the time you are covering. In this case, say Bob is on call this week but needs Fred to cover all day Wed (midnight to midnight). Fred (or Bob) goes into the on-call calendar, and creates a new entry for this (which creates an entry into the roster_schedule_span table). This entry links to the schedule for that user and also creates an entry in the cmn_schedule_span table as 'on_call'. It renders in the On-call Calendar just fine, however, it has now mucked Fred's Calendar (it'll show Fred being 'available' from midnight to midnight)...It now shows him as having a 56 hour week instead of a 40 hour week in Resource Management (and his calendar). This is a problem because being oncall doesn't mean Fred is actually working during that time, yet PMs can request him during that time through Resource Management.

2) (the workaround SN told us to use)...Same situation, Bob is on call, needs Fred to cover. Bob creates a delegate during this time and now all tickets go to Fred during this time. However, this is not represented in the on-call calendar...it still shows Bob on-call during that time (This is super problematic as we have multiple departments who might need to reach out to Bob's group...the only way to do this is to view the on-call calendar for Bob's group...in this case it shows Bob...they call Bob but he's not answering...very bad for a hospital)

Both issues are bad...in one case you have the on-call calendar correct and the resource calendar incorrect...in the other case the resource calendar is correct but the on-call calendar is not.

My Assumptions:

Issue 1) It would appear that whatever is used to render the resource calendar is not respecting the 'type' (in this case being 'on_call') and is returning all spans for that user, when, in fact, it should be ignoring PTO and On-Call (or if it were to show it, it should do so in a different color). Regardless, Resource Management is probably using this same code and isn't respecting that on_call is not actual working hours. I would love to change this code, but I don't see any script includes, ui scripts, or business rules that return this data. The URL for the calendar references ("/show_resource_schedule.do?sysparm_resviewtype=calendar") But it looks like show_resource_schedule.do is back-end and we don't have access to it

Issue 2) It would appear that the on-call calendar is not respecting delegates and not rendering them properly. It should be looking at whomever in delegated and displaying that instead. However, just like Issue 1, all of that seems to take place in code that I do not have access to or simply cannot find...

Alternative) Somehow ignore the calendars and focus on Resource Management. Determine what is returning the spans and hijack them to not include those extra hours. Total hack and probably requires too many changes...

Solutions? Thoughts?

Michael Ritchie · ‎09-19-2016

Believe I have a workaround for you, at least it works for me with a quick test. Query business rules are a great feature with many uses but in a nut shell they can apply filters in the background. Basically my solution leverages one that will detect that the URL is coming from Resource Management and if so filter out the On-Call schedule entries.

Go to System Definition\Business Rules and click New.
In the Name field enter something like Schedule Entry Query.
Set the table to Schedule Entry (cmn_schedule_span)
Check the Advanced Checkbox
Ensure When on the When to run section is set to before
Check the Query checkbox on the When to run tab
On the advanced tab, enter the following script and then submit the record.

(function executeRule(current, previous /*null when async*/) {


       var map = gs.action.getGlideURI().getMap();

       var referringProcessor = map.get('sysparm_processor').toString();

       if (referringProcessor.indexOf("com.snc.resource_management") > -1) {

               var qc = current.addQuery("show_as", "!=", "on_call");

       }


})(current, previous);

You can add other conditions but basically the way I got here is I logged the URI to determine what I could filter on. To do this, I simply put in a statement in this script include:

gs.log(gs.action.getGlideURI());

The results from when I was viewing the Resource Schedule showed this in the log:

xmlhttp.do?end=2016-9-25+00%3a00%3a00&ni.nolog.x_referer=ignore&start=2016-9-18+00%3a00%3a00&sys_id=&sysparm_processor=com.snc.resource_management.AjaxResourceSchedule&sysparm_scope=global&sysparm_type=get&sysparm_want_session_messages=true&user_id=&x_referer=show_resource_schedule.do%3fsysparm_resviewtype%3dcalendar

So I chose to get the sysparm_processor from that URI and look for it to apply a filter of NOT including on_call schedule entries.

I hope this helps! Please mark this post as helpful or the solution to your question.

View solution in original post

Michael Ritchie · ‎09-19-2016

Believe I have a workaround for you, at least it works for me with a quick test. Query business rules are a great feature with many uses but in a nut shell they can apply filters in the background. Basically my solution leverages one that will detect that the URL is coming from Resource Management and if so filter out the On-Call schedule entries.

Go to System Definition\Business Rules and click New.
In the Name field enter something like Schedule Entry Query.
Set the table to Schedule Entry (cmn_schedule_span)
Check the Advanced Checkbox
Ensure When on the When to run section is set to before
Check the Query checkbox on the When to run tab
On the advanced tab, enter the following script and then submit the record.

(function executeRule(current, previous /*null when async*/) {


       var map = gs.action.getGlideURI().getMap();

       var referringProcessor = map.get('sysparm_processor').toString();

       if (referringProcessor.indexOf("com.snc.resource_management") > -1) {

               var qc = current.addQuery("show_as", "!=", "on_call");

       }


})(current, previous);

You can add other conditions but basically the way I got here is I logged the URI to determine what I could filter on. To do this, I simply put in a statement in this script include:

gs.log(gs.action.getGlideURI());

The results from when I was viewing the Resource Schedule showed this in the log:

xmlhttp.do?end=2016-9-25+00%3a00%3a00&ni.nolog.x_referer=ignore&start=2016-9-18+00%3a00%3a00&sys_id=&sysparm_processor=com.snc.resource_management.AjaxResourceSchedule&sysparm_scope=global&sysparm_type=get&sysparm_want_session_messages=true&user_id=&x_referer=show_resource_schedule.do%3fsysparm_resviewtype%3dcalendar

So I chose to get the sysparm_processor from that URI and look for it to apply a filter of NOT including on_call schedule entries.

I hope this helps! Please mark this post as helpful or the solution to your question.

akaupisch · ‎09-20-2016

Worked like a charm, thanks!

Going to follow up with a few things here for others to learn from if they have the same issue:

1) For resource management to work, you must define a unique schedule for each user.

2) That schedule must have a child schedule that is setup as your 'work week' (not sure why it's not called a parent, but whatevs). The default out of the box SN one is Resource Management Schedule...you can easily create a script to attach this to all users if they don't have it setup properly.

3) In the above answer, the only thing he leaves out is that the business rule needs to be on cmn_schedule_span (pretty obvious once you dig in but for noobs, it isn't always apparent)

4) I've tested this on multiple forms and it works...Resource Workshop, Resource Reports, My Calendar, On-Call Calendar. I have not tested everywhere but I'm assuming SN includes all schedules under the service_management package he is searching for. There could be cases of legacy that might not? dunno, worth noting that I haven't tested it everywhere, but I'm confident it'll work everywhere that matters...

One last thing Michael, I don't see a lot of documentation on before and after on queries. In particular the objects that are passed in and returned. Through my debugging, I attempted to print out the properties of current (on before) and it printed out the props but not the values. Likewise, on the after I wanted to see the object to determine what I could manipulate. Not that there was a need to, but for my own curiosity. Can you point me in the right direction? I saw the article about scripting in business rules, but it was a bit lacking for what I wanted.

Thanks again, you rock!

Michael Ritchie · ‎09-20-2016

Awesome glad it worked. Thank you for pointing out I forgot to mention the cmn_schedule_span table. I just updated my response to include that very important step.

This solution should be safe as referring processor shouldn't be set to resource from other areas of the platform. But like you are doing, please regression test!

Query business rules are very powerful and something I consider the "other ACL" since they can filter out records during the execution of a query. They only apply before queries happen so not after. There are out of the box examples such as a business rule called "incident query" that you can learn from that restricts incidents for "ESS" (end users/requesters) to only ones where they are the caller, they opened the incident, or they are on the watch list.

Steve Bell created a "mini-lab" on query business rules that you can also learn from:

Mini-Lab: A Query Business Rule Demonstration

akaupisch · ‎09-22-2016

Sorry to keep bothering, but we ran into an issue while testing. The On-Call Calendar (roster_schedule_span) inherits from cmn_schedule_span. Based on the documentation on business rules, I didn't think inherited tables ran the biz rules of the parent table? If that is correct, then our issue doesn't make sense.

The issue we have is in the On Call Calendar is taking an incredibly long time to run when this business rule is turned on...runs fine when it's off...What am I missing/lacking in knowledge on this? As for my calendar/other locations that use the business rule, there isn't a noticeable delay, it's only on the On Call Calendar.

here's the logs, thoughts?

Green = Biz Rule Inactive

Red = Biz Rule Active

Type - REST

URL - /api/now/on_call_rota/spans?time_stamp=1474405520651&group_ids=4b5af149644534006d3dac780c39e63b&sysparm_ck=d5de1...97d85 (length=72)&from=2016-09-25&to=2016-11-07&api=api

Response time - 25,217

SQL time - 2,331

SQL count - 5,753

Business rule time - 7,138

Type - REST

URL - /api/now/on_call_rota/spans?time_stamp=1474414186186&group_ids=edad23bff0a4b000e4310d34b264d48d&sysparm_ck=d5de1...97d85 (length=72)&from=2016-07-31&to=2016-09-12&api=api

Response time - 38,445

SQL time - 2,137

SQL count - 3,530

Business rule time - 126

Business rule count - 3,077

Type - REST

URL - /api/now/on_call_rota/spans?time_stamp=1474414186185&group_ids=edad23bff0a4b000e4310d34b264d48d&sysparm_ck=d5de1...97d85 (length=72)&from=2016-08-28&to=2016-10-10&api=api

Response time - 25,388

SQL time - 2,150

SQL count - 3,531

Business rule time - 168

Business rule count - 3,077

Type - REST

URL - /api/now/on_call_rota/spans?time_stamp=1474414186186&group_ids=edad23bff0a4b000e4310d34b264d48d&sysparm_ck=d5de1...97d85 (length=72)&from=2016-09-25&to=2016-11-07&api=api

Response time - 12,963

SQL time - 1,956

SQL count - 3,530

Business rule time - 140

Business rule count - 3,077

Type - REST

URL - /api/now/on_call_rota/spans?time_stamp=1474414510127&group_ids=edad23bff0a4b000e4310d34b264d48d&sysparm_ck=d5de1...97d85 (length=72)&from=2016-08-28&to=2016-10-10&api=api

Response time - 145,357

SQL time - 41,562

SQL count - 101,321

Business rule time - 127,402

Business rule count - 6,256

Type - REST

URL - /api/now/on_call_rota/spans?time_stamp=1474414510128&group_ids=edad23bff0a4b000e4310d34b264d48d&sysparm_ck=d5de1...97d85 (length=72)&from=2016-07-31&to=2016-09-12&api=api

Response time - 295,722

SQL time - 42,661

SQL count - 101,284

Business rule time - 131,292

Business rule count - 6,254