GlideRecord AddJoinQuery not working properly

[John Prouty]

I am working on a Script Include that will will update all the children records of a given parent record.  However, I cannot get the addJoinQuery to limit the records returned.  I can get the GlideRecord to return ALL the records in the table by removing the addQuery statement.

Since I am updating the Request Task Step records, I think the GlideRecord needs to be for the child table (Request Task Step) and then addJoinQuery for the parent table (Request Task).  The parent column of the Request Task Step is a reference to the Request Task table (whose primary key is sys_id).  Therefore I coded the addQuery to include the optional parameters: 'parent', and 'sys_id'.

The filter I have is the number of the Request Task record that "owns" the Request Task Step records that are to be updated.  The Script Include receives the number of the Request Task in 'sysparm_parent_id' and I assign it to the variable parent_id (which is working just fine).

When I try to include the addQuery ('number', parent_id);, the script fails (retrieves no records).

Here is the code:

var ResequenceSteps = Class.create();
ResequenceSteps.prototype = Object.extendsObject(global.AbstractAjaxProcessor, {

     reseqSteps: function() {
          var parent_id = this.getParameter('sysparm_parent_id');
          gs.addErrorMessage('Parent ID: ' + parent_id);

          var gr = new GlideRecord('x_525091_it_sw_enh_it_sw_request_task_step');
          gr.addJoinQuery('x_525091_it_sw_enh_it_sw_request_task', 'parent', 'sys_id');
          gr.addQuery('number', parent_id);
          gr.orderBy('step_num');
          gr.query();

          var new_step_num = 0;
          gs.addErrorMessage('new_step_num: ' + new_step_num);
          while (gr.next()) {
               var old_step_num = gr.getValue("step_num");
               var rec_parent = gr.getValue("parent");
               new_step_num = new_step_num + 10;
               gs.addErrorMessage('new_step_num: ' + new_step_num + ', old_step_num: ' + old_step_num + ', record parent: ' + rec_parent);
               //gr.step_num = new_step_num;
               //gr.update();
          }
          return '';
     },

     type: 'ResequenceSteps'
});

How do I fix this?

[Allen Andreas · Accepted solution]

Hi,

From my understanding, when you add query after doing the join, it only affects the table initiated for the glide, so in your case (x_525091_it_sw_enh_it_sw_request_task_step), if you're trying to apply the query to the other table as well (thinking you're filtering both tables with the same results...then you'd need to do separate queries which should be done through different variables.

Please refer to this documentation to see if it helps:

https://developer.servicenow.com/dev.do#!/reference/api/orlando/server_legacy/c_GlideRecordAPI#r_Gli...

// Look for Problem records
var now_GR = new GlideRecord('problem');
 
// That have associated Incident records
var grSQ = now_GR.addJoinQuery('incident');
 
// Where the Problem records are "active=false"
now_GR.addQuery('active', 'false');
 
// And the Incident records are "active=true"
grSQ.addCondition('active', 'true');
 
// Query
now_GR.query();
 
// Iterate and print results
while (now_GR.next()) {
    gs.print(now_GR.getValue('number'));
}

Please mark reply as Helpful/Correct, if applicable. Thanks!