Array behavior when using GlideRecord.addQuery with IN

[thomaskennedy]

I have a requirement to update the Script Include "BackfillAssignmentGroup" backing a particular Dictionary override such that groups X and Y are excluded. So I figured this would be simple:

1. Query to get an array of those two sys_ids.

2. Query the group table using NOT IN, and pass {array}.join() as the last arg.

(There is a second case, not shown here, where I have to query sys_user_grmember and join it to sys_user_group, and the array of sys_ids facilitates this. I omit this for simplicity.)

function BackfillAssignmentGroup() {

  var gp = ' ';

  var iter = 0;

  var filter_out_groups = [];

  var log_data = [];

  // 1. get the sys_ids for groups to be excluded

  var gr = new GlideRecord('sys_user_group');

  gr.addQuery('name','IN','App-SNIncident-ETLDev,App-SNIncident-ESBDev');

  gr.query();

  while(gr.next()){

    log_data.push("adding sys_id " + gr.sys_id); // informational

    filter_out_groups.push(gr.sys_id);

  }

  gs.info( "log_data: " + log_data.join() );

  gs.info( "filter_out_groups: " + filter_out_groups.join() );

  // 2.

  gr = new GlideRecord('sys_user_group');

  gr.addQuery('type', '');

  gr.addQuery('sys_id', 'NOT IN', filter_out_groups.join());

  gr.query();

  iter = 0;

  while(gr.next()){

  if (iter>0){ gp += ','; }

  gp += gr.sys_id;

  iter++;

  }

  return 'sys_idIN' + gp;

}

I expected that both groups would be excluded from the results, but only the second was excluded.

Here is what I expected to see in the log:

log_data: adding sys_id ccae4471db69f200626add90cf9619d5,adding sys_id f3064ef2db91b20078587eb6bf961982

filter_out_groups: ccae4471db69f200626add90cf9619d5,f3064ef2db91b20078587eb6bf961982

Here is what I actually do see:

log_data: adding sys_id ccae4471db69f200626add90cf9619d5,adding sys_id f3064ef2db91b20078587eb6bf961982

filter_out_groups: f3064ef2db91b20078587eb6bf961982,f3064ef2db91b20078587eb6bf961982

How is it possible I'm getting the same sys_id in both elements of filter_out_groups[]? Particularly as the other array log_data is normal? Here again is the code that populates these two arrays:

while(gr.next()){

    log_data.push("adding sys_id " + gr.sys_id); // --> OK

    filter_out_groups.push(gr.sys_id); // --> [Y,Y]

  }

[srinivasthelu · Accepted solution]

Be explicit and convert it String

    filter_out_groups.push(gr.sys_id+'');     // I have modified this line in your below code

function BackfillAssignmentGroup() {

  var gp = ' ';

  var iter = 0;

  var filter_out_groups = [];

  var log_data = [];

  // 1. get the sys_ids for groups to be excluded

  var gr = new GlideRecord('sys_user_group');

  gr.addQuery('name','IN','App-SNIncident-ETLDev,App-SNIncident-ESBDev');

  gr.query();

  while(gr.next()){

    log_data.push("adding sys_id " + gr.sys_id); // informational

    filter_out_groups.push(gr.sys_id+'');     // I have modified this line

  }

  gs.info( "log_data: " + log_data.join() );

  gs.info( "filter_out_groups: " + filter_out_groups.join() );

  // 2.

  gr = new GlideRecord('sys_user_group');

  gr.addQuery('type', '');

  gr.addQuery('sys_id', 'NOT IN', filter_out_groups.join());

  gr.query();

  iter = 0;

  while(gr.next()){

  if (iter>0){ gp += ','; }

  gp += gr.sys_id;

  iter++;

  }

  return 'sys_idIN' + gp;

}

[mauricio23]

Have you tried the Array Util ?

http://wiki.servicenow.com/index.php?title=ArrayUtil#gsc.tab=0

[Brian Dailey1]

And... Srinivas has the answer, I believe.   I ran into this same problem using arrays for another purpose, and stumbled to the answer by accident and frustration.   When you use "push(gr.sys_id)", it seems to be passing by reference instead of by value, so you get an array of length (X) with all elements pointing to gr.sys_id, but all the values are from the last record passed into gr.   Forcing the value by using either (gr.sys_id + '')   or   (gr.sys_id.toString()) will do the trick.

It's not clear why they designed it to work this way, whether the byRef is intentional or not.   In my original run-in, I couldn't figure out for a while why my script would work if I built-out a string manually (i.e., "sys_id,sys_id,....") but failed trying to use an array that got join(ed) at the end.   This is why.   Good catch, srinivasthelu.

Thanks,

-Brian

[Kalaiarasan Pus]

To avoid this precise problem getValue() function is present as part of GlideRecord class. I always use that method or toString().