Query From Table

Standard GlideRecord Query Example

// 1. Create an object to store rows from a table 
       var myObj = new GlideRecord('table_name'); 
// 2. Build query 
       myObj.addQuery('field_name','operator','value'); 
       myObj.addQuery('field_name','value'); 
// 3. Execute query  
       myObj.query(); 
// 4. Process returned records 
       while(myObj.next()){   
        //While the recordset contains records, iterate through them
        //Logic you want to execute.   
        }

var gr = new GlideRecord('incident');  
gr.addQuery('active', true); 
gr.query(); 
if(gr.next()) { 
  gs.print('Incident Number: ' + gr.number); // get the incident number 
  gs.print('Incident Caller Name: ' + gr.getDisplayValue('caller_id')); // To get the display name of reference field. 
  gs.print('Incident Caller sys id: ' + gr.caller_id); // Caller is reference field on incident 
}

Output: Use while statement if you want to get multiple record, in my case i have used if so its return only one record. Value might be differ in your case.

Incident Number: INC0010033
Incident Caller Name : Runjay Patel
Incident Caller sys id: 25e5b0372f413010ce9524c62799b6b6

Insert Example

Create a new Incident record and populate the fields with the values.

var incGr = new GlideRecord('incident');
    incGr.initialize(); // initialize the incGr object
    incGr.short_description = 'Database issue'; // set the field value
    incGr.category = 'software';
    incGr.caller_id.setDisplayValue('Runjay Patel');
    incGr.insert();

Update Example

Find all incident records which has empty coller and make them cancel and inactive.

var gr = new GlideRecord('incident');
    gr.addNullQuery('caller_id');
    gr.query();
    while(gr.next()) {
      gr.active = false;
      gr.state = 8;
      gr.update();
    }

Output: It will make inactive and cancel list of record which has empty caller.

Delete Example

Find incident records and delete them one-by-one.

var incGr = new GlideRecord('incident');
    incGr.addNullQuery('short_description');
    incGr.query();
    while (incGr.next()) {
     incGr.deleteRecord();//Delete each record in the query result set
    }

deleteMultiple(): can be use to delete all record from set.

var incGr = new GlideRecord('incident');
    incGr.addNullQuery('short_description');
    incGr.deleteMultiple();

glideAggregate Example

GlideAggregate is an extension of GlideRecord. It provides the capability to do aggregation (COUNT, SUM, MIN, MAX, AVG).


These operations also we can perform with regular GlideRecord, but if you are doing with GlideAggregate, then it will be optimized and responsive.

var incGa = new GlideAggregate('incident');
    incGa.addAggregate('COUNT', 'category');
    incGa.orderBy('category');
    incGa.query();
    while (incGa.next()) {
     var incidentCount = incGa.getAggregate('COUNT', 'category');
     gs.log('Total count of '+incGa.category +' : '+ incidentCount);
    }

Output: Value might be differ in your case.

Total count of database : 1
Total count of hardware : 5
Total count of inquiry : 63
Total count of network : 4
Total count of software : 9

setAbortAction Example

Abort the operation if validation fails.

Lets say you have two fields date1 and date2 on incident form and you want to make sure that date2 should always be grater or equal to date1 and if it fails then stop the operation and display message to user stating that Start date must be before end date.

if ((!current.u_date1.nil()) && (!current.u_date2.nil())) {
    var start = current.u_date1.getGlideObject().getNumericValue();
    var end = current.u_date2.getGlideObject().getNumericValue();
    if (start > end) {
        gs.addInfoMessage('Start date must be before end date');
        current.setAbortAction(true);
    }
}

Output: If validation fails then it will display message to user stating ‘Start date must be before end date’ and abrupt the operation.

OR Query Example

Note: Use addQuery() for ‘And’ operation

var incGr = new GlideRecord('incident');
    var orGr = incGr.addQuery('state', 6); // Resolved State
        orGr.addOrCondition('state', 7); // Closed State
        orGr.addOrCondition('state', 8); // Canceled State
    incGr.query();
    while(incGr.next()) {
      gs.log('Category is ' + incGr.number);
    }

Output: It will give you list of incidents which has state Resolve or Closed or Canceled.

Shortest way to query from Table

Get single record from table

  
 var incGr= new GlideRecord('incident');
     incGr.get('516ad3c32fd25510ce9524c62799b676'); //PASTE_USER_SYS_ID
      
    gs.log('Incident Number: ' +incGr.number);
    gs.log('Short Description: '+ incGr.short_description);

Explanation:

