How to write negative query i.e. ( if at least one array element is not in another table than do smth with such record)?

[hyperjam]

I want to check with a query weather at least one of the peaces of personal name (i.e. Person has first name Anna HR) is not in validation table (i.e HR), and then take some actions on exactly this record. The problem in the script below, is that i'm incorrectly formulating the query so not able not achieve a goal, i.e mark users that have at least one not allowed name. (Also,i'm not sure how to update such record only one's,even if there are more not allowed names, in order to avoid database overload ..but probably if the query will search for at least one not matching name than there is no problem )

Purpose: if the user will have in his name smth which is not allowed(so not listed in validation table...like will type in his first name: HR) then some actions will be taken.

var gr = new GlideRecord('sys_user');

gr.addQuery('first_name','Anna Marie'); //query user with a certain name, one record is enough for me, Anna name is allowed, Marie not listed in val.table for testing purposes

gr.query();

if(gr.next())

{

var name = gr.first_name;

var names = name.split(/-| +/); // spliting user first_name into separate pieces and placing them in array i.e. Anna, Marie   -   in order to query if each peace exists   in other table

gs.print(names[0] +" and other split is " + names[1])//just checking the split

var gl = new GlideRecord('u_validation'); // table where i want to check if each peace of user's name is here.

gl.addQuery('u_pers_name','IN',names); // here is the problem with query, cause it in the end gives strange results WHEN I'M CHEKING IF ITS WORKING. I understand that if user has first name, where at least one peace will be   in this table, than while (gl.next()) will be triggered.

          The problem starts when user has only names that are not in the validation table..like..Marie, Hr..so while (gl.next()) is not working at all), or when in user's name, each peace is present in validation table..then also strange results. I tried to use 'NOT IN'

, 'CONTAINS' but no result

gl.query();

while(gl.next())

{

for(var i = 0; i < names.length; i++)

      {

              if(gl.u_pers_name != names[i])

              {

                  gs.print(names[i]);//just checking what name was not in the table//very strange results here depending on the input combinations//

              }

      }

}

}

PS. in the same way i want to check last name, middle name, so maybe there is more efficient way to check all at ones.

[meia_ahaesy]

You can loop through the records in u_pers_name and check to see if it contains the any of the names in sys_user via the indexOf method.

var gr = new GlideRecord('sys_user');

      gr.addQuery('first_name','Anna Marie');

      gr.query();

if(gr.next())

{

      var name = gr.first_name;

      var names = name.split(' '); // splitting this on a space.

      gs.print(names[0] +" and other split is " + names[1])//just checking the split

      var gl = new GlideRecord('u_validation');

                  gl.query();

        while(gl.next())

        {

                  if(names.indexOf(gl.u_pers_name) == -1)

                  { // the -1 indicates that the value passed in does not have a position within the array.

                            gs.print(names.toString());

                  }

        }

}

Slightly less efficient, but you'll probably get more accurate results, when the data is that variable.

[vidkeer]

Hi,

Am also facing a similar issue. Am trying to join 2 servicenow tables, say for example i have table A having values 1,2, 3, 4 and Table B having 2,3,4,5.

I want to get the list of values which are in table A but missing in table B using servicenow GlideRecord , am getting matching values using this query.

var imptable = new GlideRecord('u_asset_insight_relationship');

imptable.addJoinQuery('cmdb_rel_ci', 'parent.name', 'u_parentname');

imptable.addJoinQuery('cmdb_rel_ci', 'child.name', 'u_childname');

imptable.query();

while (imptable.next())

  {

  var parent = imptable.getValue('u_parentname');

  var child =   imptable.getValue('u_childname');

if(parent.indexOf(parent.name) == -1 &&   child.indexOf(child.name) == -1)

  {

  gs.print(parent + "=" + child);

  }

}

I need the non matching values, this query returns me 2,3,4 as per my above example, But i want 1 to be returned as its present in A but missing in B. Can you please assist

Regards,

Vidya