Reference fields

When designing the data structure for a hotel, you want to know which room a guest has checked in to. It won't be good for business if we don't know who is sleeping where! This is exactly what a reference field does: it creates a link between two records, one pointing to another.

When we examined the URLs earlier, we saw they contained two parts: the table and the sys_id value of the record. These are the two items needed to reference a record. So when you create a reference field, you need to select which table it should point to, which is stored in the dictionary. And the contents of the field will be a 32-character string. Sound familiar? Yep, you will be storing a sys_id in that field.

Reference fields are one of the most important items to understand in ServiceNow. The database sees a string field containing the sys_id value, a foreign key. However, this is meaningless to a person. Therefore, the platform allows you to pick a field that will be displayed. For a person, this might be their name. Other records might have a user-friendly reference number, like TSK0001. This is usually an incremental number-there are scripts that generate one automatically. You can choose which field to show by ticking the Display field in the Dictionary entry. But remember: only the sys_id value is important to the platform.

Reference fields are used throughout ServiceNow, just like a proper relational system should be. Scripting, lists, and forms all understand references fields, as we'll see while we work through the chapters.

Creating a reference field

Let's think about something that the hotel application needs-a room directory. Each room has several attributes that defines it: its room number, how many beds it has, and which floor it is on. We can represent this information as fields in a Room record, all stored in a dedicated table.

We also need to store guest information. When designing a data structure, it's a good idea to reuse functionality wherever possible. ServiceNow already has a table that stores information about people: the user table. Let's use that for now to store guest information.

Tip

It is sometimes a difficult decision to should reuse an existing table. The benefits are obvious: you save time from not duplicating work, both in configuration and maintenance. But sometimes, you need the table to work slightly differently for two different scenarios. We'll see one way to deal with this in Chapter 2, Developing Custom Applications.

Once we have these items in place, we can modify the Check-in table to record which room has been taken by which guest.

Building out the data structure

Let's create the tables and fields we need step by step. Let's start with the Room table first.

