Using the Choice Lists Table for Lookup Select Box

[ccajohnson]

Overview

Many times you may have a variable that also has as choices from a table. Instead of re-creating all of the choices in the variable and having to maintain them in two places, you can use a variable type of Lookup Select Box.

Single Choice Solution:

For this solution, I will create a Lookup Select Box that points to the Operating system field from the Computer [cmdb_ci_computer] table. This allows for maintenance to occur in one place rather than in the field and the variable.

Variable definition

Lookup Select Box [lookup_select_box]

Name: lookup_select_box

Type: Lookup Select Box

Order: 100

Question: Lookup Select Box

Lookup from table: Choice [sys_choice]

Lookup value field: Value

Lookup label field(s): label

Reference qualifier: name=cmdb_ci_computer^element=os^inactive=false^ORDERBYsequence

Reference qualifier field explanation

The Reference qualifier field is where you define what table, which field, if the menu option is active, and the sort. Here is the explanation of what is used in this field:

name=cmdb_ci_computer

The name field on the Choice table corresponds to the table the choice field is found.

^element=os

The element field on the Choice table corresponds to the field name of the choice field.

^inactive=false

This allows you to sort out those choice records that are not listed (inactive).

^ORDERBYsequence

This allows you to sort the list using the same order that the choice has.

Dependent Choice Solution

For this solution, I have a Record Producer that points to the Incident table. The ask is to have both a Category and Subcategory menu appear on the form and have those menus behave the same way that they do on an Incident record.

Variable definitions

Category [category]

Name: category

Type: Lookup Select Box

Order: 100

Question: Category

Lookup from table: Incident [incident]

Lookup value field: Category

Lookup label field(s):

Include none: true

Unique values only: true

Reference qualifier:

Subcategory [subcategory]

Name: subcategory

Type: Lookup Select Box

Order: 110

Question: Subcategory

Lookup from table: Choice [sys_choice]

Lookup value field: Value

Lookup label field(s): label

Reference qualifier: javascript:'name=incident^element=subcategory^inactive=false^dependent_value=' + current.variables.category + '^ORDERBYsequence'

Variable attributes: ref_qual_elements=category

Reference qualifier field explanation

The Reference qualifier field is where you define what table, which field, if the menu option is active, the dependent value, and the sort. Here is the explanation of what is used in this field:

javascript:'

This will allow the Reference qualifier to be dynamic.

name=incident

The name field on the Choice table corresponds to the table the choice field is found.

^element=subcategory

The element field on the Choice table corresponds to the field name of the choice field.

^inactive=false

This allows you to sort out those choice records that are not listed (inactive).

^dependent_value=' + current.variables.category + '

This will include the value selected from the category variable.

^ORDERBYsequence'

This allows you to sort the list using the same order that the choice has.

Variable attributes field explanation

Using the attribute "ref_qual_elements=category" will allow the Lookup select box to use the category variable in the reference qualifier. If this is left out, the menu will not be populated.

Custom Dependent Choice Solution

For this solution, the ask is to have both a Category and Subcategory menu appear on the form and have custom values for those menus.

Create Choice records

Since we are leveraging the Choice table, we need to create the choices on that table. Since the system only allows you to select the table the choice is related to, we will choose the Question Choice [question_choice] table. For this solution, I am creating three categories and six subcategories:

Table	Element	Value	Label	Dependent value	Inactive
question_choice	category	category_1	Category 1		false
question_choice	category	category_2	Category 2		false
question_choice	category	category_3	Category 3		false
question_choice	subcategory	subcategory_1a	Subategory 1a	category_1	false
question_choice	subcategory	subcategory_2a	Subategory 2a	category_2	false
question_choice	subcategory	subcategory_2b	Subategory 2b	category_2	false
question_choice	subcategory	subcategory_3a	Subategory 3a	category_3	false
question_choice	subcategory	subcategory_3b	Subategory 3b	category_3	false
question_choice	subcategory	subcategory_3c	Subategory 3c	category_3	false

