Defining custom relationships
Adding a Reference field on table A that points to table B, creates a relationship that ServiceNow can understand. This allows you to add a related list on records in table B, that displays the records from table A that are linked to it.
Creating a M2M table via the sys_m2m relationship definition table also creates a relationship that ServiceNow can understand. Because of this, you can add a related list on either of the two tables linked by the relationship, to show related records from the other table.
If you want to define a more complex relationship, however, ServiceNow allows you to do that as well! Let's explore an example of defining a custom relationship for tickets generated from.
Requests have a sort of task-hierarchy. The Request[sc_request] record is the parent of one of more Requested Items [sc_req_item] (commonly called RITMs), which may be the parent of one or more Catalog Tasks [sc_task], not to be confused with the base system Task [task] table. Work is usually logged and done at the bottom of this relationship hierarchy, on the Catalog Task [sc_task] table records, but the person who the request was for, is stored on the Request [sc_request] table, in a field called Requested for [requested_for].
When I say parent, I mean parent in the parent-child relationship sense; they don't actually use the Parent field for this relationship.
So if you want to be able to see Catalog Tasks associated with requests that were requested for a given user, in a related list on that user's record in the sys_user table, there would be no built-in relationship that ServiceNow can understand, that you could leverage. Instead, you would need to define a custom relationship by following these steps:
- In the application navigator, navigate to System Definition | Relationships. This is a table containing defined relationships that link any two tables. At the top-left, click on New.
- Give this relationship a name that you'll recognize. Since this will be the relationship between users, and the Catalog Tasks under requests that were requested for those users, let's call it Tasks Requested for User.
- The Applies to table field is where you select the table on which the related list should be able to be shown. For our use case, select the User [sys_user] table. The parent object in the query with script after corresponds to records in this table.
- The Queries from table field is for the table from which the items in the related list should be shown. Since our related list is going to contain Catalog Tasks, set this field to the Catalog Task [sc_task] table. The current object in the query with script after corresponds to records in this table.
- Once the rest of this form is filled out, we need to define the script to use for the query. We'll be covering
GlideRecord
query scripts in much more detail in a later chapter, but for now just copy the code as shown following, and then we'll go over what it's doing.
Write the following code inside the refineQuery(current, parent { }block:
var parentSid = parent.getValue('sys_id');
current.addQuery('request.requested_for', parentSid);
On the first line (which, in your editor, will be either line 2 or line 3), we are creating a variable called parentSid (which stands for parent Sys ID) which will contain... the parent record's sys_id. The parent object corresponds to the record in the sys_user table that the related list will be shown on, so another way of describing parentSid, would be to say that it contains the Sys ID of the record which is currently open, and on which the related list is displayed.
On the second line, we use a method of the GlideRecord class called addQuery(). This method is explained in great detail in a later chapter but for now, just understand that we are adding a query filter on the table that will be shown in the related list. By passing in two arguments, we're telling it that the first argument (a field name) should be equal to the second argument's value (the sys_ID of the parent that we acquired on the previous line). In the first argument, you might have noticed that we also used dot-walking, which we learned about in a previous chapter.
This query effectively states: Ensure that the requested_for field on the record in the request Reference field, is equal to the sys_id of the record that this related list is displayed on.
Now right-click on the header, and click Save. The final form should look something like this:
Now we need to test to make sure that our related list works as we expect. To do so, we're going to need to generate some Catalog Tasks. In the application navigator, go to Self-Service - Service Catalog, click on Services, and select a simple catalog item. I recommend using the New Email Account catalog item. Fill out the request form, and click Order Now on the right. Then, click Continue Shopping and repeat the process once more, so you'll have two separate requests, each with one requested item, and one Catalog Task.
Next, navigate to the sc_task table, using the .list shortcut, and find the two tasks you've just created. Write down the task numbers, and open one of them up by clicking on the task number. On the right, you should see a Requested for field. This field should contain your user account (System Administrator is the default name). Change the value of this field to some other user, such as Abel Tuter, and Save the record.
The Requested for field is not actually on the sc_task table; it's a derived field. This means that it actually exists on another table, linked via one of the Reference fields on the sc_task table. To see that relationship, right-click on the label Requested for, and click on Show - 'requested_for'. A small dialog will appear, and you'll see Dictionary Info on the top line. That should show the field name, but the field name is dot-walked to: sc_task.request_item.request.requested_for. That means that this field is actually dot-walked two layers deep-It's the value of the requested_for field of the request in the Request field of the requested Item in the request_item field of the task, rather than just on the task itself! If it were on the task itself, it would simply say sc_task.requested_for.
Now, navigate to your own user profile by clicking on your name at the top-right of your screen, and choosing Profile. (Note that getting to your profile using this link automatically directs you to the Self Service view):
On your profile page, right-click in the header, and go to Configure - Related Lists. On the next page, select Tasks Requested for User on the left, click the arrow facing right to move it to the bucket on the right side. If you wanted to make this related list appear on a view other than the self service view, you could change the View name field. Click Save.
When you have a huge list of items in a drop-down, or slush-bucket field, you can usually get to the one you're looking for by selecting one value, then quickly typing the name of the value you're looking for. For example, if you're looking for a value like Tasks Requested for User, select any random value in the list, and type tasks requested, and it'll usually take you right there.
You should now see the catalog task(s) that had System Administrator in the Requested for derived field, but not the one for which you changed the Requested for field to Abel Tuter. Let's also check Abel Tuter's profile, to make sure the related list there shows that Catalog Task.
Navigate to the sys_user table using the .list shortcut from the application navigator filter text box again, then find and click on Abel Tuter's user profile. You will probably still be in the Self Service view, so your related list should still show up. If it does, you should see the task you set to be requested for Abel Tuter. If not, right-click at the top, and go to View - Self Service.