logo

NJP

← Back to News

A GlideRecord Survey - Part 3 (Intermediate)

Import · Aug 15, 2018 · article

NOTE: MY POSTINGS REFLECT MY OWN VIEWS AND DO NOT NECESSARILY REPRESENT THE VIEWS OF MY EMPLOYER, ACCENTURE.

DIFFICULTY LEVEL: INTERMEDIATE
Assumes having taken the class SSNF and has good intermediate level of knowledge and/or familiarity with Scripting in ServiceNow.

In my previous two articles I covered some of the structure of a GlideRecord query. Now we will cover types of queries. I will be presenting the following:

  • .insert
  • Inserting Multiple Records
  • .update
  • .updateMultple
  • .delete
  • .deleteMultiple
  • Suspending Business Rules during query execution
  • Complex Dot Walking of a Query Structure
  • Joining Tables

Caveats

  1. I'm using the London Patch 0 release. This should not make a difference though. Any version after Helsinki (or maybe earlier) may work. Probably.
  2. All values I use in the following queries are ones I had on my personal developer instance. Your personal developer instances may not have the same data present. If so...improvise!
  3. I will be doing all of these examples in Scripts - Background, but they will work fine in a Fix Script.

Useful Links

Using GlideRecord To Query Tables

Operators available for filters and queries

MySQL :: SQL Statement Syntax

Previous Articles in this Series

Community Code Snippets: A GlideRecord Survey - Part 1 (Beginner)

Community Code Snippets: A GlideRecord Survey - Part 2 (Intermediate)

INSERTING A SINGLE RECORD

This is a pretty straightforward process. You definitely want to use the .initialize as this function creates a blank record with a new sys_id. Interestingly you aren't restricted by mandatory fields when inserting a new record into a table with such fields defined. However, Business Rules will fire, and ACLs will apply.

var incidents = new GlideRecord('incident');
incidents.query(); // open the table for work

incidents.initialize(); // create a blank record with new sys_id
incidents.impact = 2; // medium
incidents.urgency = 2; // medium
incidents.short_description = 'My really cool incident!';
incidents.work_notes = 'Auto inserted incident.';
incidents.insert();

SQL Equivalent:

INSERT INTO incident (impact, urgency, short_description, work_notes)

VALUES (2, 2, 'My really cool incident!', 'Auto inserted incident.');

INSERTING MULTIPLE RECORDS

Unfortunately there is no .insertMultiple function for a GlideRecord. This actually would be pretty easy to implement so I am unsure as to why it hasn't been done over the years. So, I use something like the following code as a good way to organize the insertion of multiple records into a given table (when all I have is data). If I am bringing data from one table into another I use a similar approach as this. I find it to be a best-practice in regard to readability and maintainability. 

// set up an object array to contain the records that will be inserted
var incidentList = [];

// play record 1
var newIncident = {};
newIncident.impact = 2;
newIncident.urgency = 2;
newIncident.short_description = 'hello world!';
newIncident.work_notes = 'Auto-created incident 1!';
incidentList.push(newIncident);

// play record 2
newIncident = {};
newIncident.impact = 1;
newIncident.urgency = 1;
newIncident.short_description = 'hi there!';
newIncident.work_notes = 'Auto-created incident 2!';
incidentList.push(newIncident);

var incidents = new GlideRecord('incident');
incidents.query();

// now loop through the object array and write all the new records out to the table.
for (var item in incidentList) {
    var incident = incidentList[item];
    incidents.initialize();
    incidents.impact = incident.impact;
    incidents.urgency = incident.urgency;
    incidents.short_description = incident.short_description;
    incidents.work_notes = incident.work_notes;
    incidents.insert();
}

SQL Equivalent:

INSERT INTO incident (impact, urgency, short_description, work_notes)VALUES(2, 2, 'hello world!', 'Auto-created incident 1!'),

(1, 1, 'hi there!', 'Auto-created incident 2!')

UPDATE

GlideRecords are interesting in that they allow you to "open" a record-set for insert/update/delete. That open can be done with a .query() or a .get(). Once open you can continue to do updates/inserts and even deletes on the fly. The record-set remains open until you null the object (i.e. incidents = null in the example below), or the script completes execution.

This example opens a record-set with a single record for updating. Note that this record-set stays open AFTER the if statement is complete.

var incidents = new GlideRecord('incident');
if (incidents.get('number', 'INC0010157')) {
    incidents.urgency = 1;
    incidents.update();
}

SQL Equivalent:

UPDATE incident SET urgency = 1 WHERE number = 'INC0010157';

UPDATE MULTIPLE (Method 1)

In this first method you will be modifying all returned records in the record-set. This is the most efficient code-wise. It is also easier to maintain. This is a do-it-all-at-once modification.

var incidents = new GlideRecord('incident');
incidents.addQuery('number', 'IN', ['INC0010157','INC0010158']);
incidents.query();
incidents.urgency = 1;
incidents.updateMultiple();

SQL Equivalent:

UPDATE incident SET urgency = 2 WHERE number IN ('INC0010157', 'INC0010158');

UPDATE MULTIPLE (Method 2)

This second method allows you to have more control over each record being modified. It would give you, the developer, the ability to test values before allowing the modification. This is a one-at-a-time record modification.

var incidents = new GlideRecord('incident');
incidents.addQuery('number', 'IN', ['INC0010157','INC0010158']);
incidents.query();

while (incidents.next()) {
    // update only certain records
    if (incidents.urgency == 2) {
        incidents.urgency = 1;
        incidents.update(); // update only this record
    }
}

