Enforcing one-to-one relationships

On the topic of few things being impossible in ServiceNow, let's discuss one-to-one relationships.

Strictly speaking, a one-to-one relationship doesn't truly exist in ServiceNow. In database parlance, this would require that the right-hand table records have a primary key which matches the primary key of a record in the left table. Thus, you could have a left-hand record without a right-hand one, but could never have a right-hand record without the left-hand one.

That's interesting, but ServiceNow's Sys IDs are unique, and they have to be, because of the way ServiceNow's databases are structured on the back-end. Technically, ServiceNow has a flat database structure, depending on how it's configured. In a sense, all records in the database (or all records in all tables that extend the Task table, at least) are in one monster-sized table. This means that the primary key (Sys ID) for a given record really must be globally unique.

Okay, so we can't have one-to-one relationships in the usual way you might have them in an ordinary SQL database; but remember, you can do almost anything you could need to do, in ServiceNow!

One way to create such a relationship is by propagating any changes made on one end of the relationship, to the record on the other end. Let's say for example, that we wanted to make certain that:

  • Each incident could only ever possibly have one Virtual War Room associated with it
  • Each Virtual War Room could only ever possibly have one incident associated with it:
    • Though this association should not be mandatory—if it exists, it should be bi-directional
  • The incident associated with a war room must be the one the war room is associated with, and vice-versa

There is no out-of-the-box way to accomplish this in ServiceNow, but we can accomplish this using a business rule and just a little bit of code! Follow the following steps to see how:

First, we need to add a virtual war room field on the Incident table, which we can use to link the incident to the Virtual War Room table, just like the war room is currently linked to the Incident table by the Major incident field.

  1. Start by navigating to the Incident table, using incident.list. Don't forget to press Enter!
  2. Right-click on the list header, and go to Configure | Table. Once on the form, click on New at the top of the Table Columns embedded list, in the Columns tab.

    Tip

    Remember that in List v3, there is a slightly different process to follow. You would instead click on the hamburger menu at the top-left of the list, and click on Configure, then on the dialog that pops up, you would click on Table.

  3. In the Type field, enter reference, and select the autocomplete option that displays after the field. New fields will become visible based on your selection.
  4. In the Column label field, enter Virtual War Room. The Column name field will auto-fill with the value u_virtual_war_room. Let's make this name a little shorter (but still clear and comprehensible), and change it to u_war_room.
  5. Click on the Reference Specification tab, and type virtual war room and select the appropriate value from the autocomplete suggestion:
  6. Finally, click Submit.

Now that we've got a field on the Incident table pointing to the Virtual War Room table, and one on the Virtual War Room table pointing to the Incident table, we're ready to begin enforcing this one-to-one relationship. To do that, we're going to need to create a business rule. Business rules allow us to create some basic automation functionality, or more advanced automation using scripting:

  1. Return to the Incident table, and open an incident record. If you'd like to change the location of the virtual war room field in the form, you can do so with the form designer before continuing.
  2. Right-click the incident form header, and go to Configure | Business Rules, then click on New.
  3. On the new Business Rule form, the Table field will be automatically populated for you. Enter a name you like. I chose Maintain 1:1 relationship with VWR.
  4. On the When to run column, set the filter to Virtual war room, changes. Then, check the boxes for both Insert and Update:
  5. These two check-boxes indicate that the business rule should run whenever a record is either inserted or updated, so long as that record matches the conditions we just set at the time it's saved.
  6. Next, tick the Advanced checkbox, click on the Advanced tab, and you'll see some code:
  7. This is just a stub. We've got to enter our own code starting on line 3, where it says Add your code here. For now, delete that comment and enter the following code. It's okay if you don't understand it, because we've got a whole chapter on this kind of thing coming up: 
          (function executeRule(current, previous /*null when async*/) {
    var grNewWarRoom;
    var grOldWarRoom;
    if (!current.u_war_room.nil()) {
        grNewWarRoom = current.u_war_room.getRefRecord();
        grNewWarRoom.setValue('u_major_incident', current.getValue('sys_id'));
        grNewWarRoom.update();
    }
    if (!previous.u_war_room.nil()) {
        grOldWarRoom = previous.u_war_room.getRefRecord();
        grOldWarRoom.setValue('u_major_incident', '');
        grOldWarRoom.update();
    }
})(current, previous);

    Tip

    Just to offer a quick explanation of what's going on in this script: On lines 3 and 4, we're first getting script objects representing the actual records corresponding to the war room records in the u_war_room field on the incident table, both before the change to the record that triggered this business rule to run (oldWarRoom) and after (newWarRoom). The previous object represents the record before the change, and the current object represents the record after the change. If you used a field name other than u_war_room, you should put your chosen field name in here instead! Next, on lines 5 and 6, we're updating the u_major_incident field on the war room records we found. On the war room record that was previously (but no longer) the selected war room, we clear out the Major incident field since it is no longer part of this one-to-one relationship. However, on the newly associated war room, we set the major incident field to contain the sys id of the current record (the one on which this change was made). Finally, on lines 7 and 8, we call .update(), to save our changes to the database.

  8. Once that business rule script is written, right-click on the header and click Save. This will keep us on the same record, unlike Update.

    Tip

    Note that if there are any overt errors in your script which the script editor is able to identify, it will be underlined and a red "X" icon will show up next to the line with the error.

  9. Now let's create a similar-but-opposite business rule for the Virtual War Room table. Without saving, change the Name field to Maintain 1:1 relationship with Incident, and the Table to Virtual War Room [u_virtual_war_room].
  10. At the top of the form, right-click the header, but don't hit save this time. Instead, click on Insert and Stay. This action will create a new record, identical to the one you were just on, except with the changes you made before clicking Insert and Stay. It also shows you the new record rather than the old one, like it would if you just selected Insert.
  11. Once the form reloads, you'll be on the newly created business rule record that will run on the Virtual War Room table, so we'll need to make some changes. For starters, go to the When to run tab, and change the condition so Major incident, changes.
  12. Back on the Advanced tab, we need to make some tweaks to our script so that it'll update the incident to the new war room when the war room's Major incident field is updated, rather than the other way around like we had it for the Incident table. Replace the script we wrote for the Incident table, with this new one that's tweaked just slightly for the Virtual War Room table: 
    (function executeRule(current, previous /*null when async*/) { 
      var newWarRoom = current.u_war_room.getRefRecord(); 
      var oldWarRoom = previous.u_war_room.getRefRecord(); 
      newWarRoom.setValue('u_major_incident', current.getValue('sys_id')); 
      oldWarRoom.setValue('u_major_incident', ''); 
      newWarRoom.update(); 
      oldWarRoom.update(); 
    })(current, previous);
  13. Right-click on the header, and click Save.

Because of these two business rules, from now on, whenever you update the Major incident field on a Virtual War Room, it'll propagate that change to the previously and newly selected Incident records. Similarly, if you update the virtual war room field on an incident, it'll propagate that change to the related war room(s).

It's not as elegant as a one-to-many relationship just using one field and a related list, and it would be seldom used, but that's one way to create a one-to-one relationship in ServiceNow!

上一章目录下一章