Scripting: Handling Relationships...

[SlightlyLoony]

Many tables in Service-now have relationships to other tables. For example, records in the CMDB's network adapter table (cmdb_ci_network_adapter) are generally related to other CIs (such as a computer). This relationship reflects the real-world relationship of (say) a couple network adapters installed in a server.

If we were drawing an ERD of this relationship, it might look like the diagram at left. There are many different graphic notations for ERDs, so what you're used to might look a bit different — but the general idea is the same in any notation. Here our little ERD is showing that there is a many-to-one relationship between network adapters and computers — a network adapter belongs to a single CI, but a single CI may have any number of network adapters.

GlideRecord automagically handles the pesky details of object hierarchies, but when you want to find or manipulate related records, you're on your own. For example, suppose that in a script you've got a GlideRecord for a computer (which is a CI), and you want to find the network adapters that belong to it. How do you do that?

The first thing you have to do is figure out exactly what kind of relationship your two tables have. On the Service-now platform there are three distinct types of relationships between records. Today we'll talk about one of those: the related list, which the CI/network adapter relationship is a good example of.

In related lists, the table on the many side of the relationship (related lists are always a many-to-one relationship) has a reference field in it that "points" to the one side of the relationship. In the case of network adapters, the cmdb_ci_network_adapter has a field named cmdb_ci that is a reference to cmdb_ci (meaning to any CI). If you had a record for computer "Benonio" and it had two associated network adapters, your network adapter table would contain two records whose cmdb_ci field pointed to "Benonio". It's a simple as that.

How could we generalize this and make it easy? Wouldn't it be nice if I could write code like this?

var gru = new GRUtil();
var nics = gru.getRelatedList(computer, 'cmdb_ci_network_adapter', 'cmdb_ci');

The idea here is that if my computer variable contains a GlideRecord with a particular computer in it, then I can call getRelatedList() and it will return another GlideRecord containing the network adapters that belong to that computer. Here's how I can extend GRUtil to add that method. I started with the version of GRUtil defined here, then added this new method:

   /*
    * Returns a GlideRecord instance containing the records related to the given parent (a GlideRecord)
    * in the given related table through the given reference field.
    */
   getRelatedList: function(parent, related_table, reference_field) {
      var result = new GlideRecord(related_table);
      result.addQuery(reference_field, parent.sys_id);
      result.query();
      return result;
   },

When I run this test code (I have a computer named "labapp02"):

var computer = new GlideRecord('cmdb_ci_computer');
computer.addQuery('name', 'labapp02');
computer.query();
if (computer.next()) {
   var gru = new GRUtil();
   var nics = gru.getRelatedList(computer, 'cmdb_ci_network_adapter', 'cmdb_ci');
   while (nics.next())
      gs.log(nics.name);
}

I get this result:

  eth0
  eth1
  eth2

Perfect.

See why that kid is all excited? Aren't you?