Hi
"How can I make one Reference Field depending on the value of another (Reference) field?"
As I read this question many times in the Community, I decided to create a short article about step-by-step instructions for implementation of an example.
Scenario:
In this example (see the solution in action in the below screenshot), I want to define two fields on a custom table, where a user can capture a reference to a user in the "Ref to User" field (1), and THEN, the available Roles that can be captured in the second (depending) reference field "Ref to Roles" (2), are limited based on the value of the "Ref to User" field.
In particular, if the user "Beth Anglin" is selected in the "Ref to User" field, the only valid Roles to be captured in the field "Ref to Roles" should be those Roles, that are assigned to "Beth Anglin" (in the example).
By clicking the reference button (3) to open the list of valid reference values, the list also should only the limited list of records, being valid to capture. The shown list will be filtered to all records assigned to the User (5) "Beth Anglin", and only those Roles (4) are selectable.
Steps to implement:
1.) Check the environment for the solution
- we have the "User" [sys_user] table, which holds a list of all users in the system.
- we have the "User Roles" [sys_user_has_role] table, which is an m2m table assigning Roles to users.
- if we want to get a list of Roles per User, we can filter the [sys_user_has_role] table on the "User" column
2.) Create the fields on your custom table.
I created a new custom table, with only TWO fields on it, just for demonstration purposes (see screenshot below):
The first custom field "Ref to User" (1) is a reference field to the "User" [sys_user] table and the second custom field "Ref to Roles" (2) is a reference field to the "User Role" [sys_user_has_role] table.
Now, the magic in making the field "Ref to Roles" depending on another field, which is the "Ref to User" field in my example, I need to add a Reference Qualifier to the "Ref to Roles" field. The Reference Qualifier will control and filter the records, which will be available in the Reference List as valid choices.
3.) Implement the (Advanced) Reference Qualifier
Opening the field definition (Dictionary record) for the "Ref to Roles" field" (see screenshot below), you can find the referenced table on the TAB "Reference Specification" (1) in the field "Reference" (2). In the "Default" view of this form, you can see a Condition Builder (3) to define fixed filter conditions for valid records in the reference field.
This is a great feature. For our use case, we need to be a bit more advanced in showing and allowing only valid reference values.
Each time, a different user is selected in the "Ref to User" field, the allowed and valid entries in the reference list for "Ref to Roles" may change (depending on the records in the [sys_user_has_role] table.
To add more advanced options for the Reference Qualifier, switch the View of the "Dictionary Entry" to the "Advanced" View (see screenshot below).
This will change the layout of the "Reference Specification" tab, showing up more fields for configuration (see screenshot below). I already changed the "Use reference qualifier" field value to "Advanced" (1), which shows the field "Reference qual" (2)
Now, the magic part happens:
Prefixing the content of the "Reference qual" field with "javascript:" allows to append a JavaScript expression to evaluate the conditions for filtering the shown records in the Reference List / Field.
I use the following statement to define the dependency between the "Ref to User" field on my custom table (which has the field name of [u_ref_to_user]) and the table referenced (which is the "User Role" [sys_user_has_role] table).
javascript:"user=" + current.u_ref_to_userThe field name "user" is from the referenced table and "current" always refers to the current record (which is the current record of my custom table in this case).
This reference qualifier condition now makes sure, that the records on the referenced table ([sys_user_has_role] will be filtered on the field [user] for the value, which is "current"ly stored in the [u_ref_to_user] field.
4.) Show the result in action
As shown in the introduction (see the first screenshot above), the reference list now only shows records for the currently selected user of the record.
When you first open up the reference list, it will not contain meaningful columns. To improve this, I changed the layout of the List by clicking the small hamburger menu left to one of the column headers (see screenshot below) and select the menu option "Configure/List Layout".
I configured the fields "Role", "User" and "Created" to be shown in the List (like you can see above).
As I chose this example by intention, there is now something looking uncomfortable with the outcome.
The value, which is shown up in the reference field "Ref to Roles" (1) gives no good information about what it will mean.
It just displays the "Created" date value (2), which is some sort of meaningless to the end-user (see screenshot below). For my example, it would be much more useful to show the Role name "template editor" (3) as a string in the "Ref to Roles" field (1) on my custom table.
What is happening, and why is this showing up in this way?
5.) Review the Display Value of a referenced record
Looking at the following page on the ServiceNow docs online site, you can find out, how the system behaves in finding the value to be shown for referenced values.
The following screenshot is from this page and lists the order, the system searches for the value to be displayed:
Because what we can see as the referenced value, is the "Created on" field, there seems to be NO definition for a display value on the referenced table ([sys_user_has_role]).
Let's have a look at the [sys_user_has_role] table:
As you can see from the screenshot above, there is NO column of the table marked as "Display" = "true" (2), and there is also NO column with the name "name" or "u_name" (3) in this table. Furthermore, the table is not a child table of any other table (1), to inherit a display value from a base table.
Following the rules, on knowing which value will be shown in the reference field, it must be the "Created on" field - which in fact is showing up in the "Ref to Roles" field in my custom table. Good - understood.
6.) Defining the displayed value for fields, referencing [sys_user_has_role]
Before implementing the solution, we should think first!
For my use case, I want to show the Role in the reference field (of my custom table), so I could just define the field "Role" [role] as "Display" = "true", and I would (maybe think/hope to) be happy (for the first moment).
BUT:
a) This will change an OOB field definition, which I (normally) do not want to do.
Changing OOB definitions will prevent them to be changed by future ServiceNow Release updates.
b) Showing the "Role" as the Reference may suit for my scenario now, but what, if later, I want to
reference the same table but intend to show the User field of that referenced record.
Let's even try out:
In the screenshot below, I configured the "Role" [role] field to be "Display" = "true":
Looking at what the custom table with the "Ref to Role" field now looks like, I may be happy for now (see next screenshot). The reference field now shows the name of the "Role" (1) instead of the "Created on" date.
7.) Defining an additional custom field to be used as the Display value
As mentioned above, if I would reference the same table from a different table in my instance, wanting the "User" field be shown up as the referenced value, this would not be possible without "destroying" and affecting my current implementation.
There can only be ONE field defined as "Display" = "true", so switching the "User" field to "Display" = "true" would automatically switch the "Role" field to "Display" = "false". That would result in showing up the "User" in the "Ref to Roles" field on my custom table, instead of showing the "Role" name - which is not the desired outcome.
So, think twice, before setting fields to "Display" = "true" on any table. It should always make sense, and not only for the first use-case found. As the table [sys_user_has_role] is an m2m table, there are two perspectives to look at this table. From the "User" and from the "Role" perspective.
So, let me define a new field on the table showing the Role AND the User (as a String), and set this field as "Display" = "true". This will not affect OOB fields and show both meaningful values when referenced.
To get there, I add a new field to the "User Role" [sys_user_has_role] (1) table (as you can see the definition of the field in the screenshot below).
I called the new field "Displayed as" (2) and set this field to "Read-only" (3). To use the new field as the "Displayed value" when used in a Reference, I checked the "Display" checkbox (4).
On the "Calculated Value" (5) Tab (which you can find on the "Advanced" View of the Dictionary form), I checked the "Calculated" (6) checkbox, which makes the "Calculation" field show up.
The returned value from this script will be the value to put into the field at runtime.
I just concatenated the Role and User name in one string (7).
Now, reviewing the values shown in the "Ref to Roles" field on my custom table, I can find the calculated string showing. Nice!
Thank you for reading, and I hope you enjoyed this article.
Please provide your feedback to this article in the comments below.
If you like it, just mark this article as helpful and bookmark it using the "Bookmark" button above for later access.
Have fun and built something on ServiceNow
Dirk
---------------------------------------------------------------------
If you like to also review my other articles on the ServiceNow Community, please have a look at the overview here:
NOTE: The content I provide here is based on my own experiences and does not necessarily represent my employer's views.
