how to setup reference qualifier with reference to field value of current record

[ShishirG]

I am trying to setup a reference qualifier to show values in reference field based on some other field value.

e.g. There's a table called "ROOM" with field name as room_name and room_leader

                  room_leader field is a reference type field for table name called "EMPLOYEE"

                  Employee table as field name called "room"

        So when I try to choose a room_leader, it is showing me all the employees currently. I want it to show only those employees who belong to the same room for which I am trying to choose a leader.

Sample Data EMPLOYEE table:-

Employee                   Room

Sam                                       Reception

Dave                                       Reception

Paul                                       Year1

Kirsty                                       Year1

Jane                                       Year2

Nigel                                       Year2

Sample data ROOM table:-

Room_name                   Room_leader (referenced to Employee table Room field above)

Reception

Year1                                       Should show only Sam and Dave

Year2                                       Should show only Paul and Kirsty

Year3                                       Should show only Jane and Nigel

Year4                                       Shouldn't show any names

I have tried creating dynamic filter option as current.room_name but no good.

Also, I tried to setup Advance reference qualifier on 'Room_leader' field of 'ROOM' table to name=javascript:current.room_name and again no good.

I am sure this has to be something simple which I am not getting.

Thanks for your help.

Regards,

Shishir Gupta.

[mayurt]

check the below link :

https://www.servicenowguru.com/scripting/script-includes-scripting/advanced-reference-qualifier-scri...

Section 4 of this has good example http://wiki.servicenow.com/index.php?title=Reference_Qualifiers#gsc.tab=0

[ShishirG]

Thanks for the link and I have already gone through this info in docs and wiki.

Though I can write a script include for the same but I don't believe that the correct way.. given that SN gives so many powerful point and click features, I think there is something fairly easily I should be able to achieve this.

Requirement is as simple as a filter room=current.room_name   i.e. show records from employee table whose room matches with current records' room_name.

Do I really need to write a script include for this?

[Chuck Tomasi]

Hi Shishir,

You do not need a script include for such a simple filter. You can use this in the Ref Qual field after choosing Advanced for your reference qualifier. Sadly, there is not a way to reference the current record from the condition builder on simple reference qualifiers.

javascript:'room=' + current.room_name

The end result is that it be an encoded query string.

[ShishirG · Accepted solution]

Thanks Chuck,

That solved my issue.. with a little tweak as below: -

javascript:'room=' + current.sys_id

I need to replace room_name to sys_id as the Employee table is storing sys_id of the Room instead of name.

Though it may be better to have some dynamic fields and current record available in simple filter in new release to make it more intuitive.

Cheers,

Shishir Gupta