Building hierarchical tables

Chapter 1, ServiceNow Foundations,  introduced the foundations of ServiceNow. One of the most fundamental parts of an application is how it stores its data, and we saw how virtually everything you see and do in the platform is stored in the database.

Specifically, ServiceNow is built on a relational database. Instances hosted by ServiceNow use MySQL, a popular open source database that is robust, well featured, and scalable. These kinds of relational databases are relatively simple to understand, which is one of the reasons they are most commonly used: data is held in tables and columns, and relationships may exist between rows.

Tip

The ServiceNow platform can run on almost any relational database, such as Oracle or SQL Server. But supporting different architectures is difficult, so it is not a standard offering.

Benefiting from an object-oriented design

The simplicity of a relational database means that, on its own, it does not easily represent the data structures used in modern object-oriented programming languages. One particularly useful function of an object-oriented approach is inheritance.

Tip

Inheritance allows one object to build on the functionality of another. Why duplicate effort when you can reuse existing capability?

In ServiceNow, a table can inherit another. The parent table defines the base functionality, while the child table, built on top, can continue to use it. That means that any fields you add to the base table are automatically available to the child as well. In fact, almost all functionality you add to the base table is available to the child.

Note

Inheritance is another solution to allowing facilities to use HR's Employee Profile table, as mentioned previously. It means that there are two separate tables, but facilities would benefit from all the work that HR did.

In our hotel application, we want to store information about our guests. We need to know their names, their telephone numbers, and perhaps their addresses. ServiceNow has got a built-in table for storing people: the User table. But we want a special type of person: guest. Let's keep staff in the User table and guests in a new extension table.

Tip

The User table in ServiceNow defines who can log in and use the platform's functionality. Sometimes, you need a contact database, which stores information about people: their names, phone numbers, location, and who their manager might be. It's tempting to build the contact database as a separate table and keep the two separate, but I recommend using the User table as the basis for both. It saves the duplication of data and allows reuse of the special functionality that is built specifically for the built-in table.

Extending the User table

Let's extend the ServiceNow User table in order to have a special class for guests. We'll try to us the Studio as much as possible to see how it works.

