Cheat Sheet for GlideRecord Queries

[Ravi Gaurav]

-->While the information in this post may not be groundbreaking, my objective is to create a concise compilation of common examples of these methods on a single page for convenient reference. This resource is invaluable for bookmarking!!!

A typical GlideRecord query follows this structure.

------------------------------------------------------------------------------------------------------------------------------------------------------

Query :-

var gr = new GlideRecord('incident'); //Instantiate a GlideRecord object for the 'incident' table
//Specify the criteria for the query using the 'addQuery' method (optional)
//gr.addQuery('active', true);
gr.query(); //Execute the query to fetch records
while (gr.next()) { //Iterate through each record in the result set
//Perform operations on the returned records
if(gr.category == 'software'){
gs.log('Category is ' + gr.category); //Log the category of the record
}
}

-------------------------------------------------------------------------------------------------------------------------------------

UPDATE:

This same functionality also applies to client-side GlideRecord queries! It's advisable to use an asynchronous query from the client if possible. Refer to this post for more details.

var gr = new GlideRecord('sys_user');
gr.addQuery('name', 'Joe Employee');
gr.query(myCallbackFunction); //Execute the query with the specified callback function
//Continue processing after the server returns the query result set
function myCallbackFunction(gr){
while (gr.next()) { //Iterate through each record in the result set
alert(gr.user_name); //Display the user name of the record
}
}

----------------------------------------------------------------------------------------------------------------------------------

'Get'

Query Shortcut (used to retrieve a single GlideRecord)

This method can also be utilized in Client scripts and UI policies IF YOU ARE GETTING A RECORD BY SYS_ID.
The 'get' method efficiently retrieves a single record when the sys_id of that record is known.

var gr = new GlideRecord('incident');
gr.get(sys_id_of_record_here);
//Perform operations on the returned record
if(gr.category == 'software'){
gs.log('Category is ' + gr.category); //Log the category of the record
}

You can also retrieve a record based on a specific field/value pair. The 'get' method returns the first record in the result set.

//Retrieve the first active incident record
var gr = new GlideRecord('incident');
if(gr.get('active', true)){
//Perform operations on the returned record
gs.log('Category is ' + gr.category); //Log the category of the record
}

--------------------------------------------------------------------------------------------------------------------------------

'getRefRecord'

Query Shortcut (used to retrieve a single GlideRecord referenced in a reference field)

The 'getRefRecord' method serves as a shortcut to query a record populated in a reference field on a record.

var caller = current.caller_id.getRefRecord(); //Retrieve the GlideRecord for the value populated in the 'caller_id' field
caller.email = 'test@test.com';
caller.update();

'OR' Query

The standard 'addQuery' parameter functions as an 'and' condition in your query. Below is an example of how to include 'or' conditions in your query.

//Retrieve all incidents with a priority of 1 or 2
var gr = new GlideRecord('incident');
var grOR = gr.addQuery('priority', 1);
grOR.addOrCondition('priority', 2);
gr.query();
while (gr.next()) {
//Perform operations on the returned records
if(gr.category == 'software'){
gs.log('Category is ' + gr.category); //Log the category of the record
}
}

-------------------------------------------------------------------------------------------------------------------------------------

Insert

Inserts are performed similarly to queries except you need to replace the 'query()' line with an 'initialize()' line as shown below.

//Create a new Incident record and populate the fields with the specified values
var gr = new GlideRecord('incident');
gr.initialize();
gr.short_description = 'Network problem';
gr.category = 'software';
gr.caller_id.setDisplayValue('Joe Employee');
gr.insert();

------------------------------------------------------------------------------------------------------------------------------------

Update

Updates can be performed on one or many records simply by querying the records, setting the appropriate values on those records, and calling 'update()' for each record.

//Retrieve all active incident records and set them as inactive
var gr = new GlideRecord('incident');
gr.addQuery('active',true);
gr.query();
while (gr.next()) {
gr.active = false;
gr.update();
}

--------------------------------------------------------------------------------------------------------------------------------------

Delete

Delete records by performing a GlideRecord query and then using the 'deleteRecord' method.

//Retrieve all inactive incident records and delete them one-by-one
var gr = new GlideRecord('incident');
gr.addQuery('active',false);
gr.query();
while (gr.next()) {
//Delete each record in the query result set
gr.deleteRecord();
}

-----------------------------------------------------------------------------------------------------------------------------------

deleteMultiple

Shortcut

If you are deleting multiple records then the 'deleteMultiple' method can be used as a shortcut.

//Retrieve all inactive incidents and delete them all at once
var gr = new GlideRecord('incident');
gr.addQuery('active', false);
gr.deleteMultiple(); //Deletes all records in the record set

addEncodedQuery

-----------------------------------------------------------------------------------------------------------------------------------

 'addOrCondition' statements.

//Retrieve all active incidents where the category is software or hardware
var gr = new GlideRecord('incident');
var strQuery = 'active=true';
strQuery = strQuery + '^category=software';
strQuery = strQuery + '^ORcategory=hardware';
gr.addEncodedQuery(strQuery);
gr.query();

-----------------------------------------------------------------------------------------------------------------------------------

GlideAggregate

GlideAggregate is actually an extension of the GlideRecord object. It allows you to perform the following aggregations on query recordsets: COUNT, SUM, MIN, MAX, AVG.

//Retrieve all active incidents and log a count of records to the system log
var gr = new GlideAggregate('incident');
gr.addQuery('active', true);
gr.addAggregate('COUNT');
gr.query();
var incidents = 0;
if (gr.next()){
incidents = gr.getAggregate('COUNT');
gs.log('Active incident count: ' + incidents);
}

-----------------------------------------------------------------------------------------------------------------------------------

orderBy/orderByDesc