DELETE A SINGLE RECORD

Okay, a caveat before we get into updates and deletes. TEST, TEST, TEST! Before you actually run the query. With a delete query you are basically stating to go out and retrieve all records with a particular value and then drop them from the table. The following method allows you to do a test (if statement) prior to making the delete.

var incidents = new GlideRecord('incident');
incidents.addQuery('number', 'INC0010157');
incidents.query();

if (incidents.next()) {
    gs.info('---> number: {0}', [incidents.number]);
    //incidents.delete(); // comment this line out UNTIL you are really sure that you are happy with the result set!!!!!
}

SQL Equivalent:

DELETE FROM incident WHERE number = 'INC0010157';

DELETE MULTIPLE (Method 1)

This method allows you to do testing before deleting each record. This is a delete-one-record-at-a-time method.

var incidents = new GlideRecord('incident');
incidents.addQuery('number', 'IN', ['INC0010157','INC0010158']);
incidents.query();

while (incidents.next()) {
    gs.info('---> number: {0}', [incidents.number]);
    //incidents.delete(); // comment this line out UNTIL you are really sure that you are happy with the result set!!!!!
}

SQL Equivalent:

DELETE FROM incident WHERE number IN ('INC0010157', 'INC0010158');

DELETE MULTIPLE (Method 2)

This is a delete-all-at-once method. Use this with care always! You should test this thoroughly BEFORE uncommenting the deleteMultiple statement. This can be a very dangerous query!!!

var numberList = ['INC0010157', 'INC0010156', 'INC0010155'];

var incidents = new GlideRecord('incident');
incidents.addQuery('number', 'IN', numberList);
incidents.query();
// incidents.deleteMultiple(); // poof! gone! TEST TEST TEST TEST TEST with this one!

SUSPENDING BUSINESS RULES DURING Update/Insert/Delete

So when we run an update, insert, delete or whatever query against a table it fires any business rules that are present. They may not execute because of their condition(s), but no matter, they still run. This can be a serious performance hit if you are executing against a large set of records. Also, it may be you don't WANT to run any business rules during your execution. So there is a way to suspend business rule execution with the setWorkflow method. 

var incidents = new GlideRecord('incident');
// this statement disables the business rules for the duration of the execution
incidents.setWorkflow(false); 
incidents.query(); // open the table for work

incidents.initialize(); // create a blank record with new sys_id
incidents.impact = 2; // medium
incidents.urgency = 2; // medium
incidents.short_description = 'My really cool incident!';
incidents.work_notes = 'Auto inserted incident.';
incidents.insert();

MORE ON DOT WALKING

We can really get rediculous with this, but I will do it to bring home a readability idea.

We can take the following OLD method of doing an OR query: 

var incidents = new GlideRecord('incident');
var subInc = incidents.addQuery('number', 'INC0010157');
subInc.addOrCondition('number','INC0010156');
subInc.addOrCondition('number','INC0010155');
var subInc2 = incidents.addQuery('state', 2);
subInc2.addOrCondition('state', 3);
incidents.addQuery('urgency', 3);
incidents.query();

while (incidents.next()) {
    gs.info('---> number: ' + incidents.getValue('number'));
}

and rewrite it thus to show how dot walking the WHERE can work: 

var incidents = new GlideRecord('incident');
incidents.addQuery('number', 'INC0010157')
        .addOrCondition('number', 'INC0010156')
        .addOrCondition('number', 'INC0010155')
    .addQuery('state', 2).addOrCondition('state', 3)
    .addQuery('urgency', 3);
incidents.query();

while (incidents.next()) {
    gs.info('---> number: ' + incidents.getValue('number'));
}

This REALLY enhances readability when working with the more complex query statements. Again, this is for example purposes only. The .addOrConditions should really be written as an IN statement.

The "IN" version would be written thusly: 

var numberList = ['INC0010157', 'INC0010156', 'INC0010155'];

var incidents = new GlideRecord('incident');
incidents.addQuery('number', 'IN', numberList);
    .addQuery('state', 'IN', [2, 3])
    .addQuery('urgency', 3);
incidents.query();

while (incidents.next()) {
    gs.info('---> number: ' + incidents.getValue('number'));
}

Which, obviously, is the best method in readability and maintainability.

JOIN

This has its place in the toolbox.

It is something I usually avoid using, and opt for the better way by building a Database View which will give you true JOIN capability.

Anyway, I throw it in here as it has its uses.

This query pulls in all records from the incident table where the incident.cmdb_ci field has a value, and that value exists in the task_ci table.

var incidents = new GlideRecord('incident');
incidents.addActiveQuery(); // only active = true records
incidents.addNotNullQuery('cmdb_ci');
incidents.addJoinQuery('task_ci','ci_item','cmdb_ci');
incidents.query();

while (incidents.next()) {
    gs.info('---> incident number: ' + incidents.getValue('number') + ', CI: ' + incidents.cmdb_ci.getDisplayValue());
}

SQL Equivalent:

SELECT incident.* FROM incident

JOIN task_ci ON incident.cmdb_ci = task_ci.ci_item;

So, that is it for the "intermediate" material. My next article (Part 4) will dig into some of the more "advanced" stuff!

Enjoy!

Steven Bell.

If you find this article helps you, don't forget to log in and mark it as "Helpful"!

image

Originally published on: 08-15-2018 04:19 PM

I updated the code, fixed broken links, and brought the article into alignment with my new formatting standard.

View original source

https://www.servicenow.com/community/developer-articles/community-code-snippets-a-gliderecord-survey-part-3-intermediate/ta-p/2330312