Field to reference qualifier from 2 tables

[Max Lin]

I have 2 fields and both of them are stored in different table

Assignment Group  (sys_user_group table)
Assigned Group's Assessor (u_assessor table).

I can pull related list out from the group view. see below screenshot. 

u_assessor list view:

However, i cant seems to make the field Assigned Group's Assessor. 

Field: 

I think it's related to this reference qualifier. I can't seems to figure out.. What should the javascript...

Thanks all!

[Ankur Bawiskar]

Hi,

your ref qualifier is for field which refers to sys_user table

How are you filtering the users based on assessor?

Regards
Ankur

[Max Lin]

Hi Ankur!

There is a Assessor table u_assessor that includes assessor's username and their group. 

In another words
Table sys_user contains all the user list
Table sys_user_group contains user's grouping (screenshot below)
Table u_assessor contains assessors belonging to the group (related list can pull out assessor)

So i am trying to get the Assessor's list based on this group selection. Assessor is in table u_assessor.

Thanks Ankur! 

[Ankur Bawiskar · Accepted solution]

Hi,

But your field is referring to sys_user table

you would require scripting with advanced ref qualifier

1) pass the group

2) query assessor table with this group and push all the users into array

3) return those users

Advanced ref qualifier

javascript:getUsers(current.assignment_group);

Script Include:

function getUsers(group){

	var arr = [];
	var gr = new GlideRecord("u_assessor");
	gr.addQuery("u_group", group);
	gr.query();
	while(gr.next()) {
		arr.push(gr.getValue('u_user'));
	}
	return 'sys_idIN' + arr.toString();
}

Regards
Ankur

[Ankur Bawiskar]

@Max Lin 

I have shared the approach above.

Regards
Ankur