Variable definitions

Category [category]

Name: category

Type: Lookup Select Box

Order: 100

Question: Category

Lookup from table: Choice [sys_choice]

Lookup value field: Value

Lookup label field(s): label

Include none: true

Unique values only: true

Reference qualifier: name=question_choice^element=subcategory^inactive=false^ORDERBYsequence

Reference qualifier field explanation

The Reference qualifier field is where you define what table, which field, if the menu option is active, and the sort. Even though other examples do not use this field, we define a reference qualifier to narrow the choices to the ones we created above. Here is the explanation of what is used in this field:

name=question_choice

This points to the name field in the Choice table which corresponds to the Question Choice table where we defined the choices are found.

^element=subcategory

The element field on the Choice table corresponds to the field name of the choice field.

^inactive=false

This allows you to not show those choices that are not listed.

^ORDERBYsequence'

This allows you to sort the list using the same order that the choice has.

Subcategory [subcategory]

Name: subcategory

Type: Lookup Select Box

Order: 110

Question: Subcategory

Lookup from table: Choice [sys_choice]

Lookup value field: Value

Lookup label field(s): label

Reference qualifier: javascript:'name=question_choice^element=subcategory^inactive=false^dependent_value=' + current.variables.category + '^ORDERBYsequence'

Variable attributes: ref_qual_elements=category

Reference qualifier field explanation

The Reference qualifier field is where you define what table, which field, if the menu option is active, the dependent value, and the sort. Here is the explanation of what is used in this field:

javascript:'

This will allow the Reference qualifier to be dynamic.

name=question_choice

This points to the name field in the Choice table which corresponds to the table the choices are found.

^element=subcategory

This points to the element field in the Choice table which corresponds to the name of the choice field.

^inactive=false

This allows you to not show those choices that are not listed.

^dependent_value=' + current.variables.category + '

This will include the value selected from the category variable.

^ORDERBYsequence'

This allows you to sort the list using the same order that the choice has.

Variable attributes field explanation

Using the attribute "ref_qual_elements=category" will allow the Lookup select box to use the category variable in the reference qualifier. If this is left out, the menu will not be populated.

[Mike McCall]

This is a great write-up! I have one question: have you noticed the "ORDERBY" no longer being honored in certain versions?

I swear I've used this successfully in the past, but I'm not seeing it work in the service portal right now. (We have instances on both Rome and San Diego--neither of which are working.)

NOTE: I'm working with a dependent drop-down, and I do see a KB article saying it shouldn't work (KB0695435)...but I really thought it used to!

[ccajohnson]

According to the workaround in the Knowledge article it indicates to use the attribute ref_ac_order_by. See what happens when you have the attribute defined as: ref_ac_order_by=sequence instead of defining it in the reference qualifier. If it works, let me know and I will change this page to reflect it.

[Mike McCall]

Unfortunately, I read that as the article pushing us to use a Reference-type variable (instead of the Lookup Select Box).

Just to see, I tried adding exactly "ref_ac_order_by=sequence" in the otherwise-empty Variable Attributes field. The options still default to alphabetical in the portal. 😞

[kemmy1]

wouldn't the ref_qual on the category variable be:

Variable definitions

 name=question_choice^element=subcategory category^inactive=false^ORDERBYsequence

[Brian Hofmeiste]

Hi Kilo!

I'm using this setup and experiencing a little trouble.   The select lookup box is only showing the value applied to the current user and not all the contents of that table column.  Do you know why this happens?

Here is a link to my issue along with pictures.  https://www.servicenow.com/community/developer-forum/lookup-select-box-does-not-return-all-items/td-...

[Calbanw]

This was great. That reference qualifier was spot on for what I needed. Thank you so much. 

[pavan_yakkala1]

Hi Everyone,

we have similar requirement where to add dependent questions but ORDERBY is not working either with Reference Qualifier ORDERBY or ref_ac_order_by in Attributes. Can you please let us know if you find any solution for Lookup Select box ordering of values?