How can I generate the On-Call USER information in a tab on the Incident form?

[Hola Ola]

Hello,

I am in the process of building on-call schedules for each IT support groups in my organization. 

My intention is, depending on the Assignment Group that an Incident is assigned/routed to, generate a widget that will display the assignment group's on-call person name and listed phone number.

I have created a Relationship between the Incident and cmn_rota_member tables.

I have also created a UI Action Related link - which if clicked SHOULD generate the on-call information that I need.

>>>>

I think what I'm probably missing is the scripting that'll make all these work!

Basically, what I'm ultimately looking to achieve is; If an Incident is a P1 or P2 to generate the name/contact information of the user that is on-call for the assignment group - Which will help the Help Desk in reaching out to the person ASAP!

I will appreciate any or all the help that I can get.

[Hajar BENJAHHAR]

Hello Hola Ola, 

That is my attempt for your requirement : 

    1- a Relationship between the Incident and cmn_rota_member tables.

Script : 

(function refineQuery(current, parent) {

	if(parent.assignment_group.name!=""){
		current.addEncodedQuery("rotation_schedule.nameSTARTSWITH" + parent.assignment_group.name);
	}else{
		current.addEncodedQuery("rotation_scheduleISEMPTY");
	}
   
	
})(current, parent);

     2- UI Action : it will redirect to the user information : 

Results : 

The On call related list : 

 And if the user clicks on the Generate Support member when incident is P1/P2, it will be redirected to the profil of Beth Anglin. 

I hope it will be helpful. 

[Hola Ola]

Hello and good evening Hajar, 

I would like to say a HUGE thank you for your response, which has gotten me closer to where I want to be.

However, I am wondering if there's a cleaner way of getting the needed information?

When I inserted the script and condition (below) into the 'Generate Support Member' UI Action form, what I get in return are random users - who may or may not be in the assignment group.

Condition: 
(current.priority==1||current.priority==2)&&(!gs.nil(current.assignment_group))

Script: 
var grSupportMember = new GlideRecord('cmn_rota_member');
grSupportMember.addEncodedQuery("rotation_schedule.nameSTARTWITH" + current.assignment_group.name + "^roster.name=Primary");
grSupportMember.orderBy('order');
grSupportMember.query();
if (grSupportMember.next()) {
action.setRedirectURL(gs.getProperty('glide.servlet.uri')+"sys_user.do?sys_id="+grSupportMember.member);
}else{
action.setRedirectURL(current);
}

I'm testing with an On-Call schedule with 3 members spanning 4 different shifts - which different START and END times.

OR:

In The On-Call Tab, is there anyway I can just generate/isolate the name of the On-Call (Primary) User without the secondary or tertiary?

[INFJames]

Did you find a solution to this?

My humble attempt (ongoing), note that you also need to consider if someone has done a shift swap, hence the lookup to roster_schedule_span table, which seems to house this, at least in some capacity.

You can run this at /now/nav/ui/classic/params/target/%2Fsys.scripts.do in your sub instance

var groupSysId = 'XXXXXXXXXXXXXXXXXX'; // change this to dynamically input the assignment group's sys_ID


var onCallDisplayName = ''; // Variable to store the on-call display name
var currentTime = new GlideDateTime(); // Gets the current date and time

// Query the cmn_rota table to find the shift related to the assignment group
var rota = new GlideRecord('cmn_rota');
rota.addQuery('group', groupSysId);
rota.addQuery('active', true);
rota.query();