• • Create an object using new operator and pass table name as parameter of GlideRecord.
  • Now use get() function and pass the sys id of record.
  • This two line of code will give you single record.
  • Now you can get the value using object.column_name

Output : Value might be differ in your case.

Incident Number: INC0010149
Short Description: Hello its Runjay Patel this side

Get multiple records from table

var prbGr = new GlideRecord("problem");
    prbGr .get("category", "software");
    prbGr .query();

   while (prbGr .next()) {
     gs.log(prbGr .number);
   }

Explanation:

• • Create an object using new operator and pass table name as parameter of GlideRecord.
  • Now use get() function and pass key pair value.
  • This two line of code will give you multiple records.
  • You can run the loop and get the value using object.column_name.

Output: You will get list of problem number which has category = ‘software’.

setLimit In Query

To enhance the query performance its recommended to use setLimit.

var incGr= new GlideRecord('incident');
    incGr.addQuery('priority','5');
    incGr.addQuery('category','inquiry');
    incGr.setLimit(1);
    incGr.query();
    if (incGr.next()) {
       gs.log('Incident Found: '+incGr.number);
    }

ChooseWindow will return all records between the first parameter(inclusive) and the second parameter(exclusive), so this example will return the 10 incidents between record 10-19 both inclusive. Works with orderBy()

//Limit the query to 10 incidents
var incGr = new GlideRecord('incident');
    incGr.chooseWindow(10, 20);
    incGr.orderBy('category');
    incGr.query();
    while(incGr.next()){
    gs.log('List of incident from 10th record to 19th record '+ incGr.number);
    }

getRefRecord Example

It is used to get the object of any reference field.

var callerObj = current.caller_id.getRefRecord(); //This will returns the GlideRecord object for the value populated in the 'caller_id' field
callerObj.email = '[email protected]';
callerObj.update();

var gr = new GlideRecord('incident');
gr.get('sys_id');
var caller = gr.caller_id.getRefRecord(); //Gets the sys_user GlideRecord for the caller
caller.first_name = 'New name';
caller.update();

Encode Query Example

var incGr = new GlideRecord('incident');
    incGr.addEncodedQuery('active=true^state=2'); //Encoded query
    incGr.query();
    while(incGr.next()) {
       gs.log('Category is ' + incGr.category);
    }

orderBy Example

orderByDesc() can be used in the same way to display data in descending order.

var IncGr = new GlideRecord('incident');
    IncGr.addQuery('active', true);
    IncGr.orderBy('category'); //Order this query by number
    IncGr.query();
    while(IncGr.next()) {
     gs.log('Incidens are in ascending order ' + IncGr.category);
    }

Output: orderBy() can be used when you want to display/order your data in ascending. In this example incidents will be displayed in ascending order by category.

getRowCount Example

var incGr = new GlideRecord('incident');
    incGr.addQuery('active', true);
    incGr.query();
   gs.log('Active incidents: ' + incGr.getRowCount());

Output: You will get count of active incident.

addNullQuery And addNotNullQuery Example

addNotNullQuery() is just opposite to addNullQuery, it will give list of incidents which caller id is not empty.

var gr = new GlideRecord('incident');
    gr.addNullQuery('caller_id');
    //gr.addNotNullQuery('caller_id');
    gr.query();
    while(gr.next()) {
      gs.log('Incidents which does not have caller : ' + gr.number);
    }

Output: It will give list of incident which caller id is empty.

setWorkflow(false) Example

If you wanted to update the record but at the same time you don’t want to trigger any other business rule which are running on the same table.  To overcome from this situation you can use setWorkflow(false).

var gr = new GlideRecord('incident');
gr.addNullQuery('short_description');
gr.query();
while(gr.next()){
    gr.short_description = 'This should not be empty';
    gr.setWorkflow(false); //Disable business rules for this query
    gr.update();
}

Output: Query will update the target record but other business rule which are running on incident update will be skipped.

autoSysFields Example

Sometime you might come in a situation where you want to update the record but at same time you want to update the system field like updated, updated_by etc.

You can use autoSysField(‘false’) in your code to do so.

var gr = new GlideRecord('incident');
gr.addNullQuery('short_description');
gr.query();
while(gr.next()){
    gr.short_description = 'This should not be empty..';
    gr.autoSysFields(false); //Disable the update of system fields
    gr.update();
}

Output: It will update the short description with value ‘This should not be empty..’ but it will not update the system fields.

System field which will get update skip: sys_updated_by, sys_updated_on, sys_mod_count, sys_created_by, and sys_created_on.