1. Return to System Definition > Tables to create another new table. Use the New button on the list. Fill out fields as follows, click on the menu button, and then click on Save (don't click on Submit!):
   • Label: Room
   • Add module to menu: Hotel
   

   Tip

   I suggest using the Save button, accessible via the three-line menu icon (or right clicking on the header) rather than using Submit to commit records to the database. This ensures that the updated record is shown on screen after saving rather than redirecting to the previous page, like Submit does.

2. Now create a field to store the room number by scrolling down the Columns tab to Insert a new Row. Double-click again to enter the information.
   • Column label: Number

Note

Don't use the auto-number option; create a new field using the related list.

We need another field to store the floor it is located on:

• Column label: Floor
• Type: Integer

The final result should look like this:

1. Use Save to make the changes in the database.
2. Let's see what we've created. When we created the table, ServiceNow also created a module in the Hotel application menu that will show us all the room data in the instance. Navigate to Hotel > Rooms. You shouldn't be surprised to see No records to display- we haven't created any yet!
3. Using the New button, create a few example records, giving each one a different number. Make several on the same floor. One of them could be Room 101 on the first floor. As you do so, try using the Save and Submit buttons, and notice the difference between them.

Note that the platform does not force you to choose different numbers for each record. Unless you mark a field as unique or create a rule to check, the platform will allow you to create them.

Tip

To mark a field as unique, you can edit the dictionary entry of that field (you will need to configure the dictionary form and add the Unique checkbox). By ticking that field, you are asking the database to enforce it. It does this by making that field a unique key. This has two impacts. It creates an index on that field, which is good. However, if a user attempts to save a duplicate value, they will get a message saying Unique Key violation detected by database. This can be a little jarring for a non-technical user. Try to catch the error with a Business Rule first.

Linking the data together

Now that we have a list of rooms, we need to create the link between the Room and Check-in records.

A reference field can be referred to as a one-to-many relationship. This is because a room may be checked in to multiple times (you might have one particular guest one day, and the next day, another might sleep in the same room after our fabulous cleaners have done their work), but for a single check-in, you can only select one room. You can only sleep in one bed at a time!

Tip

A classic example of a one-to-many relationship is between a mother and her children. A child can only have one biological mother, but a mother can have many children.

The following diagram shows the relationship needed between the Room and Check-in records:

1. Go back to System Definition > Tables and find the Check-in table. Once there, create two new reference fields-one will be for the room, using the following data:
   • Column label: Room
   • Type: Reference
   • Reference: Room

     The other will be for the guest:

   • Column label: Guest
   • Type: Reference
   • Reference: User [sys_user]-be careful to select the right one here!
2. Save your changes.
3. Now is a good time to rearrange the new fields on the form so that they look good. To do this, click on Design Form in the Related Links section near the bottom of the form. It'll open a new tab or window.
4. Once in the form designer, drag the section containing the Room and Guest fields above Comments. Then, click on Save, and close the tab. If you have trouble, try using Form Layout, and move Comments to the bottom of the list, so it is under Guest and Room. The form should end up looking like the next screenshot.
5. Now, create a few example Check-in records. To simulate someone going into room 101, create a new entry in the Check-in table by navigating to Hotel > Check-in, and clicking New. Populate both the Guest and Room fields, and then click on Submit:

Looking from different perspectives

You can view the relationship between Room and Check-in entities from both directions. If you are on the Check-in form, you can see which room is in use through the reference field. The reference icon is very useful for viewing more details about the record-just hover over it.

Tip

If you hold down the Shift key while you move your cursor over the reference icon and then go into the pop-up window, the information will remain until you click on the Close button. This is quite useful when copying data from that record without losing position.

You can easily view the relationship from the other perspective, too. When you create a reference field, you can add a related list to the form, or a list of the referenced tables. This will let you see all the records that are pointing to it.

1. Navigate to Hotel > Rooms, and select a room that you have created a check-in record for. (I made one for room 101 for David Loo, as above.) On the Room form, click on the menu button (or right-click on the header bar), and then go to Configure > Related Lists. The one we want will be named in this format: table->field-in our case, it's Check-in > Room. Add that to Selected list, and click Save.
   
2. To make things look better, right-click on the list headings, go to Configure > List Layout, and remove Comments. Click Save.

The related list gives you a New button. When you click on it, you will see the Check-in form but with the reference field already filled in. So, if we know which room we want to check a guest in to, we can navigate to the Room record, click on the New button in the Check-in related list, and need not type in the room number again.

Using reference qualifiers

By default, a reference field can select from any record in the referenced table. However, often, you want to filter the results. For example, you may want to specify a guest as inactive, perhaps representing someone who won't be visiting Gardiner Hotels any longer. Therefore, let's filter out inactive users so they cannot be inadvertently checked in.

Reference qualifiers allow you to do this. When you edit the dictionary entry of a reference field, you can specify the filter you want to apply. These can be specified in three different ways:

• Simple: This lets you specify which records should be returned using a condition builder.

  Tip

  Simple is the default reference qualifier. Click on Advanced view in Related Links to see the other options.

• Dynamic: This lets you pick from prebuilt scripts. The choices they provide often differ depending on the context of the record or the session. A good example is Me, one of the dynamic filter options. This will return whoever is currently logged in, meaning that users who use the reference field will have personalized results. A dynamic filter option is the most reusable

  Tip

  You can build your own dynamic filter options by navigating to System Definition > Dynamic Filter Options. This will be covered in Chapter 2, Developing Custom Applications .

• Advanced: This is the original way of creating reference qualifiers. It accepts an encoded query. JavaScript can be embedded in these queries, by prefixing them with javascript.

  Tip

  An encoded query is a field-operator-value triplet, separated by the caret (^) symbol. This string represents part of the where clause of the resulting SQL query. For example, active=true specifies all records where the active field is true, while active=true^last_name=Smith represents active being ticked and the contents of the last_name field being Smith.

  One easy way to obtain an encoded query is to build a filter in the list view, right-click on the result, and choose Copy Query.

For our Hotel application, let's use a simple reference qualifier. Navigate to Hotel > Check-In, and select a record. Right-click on the Guest field label, and choose Configure Dictionary. Fill out the fields as below, and Save.

• Reference qual condition: Active - is - true.

Now, if you mark a user as inactive (by unchecking the Active checkbox and saving), they cannot be selected when checking in, neither through the magnifying-glass lookup window, nor using the type-ahead functionality.

Dot-walking through data

Dot-walking is a very important concept in ServiceNow. It means you can access information through reference fields quickly and easily. It can be leveraged throughout ServiceNow-both through the interface and through code.

You've already used dot-walking. When you hover over the reference icon, you can see information from that record. That's the whole concept! We are using the platform's capability to "see through" reference fields and pull out information from that record. And, as we'll see in Chapter 3, Server-Side Control the same is possible through code.

Using derived fields

Dot-walking can be used throughout the user interface. Another example is adding derived fields to lists, forms, and queries. A derived field is a field from another record that is found through a reference field.

For example, we could add the floor number of the room to the check-in form as a derived field. The floor number doesn't belong to the check-in record, and if we change the room on the form, the system will dynamically change the floor number displayed.

Note

With scripting, you have the option to copy data through the reference field onto the record you are dealing with. That data then becomes part of the record. Derived fields will exist through the link only.

This concept is important to understand. If the referenced record gets deleted or changed, it will then affect our current record. For example, if we delete the room record, the check-in form won't be able to show which floor it is on. If we change the floor value on the room record, our check-in form will show the new value.

The simplest example of derived information is the display value, which was mentioned earlier. If the display value of the referenced record changes, you'll see it altered everywhere. Since sys_id is the primary key for a record, you can easily rename groups, alter the names of users, or update virtually any record without penalty.

1. Navigate to the Check-in form (Hotel > Check-in, then choose a record) . Use the menu icon and choose Configure > Form Layout.

   Tip

   Derived fields can only be added via Form Layout (rather than Form Design)

   Even though the Room field has already been added to the form, it is still in the "available" list. It should have [+] as a suffix to the field name, showing it is a reference field that can be dot-walked to, for example, Room [+].

2. On selecting it and clicking on the Expand selected reference field icon, you get to see the fields in the Room table. Choose the Floor field, and add it to the form. It should be labeled Room.Floor, showing that you are dot-walking. Click on Save.

   The Check-in form should now have several fields in it: the Room reference field, the Floor derived field, and a simple Comments field. Here they are:

   

Tip

Notice how the Floor field is editable. If the value is changed and the Submit button is clicked on, it is actually the Room record that will be altered.