if (rota.next()) {
//gs.print('Assignment group record found.');


// Query the roster_schedule_span table to find any changes to the rostered schedule
var rosterScheduleSpan = new GlideRecord('roster_schedule_span');
rosterScheduleSpan.addQuery('group', groupSysId);
rosterScheduleSpan.addQuery('show_as', 'on_call');
rosterScheduleSpan.addQuery('type', 'on_call');
rosterScheduleSpan.query();

while (rosterScheduleSpan.next()) {
gs.print('replacement found, ' + rosterScheduleSpan.schedule.getDisplayValue());
var startDateTime = rosterScheduleSpan.start_date_time.getDisplayValue();
var endDateTime = rosterScheduleSpan.end_date_time.getDisplayValue();
var parsedStartDateTime = new GlideDateTime(startDateTime);
var parsedEndDateTime = new GlideDateTime(endDateTime);
gs.print('currentTime' + currentTime);
gs.print('startDateTime ' + startDateTime);
gs.print('endDateTime ' + endDateTime );
gs.print('parsedStartDateTime ' + parsedStartDateTime );
gs.print('parsedEndDateTime ' + parsedEndDateTime );
// Check if the current time falls within the schedule span
if (currentTime.compareTo(parsedStartDateTime) >= 0 && currentTime.compareTo(parsedEndDateTime) <= 0) {
// Retrieve the replacement staff's name from the related roster_schedule_span record
var replacementStaff = rosterScheduleSpan.getDisplayValue('schedule'); // Assuming 'schedule' is the field storing the replacement staff's name
onCallDisplayName = (replacementStaff != '') ? replacementStaff : onCallDisplayName; // If replacement staff exists, use it; otherwise, keep the current on-call display name
break; // Exit the loop once the on-call person is found
}
}


// Query the cmn_rota_roster table to find the roster related to the shift
var roster = new GlideRecord('cmn_rota_roster');
roster.addQuery('rota', rota.sys_id);
roster.query();

if (roster.next()) {
// Query the cmn_rota_member table to find the on-call members related to the roster
var member = new GlideRecord('cmn_rota_member');
member.addQuery('roster', roster.sys_id);
member.query();

if (member.getRowCount() == 1 && member.next()) {
onCallDisplayName = member.member.getDisplayValue();
//gs.print('On-call person is: ' + onCallDisplayName);
} else {
// If there are multiple members in the roster, proceed with checking schedule spans
//gs.print('Multiple members in the roster.');

while (member.next()) {
// Query the cmn_schedule table to find the schedules related to the on-call member
var schedule = new GlideRecord('cmn_schedule');
schedule.addQuery('document_key', member.sys_id);
schedule.query();

while (schedule.next()) {
// Query the cmn_schedule_span table to find the schedule spans for the on-call user
var scheduleSpan = new GlideRecord('cmn_schedule_span');
scheduleSpan.addQuery('schedule', schedule.sys_id);
scheduleSpan.addQuery('show_as', 'on_call');
scheduleSpan.query();

while (scheduleSpan.next()) {
//gs.print('Schedule span record found. ' + scheduleSpan.name);

var startDateTime = scheduleSpan.start_date_time.getDisplayValue();
var endDateTime = scheduleSpan.end_date_time.getDisplayValue();

var startDateTimeParts = startDateTime.split(' ');
var endDateTimeParts = endDateTime.split(' ');

var startDateParts = startDateTimeParts[0].split('/');
var startTimeParts = startDateTimeParts[1].split(':');

var endDateParts = endDateTimeParts[0].split('/');
var endTimeParts = endDateTimeParts[1].split(':');

var parsedStartDateTime = new GlideDateTime(startDateParts[2] + '-' + startDateParts[1] + '-' + startDateParts[0] + ' ' + startTimeParts[0] + ':' + startTimeParts[1] + ':' + startTimeParts[2]);
var parsedEndDateTime = new GlideDateTime(endDateParts[2] + '-' + endDateParts[1] + '-' + endDateParts[0] + ' ' + endTimeParts[0] + ':' + endTimeParts[1] + ':' + endTimeParts[2]);
/*
gs.print('currentTime ' + currentTime);
gs.print('startDateTime ' + startDateTime);
gs.print('endDateTime ' + endDateTime);
gs.print('parsedStartDateTime ' + parsedStartDateTime);
gs.print('parsedEndDateTime ' + parsedEndDateTime);
gs.print('currentTime.compareTo(parsedStartDateTime)' + currentTime.compareTo(parsedStartDateTime));
gs.print('currentTime.compareTo(parsedEndDateTime)' + currentTime.compareTo(parsedEndDateTime));
*/
// Check if the current time falls within the schedule span
if (currentTime.compareTo(parsedStartDateTime) >= 0 && currentTime.compareTo(parsedEndDateTime) <= 0) {
onCallDisplayName = member.member.getDisplayValue(); // Store the display name of the on-call user
//gs.print('On-call person is: ' + onCallDisplayName);
break; // Exit the loop once the on-call person is found
}
}
}
}
if (!onCallDisplayName) {
gs.print('No on-call person found for the provided assignment group.');
}
}
} else {
gs.print('No roster found for the provided assignment group.');
}
} else {
gs.print('No roster found for the provided assignment group.');
}

if (onCallDisplayName) {
gs.print('The on-call user is: ' + onCallDisplayName);
} else {
gs.print('No current on-call user found for the provided assignment group.');
}