First, we have to mark the User table as extendable. This needs to be done in the main interface, since the User table is in Global (keep the Studio window open-it's useful having both accessible).

1. Go to System Definition > Tables and find the User[sys_user] table. (Ensure you find the right one - the name is sys_user. When you enter the form, you should get a message at the top of your screen saying this record is not part of the hotel app. Click on the link to edit the record.

   Tip

   This means the change to the User table will not be recorded as part of the application. Alternatives to this (such as Update Sets) is Packaging applications is discussed in Chapter10, Packaging with Applications, Update Sets, and Upgrades.

2. Make the following change and save it once you're done:
   • Extensible: <ticked> (In the Controls tab)
3. Now, return to Studio, and choose Create New Application File. Choose Table, click Create and fill out the following data:
   • Label: Guest
   • Extends table: User
4. Click the Submit button to create the table.
5. If you look at the fields available in this new table, you'll see lots of fields already, besides the normal automatic five. These additional fields are those defined in the User table.

What does this mean? The Guest table has inherited the fields of the User table. I don't need to create new name, telephone, and e-mail fields-they are already available for use in the Guest table.

Indeed, when you create a table that inherits another, you gain all the functionality of the parent. Most of the scripts, rules, and policies of the parent automatically apply to the new table. But sometimes, you want to create the functionality only for the child table. To this end, ServiceNow lets you place it at the level you need.

Tip

We'll cover how scripts are handled in ServiceNow in the next chapter.

Interacting with hierarchical tables

Our new table is the right place for storing information about our valued customers. While useful fields have been inherited from the User table, it doesn't contain everything. Let's make a new field to store the membership number of our guests..

1. Click on the Create New Application File button, choose Table Column, click Create, then fill out the following fields and Save.:
   • Table: Guest
   • Type: String
   • Column label: Membership number
   • Max length: 40
2. Next, add it to the form. Click on the Create New Application File button, choose Forms & UI, Form then click Next. Select Guest under My Tables, and click Create.
3. Find our new field called Membership Number from the list on the left, drag it underneath the Last name field in the layout, and click on Save.
   
4. To test, let's create a new guest record. Switch to the standard interface, and navigate to Hotel > Guests. (You may need to refresh the page to see it.) Click New, use this data, and Save.
   • First name: Alice
   • Last name: Richards
   • Membership number: S2E1

Great! We can enter a membership number properly. And if we look at a standard User record, such as Fred Luddy (User Administration > Users), the Membership number field does not show up. That's because Fred is a user, not a guest.

Viewing hierarchical tables

You may have noticed that our new guest, Alice, showed up when you visit the User table. That's because Alice is both a user and a guest. A Guest record will be treated just like a User record, unless there is something specific that overrides that behavior. In our case, the only difference between a User and Guest record right now is that the latter has an extra field.

Note

If you want to impress your friends, explain that this behavior is called polymorphism. I think it's pretty cool. It lets you use the base or extended functionality as you need it.

But this gives rise to something that confuses many. If I look at the Guest table, I can add, through Personalize List, the Membership number field.

However, if I try to add a Membership number field to the User table, I can't. Why?

This is because a User record doesn't have a membership number; only a Guest record does. Think carefully about where you position fields to ensure they can be seen at the right level.

Extended fields are not available while dot-walking. The Membership number field would not be available when dot-walking through a User reference field.

Tip

The Allow base table lists to include extended table fields property in UI Properties changes this for the UI. Scripts can use a special syntax when dot-walking. This is mentioned in Chapter 3, Server-side Control, and Chapter 5, Getting Things Done with Tasks .

Overriding field properties

Inherited fields allow you to easily reuse functionality. However, sometimes, you want the fields in the extended table to work differently from the base table. This is accomplished with dictionary overrides.

For example, let's change the default time zone for new guests so that it's different from the User table's. The current default for Users is the system time zone, and Guests inherits this setting.

1. Navigate to the dictionary entry for the Time zone field. Accomplish this in the Studio by clicking on the Guest table entry at the top of the Application Explorer list. In the Columns tab, find the Time zone field, and click on it.
2. Once there, look for the Dictionary Overrides Related List. Click on New, use the following data, and Save.
   • Table: Guest [x_hotel_guest]
   • Override default value: <ticked>
   • Default value: Europe/London (or your own choice!)

   Now, when you create a new Guest record, it sets the default time zone to Europe/London. Any new User records will be unaffected.

Tip

You can also change field labels so that they are different for the base and extended tables. Navigate to System Definition > Language File and create a new entry, populating Table with the extended table name (such as x_hotel_guest). The Element field should be the field name.

Understanding the background behavior

You might be wondering how this all works. Let's have a look.

A child table is a normal database table. However, it does not recreate all the fields of the parent. Instead, the only columns in that new table are the new fields. For example, if I were to run the DESCRIBE x_hotel_guest SQL command on the database, I'd only see two fields: u_membership_number and sys_id.

So, when I look at Alice's record in the Guest table, the ServiceNow platform is actually joining the parent table and child table behind the scenes. ServiceNow takes the independent tables and (invisibly) joins them, creating the illusion of a single, bigger table.

Our friend, the sys_id field, enables the platform to do this. If you remember, the sys_id field uniquely identifies a record. In the case of an extended table, the sys_id field is actually stored in two places: the parent and child tables. The platform joins both together whenever you query the Guest table. The following image shows how this works:

When you mark a table as extendable, you are also adding a second system field: Class (sys_class_name). It contains the name of the table that the record represents. For example, the User record representing Fred would have sys_user in the Class field, while the User record for Alice would be u_guest. With this information, ServiceNow can join tables if necessary and present you with the appropriate data.

Note

There are actually two models for table extension: hierarchical and flat. The hierarchical method consists of multiple tables that are joined together as needed, as just described, while a flat structure consists of one very large table with all the columns of every table. When you make a new table and add a new field, in reality, it is simply adding another column to the base table. The platform again hides this from the user. The majority of the time this does not have an impact on how table extension works in ServiceNow and is purely undertaken for performance reasons. (The one occasion when this does matter is if you try to add more than 10 large string fields to a flattened table due to the MySQL row size limit. The Task table, discussed in Chapter 5, Getting Things Done with Tasks, suffers from this.)

The ServiceNow interface knows about this behavior. When you navigate to a record, ServiceNow will always show you its actual class. So, even if I am viewing a list of Users, when I click on Alice, I will see the Guest form, with all of the appropriate attributes.

Making it visual with the Schema Map

Sometimes it can be difficult to understand how this is structured. To help, use the schema map. It is really useful for visualizing what is going on.

1. In the standard interface, navigate to System Definition > Tables, and select the User [sys_user] table.
2. At the bottom of the form, in the Related Links section, click on Show Schema Map. For clarity, tick only the Show extended tables and Show extending tables checkboxes.
3. The following screenshot shows how the Guest table is related to the User table:

Tip

The product documentation has more information on the schema map: https://docs.servicenow.com/bundle/helsinki-servicenow-platform/page/administer/table-administration/concept/c_SchemaMapForTables.html.

Changing class

Once a record is stored in a particular table, a neat trick you can learn is moving it. If I decide that Alice is actually a user, I can alter the value of the Class field. The platform will drop any information specific to the Guest schema and start treating the record just like a User record. The Class field can be added to a form or list and is represented as a choice list. Often, you will want it to be read-only.

Tip

The ability to change the class is a powerful feature, and you should be aware of the consequences. It is unusual to reclassify a record, and that may throw off reporting; for example, if you counted 10 users and nine guests, and suddenly one switched, you might have an overbooking. If there is data in a column that is not on the new table, it is lost. Be careful!

So far, we've discussed how you can add fields into a specific class and seen how they are inherited. But this will work with much more than fields! As we work through the chapters, we'll see how functionality such as business rules, access control rules, and import sets all benefit from hierarchical tables.

Repointing the reference field

At the moment, there is a field on the Check-in table called Guest that is actually pointing to the user table. Now we have a dedicated place for our valued customers, lets change that reference field.

1. In Studio, select the Check-in table from the Application Explorer.
2. In the Table Columns list, edit the Guest reference from User to be Guest. Do this by double-clicking on the cell, changing the value, then clicking the green tick, as per the screenshot below.
   
3. While we are improving the Check-in table, let's tidy up the Check-in related list. As mentioned in Chapter 1, ServiceNow Foundations, it's better to keep long string fields off lists.
4. Click on Check-in under List Layouts in the Application Explorer. Remove comments from the selected column and click Save.