Glidequery Part 2

#glidequery , #scripting

Peter Bell Andrew Barnes

4 minute read

Inserting, Updating, and Deleting in GlideQuery! Part 2 of the GlideQuery Series. Read Part 1 to catch up.

This is the second in a series of blog posts showing practical examples and patterns using GlideQuery. I believe one of the quickest ways to learn is by diving straight into examples. We’ll start with simple read queries and move on to more advanced topics with each post. Let’s begin!

Inserting

new GlideQuery('task')
    .insert({  
        priority: 1,
        description: 'Please help!',
    });

The insert method requires a JavaScript object, where each key is the name of the field you’d like to set, and the values are the values of the object. Insert actually returns an Optional (similar to selectOne) which can read back the value inserted. This can be useful if you’d like to get the sys_id. For example:

var task = new GlideQuery('task')
    .insert({  
        priority: 1,
        description: 'Please help!',
    })
    .get()
//  gs.info('task: ' + JSON.stringify(task, null, 4)); would provide
// task:  
// {
//     "sys_id": "ffbb332e61952010fa9b9c05b7356b03",
//     "priority": 1,
//     "description": "Please help!"
// }

GlideQuery’s fail fast principle will throw an Error if a business rule aborts an insert, unlike GlideRecord. GlideRecord returns null when an insert fails, but unless the developer is always diligent in checking for null, they may not realize this is happening. By throwing an Error (with some helpful details in the message), GlideQuery can make a mistake more visible:

new GlideQuery('alm_license')
    .insert({
        start_date: '2020-04-15', // note start_date should be before end_date!
        end_date: '2016-04-15'
    });

// Error message:
// {
//     message: 'Failure to insert object',
//     table: 'alm_license',
//     lastGlideError: 'Operation against file 'alm_license' was aborted by
// Business Rule 'Check for valid date range^011783713750200044e0bfc8bcbe5d81'.
// Business Rule Stack:Check for valid date range',
//     changes: {
//         end_date: '2016-04-15',
//         start_date: '2020-04-15'
//     }
// }

Note the value lastGlideError comes from calling GlideRecord’s getLastErrorMessage method, which contains the business rule which aborted the transaction.

Updating (single record)

GlideQuery has an update method for updating a single record. For example:

new GlideQuery('sys_user')
    .where('sys_id', userId)
    .update({ email: 'admin@example.com', active: true });

Note that update requires a where clause using a primary key. Like, insert, update returns an Optional containing the newly updated record. Additionally, update throws an Error if a business rule aborts the update, just like insert.

Updating (multiple)

new GlideQuery('sys_user')
    .where('sys_id', userId)
    .where('last_name', 'Griffey')
    .updateMultiple({ active: true });

updateMultiple does not return anything, and because of the nature of business rules, it won’t throw an Error if a business rule rejects the update.

Deleting

new GlideQuery('sys_user')
    .where('active', true)
    .where('last_name', 'Jeter')
    .deleteMultiple();

There is no method for simply deleting a single record; instead, you should use deleteMultiple with a where clause specifying a primary key if you only want to delete one record. It’s worth noting that both GlideRecord’s and GlideQuery’s versions of deleteMultiple can execute faster than GlideRecord’s deleteRecord, as the latter requires first fetching the record before deleting it.

Business Rules

Sometimes we want to ignore business rules when executing a query. For this we turn to disableWorkflow. For example:

new GlideQuery('sys_user')
    .disableWorkflow() // ignore business rules
    .where('email', 'bob@example.com')
    .updateMultiple({ active: false });

Data Integrity

When inserting or updating, GlideQuery automatically checks to make sure:

  • The fields used are valid
  • The type of the value matches the field type
  • If the field is a choice field, that the value given matches one of those choices

So, for example:

new GlideQuery('task')
    .insert({ name: 'Power outage' });
// Unknown field 'name' in table 'task'. Known fields:
// [
//     "parent",
//     "description",
//     ...
//     "location"
// ]

Throws an Error because there is no name field in the task table. GlideQuery is also nice enough to show which fields are available.

Conclusion

GlideQuery can insert, update, and delete records. Insert and update will return an Optional with the newly created/updated record. GlideQuery performs validation checks to ensure a greater degree of data integrity to modified records. Stay tuned for the next installment, and let me know in the comments how you are using GlideQuery!

Comments