Many-to-many relationships in ServiceNow
Many-to-many (M2M) relationships are similar to one-to-many relationships, except that they aren't usually dependent on just a foreign key column in a child table. Instead, many-to-many relationships in ServiceNow usually rely on an entire intermediary table called an M2M table. This table commonly has a PK (Sys ID) of its own for each record (of course), and otherwise primarily consists (other than the default System fields, like Updated and Created by) of two FK columns (reference fields).
As it happens, we've already seen one example of a many-to-many table: the Lou's Shoes Order table we defined earlier! This table creates a many-to-many relationship between the Customer and Item tables. This structure makes sense, because any one customer might order multiple different items, and any one item might be purchased by multiple different customers.
Luckily, in ServiceNow, there is an easy and pre-defined way to create M2M tables. Follow the following steps, to see how to define a new many-to-many relationship table, and add a related list to display that relationship on both related tables (as opposed to just the parent records).
Creating a M2M table
In the following demo, we'll create a many-to-many relationship between Problems and Virtual War Rooms. This functionality would allow us to link War Rooms, to the Problem record that's associated with the Major incident and any other issues that the War Room relates to.
- In the application navigator filter text bar, enter
sys_m2m.list, and press Enter. This will take you to theMany to Many Definitionstable. Even in your stock developer instance, you should see at least two or three many-to-many definitions. Note that these records are not the M2M tables themselves, they are simply the definition records for those tables. They define the many-to-many relationship; including, the names of the two tables that are linked.Note
This is a great example of using the
.listshortcut to navigate to a table that isn't otherwise accessible from the application navigator! It's also a great example of why it's important to remember a table's name, as well as its label. - At the top-left, click on New, to be taken to the New record form on the
sys_m2mtable. - In the From table field, we're going to choose our left-hand table. The left table is generally considered the primary in the M2M relationship, but this isn't important here. For our demo, select Virtual War Room [
u_virtual_war_room]. - In the To table field, select Problem [
problem] as the right table. The other fields on the form will auto-populate. - You may then want to edit the pre-filled values, such as the many to many table name. I'm going to call mine
u_war_room_problem.
- Finally, click on Create Many to Many at the top-right, or bottom-left of the form.
The result of creating this M2M table definition, is that a new table has been created with the name we specified (I used u_war_room_prob). This table's form will have two reference fields: One for the Problem, and one for the Virtual War Rooms. Thus, the Problem and Virtual War Room tables are now linked through this intermediary m2m table!
Normally when you have a reference field on one (child) table that points to a record in another (parent) table, you can add a related list on the parent table form in order to show all of a given record's child-records. However, in the case of a M2M table, the reference field isn't on the child record-it's on an intermediary record. Luckily, ServiceNow understands this M2M relationship as defined in the sys_m2m table, and makes it easy for us to display this relationship on both tables, using related lists. Here's how:
- Head on over to the
Problemtable. If you want to be slick about it, enterproblem.listinto the application navigator filter bar, and press Enter. - Open any problem record by either clicking on the display value, or clicking on the reference icon
. - Note that you may need to click Open Record after clicking on the reference icon, if you're on List v3.
Note
The view that shows up when hovering over a reference icon is called the
sys_popupview. By default, the default view is shown in the popup, but you can customize the fields that show here, by creating a new view for a given table with that name (sys_popup). - In the Problem record, right-click on the header and go to Configure | Related Lists:
- In the corresponding list, select the related list called simply Virtual War Rooms, and either double-click it, or click the right-arrow between the two list buckets:
- Click Save, and you should be returned to the problem form, at the bottom of which you'll see a new related list: Virtual War Rooms.
- It's probably rare that we're going to be making use of this relationship since it currently requires a manual association between a Problem and a Virtual War Room record, so even though we might expect most problems to be generated from major incidents, which would also result in Virtual War Rooms being created, let's make sure that this related list only shows up when it has something in it. Right-click in the header of the Virtual War Rooms related list at the bottom of the Problem form, and go to Configure | List Control.
Check the box Omit if empty, then click Save or Submit.
Now let's repeat the preceding steps on the Virtual War Room table, and add the Problems related list there. Let's also similarly check the Omit if empty box on that related list. This way, we can see the relationship from both directions!
It's likely that most relationships between a war room and a problem will be one-to-one, but by making this M2M table and relationship, we allow for the possibility of having multiple problems generated from one major incident, and/or associated with multiple war rooms, or one problem addressed by multiple war rooms from multiple major incidents, which were only discovered later, to be related to the same problem.
Other M2M relationships
Perhaps the most well-known examples of many-to-many tables, are the sys_user_has_role and sys_user_grmember tables. These tables respectively maintain relationships between users and the roles that they have, and between users and the groups of which they are members. These M2M tables are not defined in the sys_m2m table, but are specially made by ServiceNow.
The sys_m2m table is probably the easiest way to define a many-to-many relationship, but can you think of another way to do so? That is, to create a relationship in which the left-side record can be related to an arbitrary number of right-side records, which can in turn be related to an arbitrary number of left-side records?
In terms of using a field to relate one record to one other, a reference field is pretty effective; however, that isn't the only type of field that can accept a FK value in ServiceNow. To see an example of a List field type, let's navigate to the sc_catalog table. This table stores information about the service catalogs in your instance.
On the sc_catalog table, open the Service Catalog record, and you'll see a field in the left column called Editors, but instead of an input field, you'll see a lock icon:
This is a List field type, which is a particular sort of reference field. In this case, the reference points to the User (sys_user) table. Clicking the lock icon opens up the field for editing. You can enter one value after another, and the field will store them in the database column as a comma-separated list of Sys IDs. You might say that this is not technically an FK column in the database, but more like a multi-FK column. It contains multiple PKs (Sys IDs).
It would be technically possible to put one of this sort of field on one table (table A) and another on another table (table B), and thus have a pseudo-many-to-many relationship between these two tables. In practice, this is wildly impractical, but just serves to demonstrate that there are few things that cannot be done in ServiceNow's database!