gliderecord two tables

[gabriel_sk]

Hello:)

I am trying to query a table by comparing 2 fields which are not on the same table using GlideRecord. Some of these 2 string fields contain identical data.

The result should display the records of the table2 that don't have matching entry on table1 for the specific field I am trying to query against.

Basically smth like this... but does not work

function onetest1() { 
table1= new GlideRecord('u_table1');
table2= new GlideRecord('u_table2');
table2.addEncodedQuery('field2NSAMEAStable1.field1')
table2.query();
while (table2.next()){
gs.print(table2.field2);
}
}

If I query against 2 fields on the same table, the script works just fine...

I am testing it on Scripts Background... trying to learn how to work with GlideRecord

Any suggestion will be appreciated.

Thanks a lot:)

[Deepak Kumar5]

You have to iterate table1 with the table2 field value. script be like below.

function onetest1() {  

table2= new GlideRecord('u_table2');

table2.addNotNullQuery('field_name2');  

table2.query();

while (table2.next()){

table1= new GlideRecord('u_table1');

table1.addQuery("Field_name1","table2.field_name2");

if(!table.hasNext()){

gs.print(table1.Field_name1);

}

}  

}  

[Pradeep Sharma · Accepted solution]

Hello Gabriel,

Please go through the below script for reference. The below script will compare short_description field of incident and problem and return short description of problem records that are not matched.

var arr = [];
onetest1();
function onetest1() {     
var gr= new GlideRecord('incident');   //Pass table 1 name here
gr.addNotNullQuery('short_description'); //Pass field name here
gr.query();
while(gr.next())
{
arr.push(gr.short_description.toString()); //Replace short_description with the exact field name to be matched
}
}
for(i=0;i<arr.length;i++)
{


var gr1 = new GlideRecord('problem'); //Pass table name 2 here
gr1.addQuery('short_description',arr[i]); //Pass table name 2 field column name that has to be matched with table name 1 field
gr1.query();
while(!gr1.next())
{
gs.addInfoMessage(gr1.short_description); //Replace short_description with the exact field name here
}
}   

Reference:

http://wiki.servicenow.com/index.php?title=GlideRecord

[gabriel_sk]

Thank you all for your kind support, I was able to take inspiration from the Pradeep suggestion and have it working...

only thing on line 19 should be while(gr1.next()) ) without "!"   otherwise would not work.

I have then created a script include that generate an array of the fields on the second table and use it to filter a reference field (this was my ultimate goal).

The script is much shorter but works fine   if could be useful for anyone else, here it is (I use it as reference qualifier in a reference field to filter the incident field).

arr = [];
function oneTest1() {
       gr = new GlideRecord('u_table1');
gr.query();
while (gr.next()){
       arr.push(gr.u_field.toString());

}
       return 'numberNOT IN' + arr;
}       

Thanks a lot:)

[yuga bhosale]

Thank you so much Pradeep. I had same kindof requirement. And the above code was helpful.😊