Many-to-many relationships

Another type of relationship between records is many-to-many. The relationship between siblings is many-to-many. I can have many brothers and sisters, as can they. But how can I store this information? A reference field can only point to one record.

Adding a lot of reference fields into a form is one way. I could create as many reference fields as I have siblings. However, that's not great design. What if another brother or sister were born? Making new fields every time a new baby arrives is not cool.

Instead, we could create another table that sits in between two records, acting as the "glue" that sticks them together. This many-to-many table has two reference fields, each pointing to a different side of the relationship.

In the hotel application, we want to take reservations for our guests. Each reservation might be for more than one person, and each person might have more than one reservation. This sounds like the perfect use for a many-to-many table. Here's a representation:

The diagram shows how it might work out. Richard is staying one night, on 1 Feb. That's easy enough. Lionel is staying two nights, on 1 Feb and 1 Mar. He liked our hotel so much that he came back, and encouraged his wife Lotte to stay with him.

The ServiceNow platform makes this a little easier to visualize, since it hides the complexities of the many-to-many table in most situations. It focuses on the records in the two target tables.

Building a many-to-many table

Let's begin building a many-to-many table. To do this, perform the following steps in Studio:

1. Click Create New Application File, select Table, then Create. Fill out the form, and Save.
   • Label: Reservation
2. Using the Table Columns related list, add the first date field to the table:
   • Column label: Arrival
   • Type:Date
   • Then add another, as below:
   • Column label: Departure
   • Type:Date
3. Finally, since we want to reserve a room, let's also create a reference field called Room:
   • Column label: Room
   • Type:Reference
   • Reference:Room [x_hotel_room]
4. Click Submit to save.
5. Then, click Create New Application File, select Many to Many Definition, then Create. Use the following data to fill out the form.
   • From table: Reservation [x_hotel_reservation]
   • To table: Guest [x_hotel_guest]

   The From and To tables are where we want to point our reference fields. It doesn't matter which way round you do it.

   You'll see the other fields populate automatically. Make sure the table name makes sense - but leave the m2m part in, so you know what it is. The default (x_hotel_m2m_guests_reservations) makes sense in this case.

   It should look like this:

   

   Tip

   The way to do this in the standard interface is by entering sys_m2m.list in the filter text in the application navigator. It accepts a few shortcuts like this. For example, <table_name>.form will display the form of the table.

6. Click on Create Many to Many.
7. Finally, click Create New Application File, select Related List from Forms & UI, and click Next. Choose the Guest table, and click Create. Add in the new Reservations entry into the Selected column, remove all the others and click Save.
8. If you look at Alice Richard's Guest record in the standard interface, you should see the related list, as below:

Adding fields to a many-to-many table

Sometimes, just having the two reference fields in the many-to-many table is enough. However, since it is a real table, you can also add new fields to it. This technique is useful for identifying something particular about the relationship. Let's use this capability to store who the lead passenger in a reservation is:

1. In Studio, open the table record by finding M2m Guests Reservations at the top of the Application Explorer, and clicking it. Unfortunately, the default label is a little ugly, so change it as below.
   • Label: Guests Reservations
2. Then, add the following fields to the table, using the Table Columns.
   • Column label: Lead
   • Type: True/False
3. Once done, click Update.

   Once we added the Reservations list to the Guest form, we can see what reservations a guest has. Let's now add the Guests list to the Reservations form, and view the many-to-many table the other way round.

   Click Create New Application File, select Related List from Forms & UI, and click Next. Choose the Reservation table, and click Create. Add in the Guests entry into the Selected column, and click Save.

4. Let's check our reservations work. Jump into the normal interface, and navigate to Hotel > Reservations. Click New, and specify some sample Arrival, Departure and Room values. Once done, click Save.
5. Let's create a quick way to add a guest to the reservation. Right click on the field headers of the Guest Related list, and choose Configure, List Control, like the screenshot:
   
6. Fill out the following fields, and click Submit.
   • List edit insert row: <ticked>
7. You can then use list editing on the related list to record the information you want.
   

Comparing reference fields and many-to-many tables

Many-to-many tables are very flexible, but by using them, you lose some of the advantages of simple reference fields. The biggest disadvantage is that you can't dot-walk in the same way. This makes scripting more challenging.

Also, with a simple list view, you can't easily identify related records. One way round this is through hierarchical list views, which we will discuss later.

We'll look at further disadvantages of many-to-many tables as we progress.

Deleting a many-to-many table

Deleting a many-to-many table isn't straightforward. You need to do it in two parts: delete the table, and then delete the entry from the sys_m2m table. However, there are security rules that prevent you from deleting records in this table. You will need to disable or modify those rules to proceed. But beware of what you are doing!

Choosing lists

An alternative to many-to-many tables are list fields.

Note

You may hear lists being referred to as Glide lists.

Lists store an array of sys_id values. That means that one field can reference multiple records. One field can work in a similar way to a many-to-many table. In our earlier example, a List field could be added to the Reservations form instead, pointing to the Guest table.

Tip

One disadvantage of lists is the interface. It is more difficult to interact with compared to other fields, both on the list and the forms, since there are more clicks and buttons. Also, since it contains multiple values, you can't dot-walk through it-which one would you walk to?

When you want to reference many records, compare the different advantages of the two approaches:

• Lists are represented as fields. They are more compact than many-to-many tables, and many built-in functions in ServiceNow accept comma-separated reference fields as input. For example, a comma-separated list of users can easily be sent an e-mail. Glide lists are usually simpler to deal with.
• Many-to-many relationships are represented as records in a table. This means there is no limit to the number of records stored, and you can easily extend the functionality. You can add extra fields (such as representation of the lead passenger). It also has better hooks for scripts and other functionalities. Many-to-many tables are generally more flexible.
• Reference fields deal with the deletion of the target data really well, as we'll see. Lists don't; they stay unaltered, meaning that sys_ids of records that don't exist anymore may hang around.