GlideQuery insertOrUpdate()

[Thiago Pereira]

Hi!

I would like to know if in the insertOrUpdate method there is any way to return fields other than those informed in the object.

var user = new GlideQuery('task')
    .insertOrUpdate({
        sys_id: '993de72847554210cadcb60e316d4345',
        short_description: 'TEST',
        priority: 1,
        active: true		
    })
    .orElse(null);

Return

{
    "sys_id": "993de72847554210cadcb60e316d4345",
    "short_description": "TEST",
    "priority": 1,
    "active": true
}

I also need description and state.

brest,

Thiago

[Maddysunil · Accepted solution]

@Thiago Pereira 

I think In ServiceNow's GlideQuery API, the insertOrUpdate() method only returns the fields explicitly provided in the object passed to it. If you need additional fields like description and state, you will have to perform another query to retrieve those fields after the insertOrUpdate operation.

var user = new GlideQuery('task')
    .insertOrUpdate({
        sys_id: '993de72847554210cadcb60e316d4345',
        short_description: 'TEST',
        priority: 1,
        active: true        
    })
    .orElse(null);

// Now perform another query to retrieve additional fields
var updatedUser = new GlideQuery('task')
    .where('sys_id', user.sys_id) // Use the sys_id of the inserted/updated record
    .select('sys_id', 'short_description', 'priority', 'active', 'description', 'state')
    .first();

gs.info('Updated User: ' + updatedUser);

after the insertOrUpdate operation, we perform another query using the sys_id of the inserted/updated record to retrieve additional fields description and state. We use the select() method to specify the fields we want to retrieve, including the ones from the original object passed to insertOrUpdate(). Finally, we use first() to retrieve the first matching record.

Please Mark ✅Correct if this solves your query and also mark 👍Helpful if you find my response worthy based on the impact.

Thanks

[Thiago Pereira]

thanks Maddysunil

[Warren Shekyls]

This is a bug in the code of GlideQuery. Their logic is incorrect, you should not have to do another query to retrieve more fields. Under the hood they are essentially just calling an insert or update dependant on whether your update object has a primary key for that table.

Even the nice error that pops up confirms it's a bug, here is a simple example using insertOrUpdate where I want the number field back, even though it's not in my update payload. I think we can all agree number is a field on Incident table, and you shouldn't need to have it in the update object.

NiceError: [2024-06-27T09:05:19.159Z]: Unknown field 'number' in table 'incident'. Known fields:

[

  "parent",

  "made_sla",

  "caused_by",

  "watch_list",

  "upon_reject",

  "sys_updated_on",

  "child_incidents",

  "origin_table",

  "hold_reason",

  "task_effective_number",

  "approval_history",

  "number",

If they do not want you to include additional fields, they should remove the second parameter completely from the insertOrUpdate method, and update the documentation.

Lets dig a little bit deeper, where does the bug actually exist?

The insertOrUpdate function works by calling either the insert or update method, depending on whether the changes object contains a primary field. The insertOrUpdate method will generate a schema to decide between insert and update, then pass this "prefetched schema" to either method.

The idea is to avoid having the insert or update methods rebuild the schema again when they are executed.

However, there's a problem with how this schema is built. The schema only includes fields from the changes object and does not take into account any additional fields. As a result, the insert and update methods do not recognize the additional fields passed in as they are not included in the schema.

GlideQueryEvaluator.insertOrUpdate = function insertOrUpdate(glideQuery, changes, selectFields, reason) {

  //** It only builds the schema with the changes keys, not including selectFields as well... which eventually causes the errors */
  var schema = GlideQueryEvaluator.loadSchemaForTable(glideQuery,Object.keys(changes));

  var primaryKeys = GlideQueryEvaluator.getPrimaryKeysFromSchema(glideQuery.table,schema);
  var whereSteps = [];

  for (var pk in primaryKeys) {
    if (!changes[pk]) {
      /** It calls insert passing in the schema it has generated, this means insert doesnt have to do it again **/
      return GlideQueryEvaluator.insert(glideQuery, changes, selectFields, schema);
    }

    whereSteps.push({
      type: "where",
      field: pk,
      operator: "=",
      value: changes[pk],
      whereClause: true,
      action: GlideQueryActions.where(glideQuery.table, pk, "=", changes[pk]),
    });
  }

  var query = new GlideQuery(glideQuery.table, glideQuery.plan.concat(whereSteps));

  /** It calls update passing in the schema it has generated, this means update doesnt have to do it again **/
  return GlideQueryEvaluator.update(query, changes, selectFields, reason, schema, true);
};

I believe this could be fixed by concatenating the selectFields parameter with the changes keys when generating the schema:

var schema = GlideQueryEvaluator.loadSchemaForTable(GQ, Object.keys(changes).concat(fields));

Unfortunately these scripts are read only so we cannot override/patch the bug and run a thorough set of tests. Instead I wrote my own insertOrUpdate function (albeit a very simple version to match my use case where I check for sys_id in changes object). This means I do not need to do another DB query to retrieve additional fields after inserting/updating the record, much better performance if you are doing this a lot of times.

function iOrU(GQ, changes, fields) {
  if (!changes.sys_id) return GQ.insert(changes, fields);

  return GQ.where("sys_id", changes.sys_id).update(changes, fields);
}

A fuller solution will most probably look like this. BE WARNED THE SOLUTION BELOW HASNT BEEN FULLY TESTED, USE AT YOUR OWN RISK.

GlideQuery.prototype.insertOrUpdate = function iou(changes, fields, reason) {
    return myInsertOrUpdate(this, changes, fields || [], reason);
}

var gq = new GlideQuery('incident')
.insertOrUpdate({"short_description": "new incident"}, ["priority","number"])
.orElse(null);

gs.debug(JSON.stringify(gq,null,2))


function myInsertOrUpdate(GQ, changes, fields, reason) {
  var schema = GlideQueryEvaluator.loadSchemaForTable(GQ,Object.keys(changes).concat(fields));
  var primaryKeys = GlideQueryEvaluator.getPrimaryKeysFromSchema(GQ.table,schema);

  var whereSteps = [];

  for (var pk in primaryKeys) {
    if (!changes[pk]) {
      return GlideQueryEvaluator.insert(GQ, changes, fields, schema);
    }
    whereSteps.push({
      type: "where",
      field: pk,
      operator: "=",
      value: changes[pk],
      whereClause: true,
      action: GlideQueryActions.where(GQ.table, pk, "=", changes[pk]),
    });
  }

  var query = new GlideQuery(GQ.table, GQ.plan.concat(whereSteps));
  return GlideQueryEvaluator.update(query,changes,fields,reason,schema,true);
}