Reference qualifier that filters on a related list of the referenced table

[taylor21]

Hi all,

I am attempting to create a reference field that only shows records that have the current customer in their related list. So here are some details:

A student submits an incident in relation to a course.

There is a field on the incident form that references a table of courses [u_courses].

That field should only show courses that the student is currently in.

There is a table that creates the relationship between the student [sys_user] and the course [u_courses] called [u_student_course_relationship].

How can I get this reference field to only show those records?

[Dubz]

You need to write a script include and call it as an advanced reference qualifier. In your script include you can glide into the u_student_course_relationship table and get a list of all the courses that student is enrolled in and return a query string with those sys id's eg:

function scriptIncludeFunction(){
var arr = [];
var gr = new GlideRecord('u_student_course_relationship');
gr.addQuery('u_student', gs.getUserID());
gr.query();
while(gr.next()){
arr.push(gr.getUniqueValue());
}
return 'sys_idIN' + arr;
}

[taylor21]

Thank you so much! I feel like this is on the right track. This field will be filled out by the technician after the incident is submitted. Do I need to use something else in place of gs.getUserID()? The field we will be using for the customer (student) is called caller_id which is a reference to sys_user.

[taylor21]

Something like this maybe? (I tried this but it didn't work, am I on the right track though?): 

function getMyCoursesIncident(){
var arr = [];
var gr = new GlideRecord('u_student_course_relationship');
var caller = g_form.getValue('caller_id');
gr.addQuery('u_user', caller);
gr.query();
while(gr.next()){
arr.push(gr.getUniqueValue());
}
return 'sys_idIN' + arr;
}

[dvp]

In script include we cannot use g_form.

Try the below script Include

function getMyCoursesIncident(id){
var arr = [];
var gr = new GlideRecord('u_student_course_relationship');
gr.addQuery('u_user', id);
gr.query();
while(gr.next()){
arr.push(gr.getUniqueValue());
}
return 'sys_idIN' + arr;
}

In reference qualifier on u_course field you need to call the script include and pass the caller value

javascript: getMyCoursesIncident(current.caller_id)