Reference Qualifier based on previous field

gm01 · ‎03-06-2017

I am trying to figure out how to create an encoded query string for a reference qualifier or see if I have to possibly script this. I have a request form which the first variable which is a lookup select references the sys_user table based on the user_name and pulls a custom field of test_uuid as the lookup value field. The name of that variable is vm_user. Now the next selection is a variable called test_proj which references another table that has values (in a column called proj) associated with that value of test_uuid. I want to make sure that when the user is selected that only the values in the table that match test_uuid are returned. Can this be done in a encoded query where I have the reference lookup the values for proj and only show proj values that match the test_uuid value or will this require a script include?

Erik Stolberg · ‎03-07-2017

I'm using it on a Lookup Select Box so it should. All you are doing is creating a filter on your data and using another defined variable to give you the value.

Make sure to check that on the Type Specifications tab of your 'vm_user' variable-- your lookup value field must return the sys_id.

Small Example:

I have on my form a field called Building and another field called Floor. Floor is dependent on Building.

Building => reference to a table called u_building

Floor => reference to a table called u_floor (this table has a reference field called u_bldg that is a reference to the u_building table)

If I want to generate my Reference qualifier, I can view the Floor table records, build a sample filter with a random building, then right-click and grab the query:

u_bldg=57d1a36e4f403240c7567fe24210c719

My Catalog Item Variable for Building (u_bldg_name😞

Lookup from table = u_building

Lookup value field = Sys ID

Lookup label field(s) = u_name

Reference qual = u_active=true

My Catalog Item Variable for Floor (u_name):

Lookup from table = u_floor

Lookup value field = Sys ID

Lookup label field(s) = u_name

Reference qual = javascript:'u_bldg='+current.variables.u_bldg_name; <<matches my prior filter query format

View solution in original post

Lisa Silvaroli · ‎06-09-2017

Works!!! Thank you so very much!

Chris Richie · ‎02-10-2022

Digging this one up from awhile ago. I'm trying to achieve something similar. Looking to produce a simple form for a hardware refresh.

I want variable #2 to show me the assets assigned to the employee from Variable 1.

Here is my screenshot of the form.

Based on the accepted solution, I tried to replicate it, but getting Null as the result.

Erik Stolberg · ‎02-10-2022

Chris, looks like you're trying to populate the second variable based on the first on your catalog item. I am assuming the 'Employee Name' variable is a reference field, where the selected value will be a sys_id.

If the pictured variable refers to alm_hardware table, to correctly query that table (independent of the catalog item element) would look like this:

Assigned to = Joe Employee

This would give you an encoded query of something like
assigned_to=0e792b381451055044a3dc06b04bcbb5

What you are trying to accomplish with the Reference qualifier field is to apply this filter in a dynamic way, incorporating the other variable.

Let's break down the parts of that req qual field...

javascript:
This is included to tell ServiceNow we want to evaluate this (not a static value)

'assigned_to='
This is the static part of the query

+ current.variables.employee_name;
This is the dynamic part, where you reference the variable. The last part of it needs to match the NAME of the prior variable, as well as the TYPE. If the variable has a value of "Joe Employee" instead of the sys_id, you would need to change the static part of this to 'assigned_to.name=' to match the data value.

Putting this together (and what should be in your reference qualifier field):
javascript: 'assigned_to='+current.variables.employee_name;

Erik Stolberg · ‎03-01-2022

@Chris Richie Were you able to update your variable and get this to work using the prior info?

apurva3 · ‎07-27-2022

thanks! it help much understand the logic.