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

sabell2012 · ‎08-15-2018

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

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.
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!
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"!

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.

Ahmed Hmeid1 · ‎08-17-2018

Nice article. Couple of things I wanted to point out. on .insert(), ACLs are not applied. GlideRecord does not adhere to any ACL restrictions for any operation by design (client side GlideRecord does). To have GlideRecord adhere to ACLs you need to use GlideRecordSecure instead, but to be honest, I've never found a use case for it in my work.

Secondly, with deleteMultiple(), you don't need to do the .query() first. If it's a large set of data, then it actually adds performance hit too. .deleteMultiple() works just with the query that's already in the GlideRecord, it doesn't need to do the "select *" first and pull all the data back.

sabell2012 · ‎08-17-2018

Good points. Thanks for the feedback!

Steven.

GoBucks · ‎08-22-2018

These are great GlideRecord articles, Steven. I've learned some good stuff in these. A couple of questions...

1) I see the following for-loop in your code for inserting multiple records:

// 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 = item.impact;
    incidents.urgency = item.urgency;
    incidents.short_description = item.short_description;
    incidents.work_notes = item.work_notes;
    incidents.insert();
}

I never see where the 'incident' variable is used after it is declared (line 3 in above snippet). 'incidents' is the GlideRecord object you're going to insert, item is the current object from the array incidentList, but 'incident'?

2) Really helps to see how one can use the addOrCondition() method via dot-walking. I'm so going that route in the future! In the past, with some more complex 'ORs' I've ended up just using addEncodedQuery() to get my 'ORs' within a bigger query. Is addEncodedQuery() slower though, than say one built using addQuery and addOrCondition?

sabell2012 · ‎08-22-2018

1) Yow! Nice catch! I have corrected that script as it was wrong!

2) I have not seen any performance differences between the two methods.

Thanks,

Steven.

GoBucks · ‎08-22-2018

Sounds good, thanks again.

Jamsta1912 · ‎08-25-2018

Hi Steve,

Thanks for posting. This is a really useful series. I have some questions about the first example script, 'INSERTING A SINGLE RECORD'.

1) What are your thoughts on using newRecord() in place of initialize()? My understanding is that using newRecord() sets default field values before the insert, but there may be other considerations? I know that it's also possible to create the new record without using either method and instead just jumping straight in to setting field values.

2) What is the value of using query() before the initialize step?

3) I've tended to use the same direct method you have here for setting field values in the new record, eg incidents.short_description = 'My really cool incident!'; But I've seen it suggested that it's better practice to use setValue, eg incidents.setValue('short_description', 'My really cool incident!');. Do you have any thoughts on that?

Jamie

sabell2012 · ‎10-01-2018

Jamie:

Sorry, meant to respond before now.

1) Chuck (Chuck Tomasi) did a great job answering your question here:

https://community.servicenow.com/community?id=community_question&sys_id=639a072ddb5cdbc01dcaf3231f9619ae

2) It opens a connection to the table for CRUD activities (Create, Read, Update, Delete).

3) I have done performance checks on the various functions. Unlike with the getValue method I find setValue does not improve performance and complexes readability/maintainability. Therefore I use the direct method.

Hope that helps!

Steven.

Jamsta1912 · ‎10-01-2018

Thanks Steve 🙂