How to filter reference user field based on another field value

[cgedney]

I have been asked to filter a reference (sys_user) to only show our SVPs or "SVPs and VPs" based on a SelectBox value. Say the SelectBox has values a, b, c. If the user picks a, they want only SVPs to show up in the reference field. If they pick b or c, they want SVPs and VPs to show up. Do I need to clear out the values in the reference field and then re-build them and somehow load that list into the reference field? Never done this before, so trying to think how this works.

I created a script include to query the cmn_department table and grab all the users in our smt column and dept_head column. Then I got the unique values and returned that back. In the catalog client script, I used ajax to get the list from the script include. Only piece I don't understand is how to "re-populate" the reference field.

Thank you, Charles

[Ankur Bawiskar · Accepted solution]

@cgedney 

is SVPs and VPs names of department for those users?

If yes then simply use advanced ref qualifier as this

javascript: var query; if(current.variables.selectBoxVariable == 'a') query = 'department.name=SVPs'; else if(current.variables.selectBoxVariable == 'b' || current.variables.selectBoxVariable == 'c') query = 'department.name=VPs'; query; 

share your complete script include and advanced ref qualifier

If my response helped please mark it correct and close the thread so that it benefits future readers.