Advanced Reference Qualifier on Record Producer

TCole94 · ‎12-22-2022

I am currently trying to create an advanced reference qualifier on record producer to have a dynamic user list show up when a certain record is referenced on the form.

When an event (record) is referenced on the form, each record contains a list of users. On an event record, there is a list type field (referenced to sys_user table) that contains users. (field name is u_poi)

The ask is only show the list of users for the referenced record on the form.

Picture below:

For example:

1. If the event contains multiple users, the person of interest reference should only show the list of users on the event record.

I have tried creating a script include but no luck as of yet.

TCole94 · ‎01-05-2023

I figured out a solution. I created a script include and used the event record sysID as a parameter. Below is my script that worked:

getQuery: function(event) {

var gr = new GlideRecord("u_metric_example");
gr.get(event);

var users = gr.watch_list.toString().split(",");

var q = new GlideRecord("sys_user");
q.addQuery("sys_id", users);
q.query();

var filter = q.getEncodedQuery();

return filter;

},

View solution in original post

Prateek kumar · ‎12-22-2022

Can you post your script include here

Please mark my response as correct and helpful if it helped solved your question.
-Thanks

TCole94 · ‎01-05-2023

For testing purposes, I am now use the OOB watchlist field to get the list of users for the referenced "Event" field above.

My current code for the reference qualifier for the person of interest field is below:

javascript&colon;'sys_idIN='+current.variables.int_event.watch_list;

^the above code semi-works because it does dynamically filter the list of users on the watchlist for the referenced event HOWEVER, it will not pick up the first user in the watchlist.

For example,

A watchlist has 3 users: George Washington, Abraham Lincoln, Thomas Jefferson for an event. On the form, when I select the event record, only George Washing and Thomas Jefferson appear. I've provided a picture below:

Any reason why it is not returning all members?

Thank you.

benn23 · ‎01-05-2023

Curious, if you open up that event and look at its XML, what's displayed in the watch list node? What happens if you copy that node (list of sys_id's), then open up a sys_user filter and use 'sys_id is one of' then paste what you've copied. Does it return all 3?

TCole94 · ‎01-05-2023

The watchlist node contains the 3 sysIDs (below):

"<watch_list>a8f98bb0eb32010045e1a5115206fe3a,67484bb0eb32010045e1a5115206fee6,2428c7f0eb32010045e1a5115206feb8<watch_list>

When I applied that query condition as a simple reference qualifier, all 3 users appeared.

Then I applied that filter in the advanced query below and all three appeared! So that worked.

CODE:

javascript;'sys_idINa8f98bb0eb32010045e1a5115206fe3a,67484bb0eb32010045e1a5115206fee6,2428c7f0eb32010045e1a5115206feb8='+current.variables.parent.watch_list;

Seems like a sys_id is or the first index of the list array is not being picked up which is odd.