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?

[taylor21]

Thank you for your help - I tried this and added the call to the reference qualifier for the field. It is now bringing up no records. Any ideas on what I might be doing incorrectly? 

Here is the script and a visual of what I am seeing:

What I am seeing on the incident form:

[dvp]

Can you replace

return 'sys_idIN' + arr;

with

return 'sys_idIN' + arr.toString();

and also can you share what you have for reference qualifier

[dvp · Accepted solution]

In the earlier code we are passing the sys_id of the u_student_course_relationship data not the course sys_id's.

Can you try the below script

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.u_course.toString()); // Update u_course field name to respective
}
return 'sys_idIN' + arr.toString();
}

[taylor21]

You are the best. That worked beautifully. I just had to change the gr.u_course.toString(); to reflect the actual name of the course field and it worked! 

[Yifeng Zhang]

I'll just add a comment where I did quite a bit investigation on related list queries, 

Let's assume

u_student_course_relationship.u_course  stores the reference to [u_courses]

u_student_course_relationship.u_student stores the reference to [sys_user]

you could do a ref qualifier like this instead of using a script:

^RLQUERYu_student_course_relationship.u_course,>=1^u_student= javascript:gs.getUserID()^ENDRLQUERY

this will get the list of [u_courses] that has at least 1 record in the [u_student_course_relationship] table which reference a student that is the current user.

Here's the doc:

https://docs.servicenow.com/bundle/london-platform-user-interface/page/use/using-lists-v3/task/create-related-list-query.html