You can order the results of your recordset by using 'orderBy' and/or 'orderByDesc' as shown below.

//Retrieve all active incidents and order the results ascending by category then descending by created date
var gr = new GlideRecord('incident');
gr.addQuery('active', true);
gr.orderBy('category');
gr.orderByDesc('sys_created_on');
gr.query();

-----------------------------------------------------------------------------------------------------------------------------------

addNullQuery/addNotNullQuery

'addNullQuery' and 'addNotNullQuery' can be used to search for empty (or not empty) values.

//Retrieve all incidents where the Short Description is empty
var gr = new GlideRecord('incident');
gr.addNullQuery('short_description');
gr.query();
//Retrieve all incidents where the Short Description is not empty
var gr = new GlideRecord('incident');
gr.addNotNullQuery('short_description');
gr.query();

-----------------------------------------------------------------------------------------------------------------------------------

getRowCount

'getRowCount' is used to get the number of results returned.

//Log the number of records returned by the query
var gr = new GlideRecord('incident');
gr.addQuery('category', 'software');
gr.query();
gs.log('Incident count: ' + gr.getRowCount());

Although 'getRowCount' isn't available client-side, you can return the number of results in a client-side GlideRecord query by using 'rows

.length'.

//Log the number of records returned by the query
var gr = new GlideRecord('incident');
gr.addQuery('category', 'software');
gr.query();
alert('Incident count: ' + gr.rows.length);

-----------------------------------------------------------------------------------------------------------------------------------

setLimit

'setLimit' can be used to limit the number of results returned.

//Retrieve the last 10 incidents created
var gr = new GlideRecord('incident');
gr.orderByDesc('sys_created_on');
gr.setLimit(10);
gr.query();

-----------------------------------------------------------------------------------------------------------------------------------

chooseWindow

The chooseWindow(first,last) method lets you set the first and last row number that you want to retrieve, which is useful for chunking-type operations.

//Retrieve the last 10 incidents created
var gr = new GlideRecord('incident');
gr.orderByDesc('sys_created_on');
gr.chooseWindow(10, 20);
gr.query();

-----------------------------------------------------------------------------------------------------------------------------------

setWorkflow

'setWorkflow' is used to enable/disable the running of any business rules that may be triggered by a particular update.

//Change the category of all 'software' incidents to 'hardware' without triggering business rules on updated records
var gr = new GlideRecord('incident');
gr.addQuery('category', 'software');
gr.query();
while(gr.next()){
gr.category = 'hardware';
gr.setWorkflow(false);
gr.update();
}

-----------------------------------------------------------------------------------------------------------------------------------

autoSysFields

'autoSysFields' is used to disable the update of 'sys' fields (Updated, Created, etc.) for a particular update.

//Change the category of all 'software' incidents to 'hardware' without updating sys fields
var gr = new GlideRecord('incident');
gr.addQuery('category', 'software');
gr.query();
while(gr.next()){
gr.category = 'hardware';
gr.autoSysFields(false);
gr.update();
}

-----------------------------------------------------------------------------------------------------------------------------------

setForceUpdate

'setForceUpdate' is used to update records without having to change a value on that record to get the update to execute.

//Force an update to all User records without changing field values
var gr = new GlideRecord('sys_user');
gr.query();
while (gr.next()) {
gr.setWorkflow(false); //Do not run business rules
gr.autoSysFields(false); //Do not update system fields
gr.setForceUpdate(true); //Force the update
gr.update();
}

-----------------------------------------

JavaScript Operators

The following operators can be used in addition to the standard field/value query searching shown above.

Operator Description Code
= Field value must be equal to the value supplied. addQuery('priority', '=', 3);
> Field must be greater than the value supplied. addQuery('priority', '>', 3);
< Field must be less than the value supplied. addQuery('priority', '<', 3);
>= Field must be equal to or greater than the value supplied. addQuery('priority', '>=', 3);
<= Field must be equal to or less than the value supplied. addQuery('priority', '<=', 3);
!= Field must not equal the value supplied. addQuery('priority', '!=', 3);

STARTSWITH Field must start with the value supplied. The example shown on the right will get all records where the short_description field starts with the text 'Error'. addQuery('short_description', 'STARTSWITH', 'Error');

ENDSWITH Field must end with the value supplied. The example shown on the right will get all records where the short_description field ends with text 'Error'. addQuery('short_description', 'ENDSWITH', 'Error');

CONTAINS Field must contain the value supplied anywhere in the field. The example shown on the right will get all records where the short_description field contains the text 'Error' anywhere in the field. addQuery('short_description', 'CONTAINS', 'Error');

DOES NOT CONTAIN Field must not contain the value supplied anywhere in the field. The example shown on the right will get all records where the short_description field does not contain the text 'Error' anywhere in the field. addQuery('short_description', 'DOES NOT CONTAIN', 'Error');

IN Field must contain the value supplied anywhere in the string provided. addQuery('sys_id', 'IN', '0331ddb40a0a3c0e40c83e9f7520f860,032ebb5a0a0a3c0e2e2204a495526dce');

INSTANCEOF Retrieves only records of a specified class for tables which are extended. For example, to search for configuration items (cmdb_ci table) you many want to retrieve all configuration items that are have are classified as computers. The code uses the INSTANCEOF operator to query for those records. addQuery('sys_class_name', 'INSTANCEOF', 'cmdb_ci_computer');

Thanks

Ravi Gaurav

Linkedin :- https://www.linkedin.com/in/ravi-gaurav-a67542aa/

YouTube :- https://www.youtube.com/@learnservicenowwithravi
```

[Madhuri14]

Useful. Thanks Ravi

[debendudas]

Nice, very useful!

[gourav786gs]

Thanks for sharing