"addQuery" or "IF" condition to check whether "TABLE_A.FIELD_A.sys_id = TABLE_B.FIELD_B.sys_id" or not.

[Aki17]

I would like to compare the sys_id of 2 fields of different table for each.

Could someone please tell me how to describe "addQuery" or "IF" condition to check whether "TABLE_A.FIELD_A.sys_id = TABLE_B.FIELD_B.sys_id" or not?

Best Regards,

Aki

[OlaN]

Hi,

would something like this work?

if (record_A.getValue('field_A') == record_B.getValue('field_B'))
{
  gs.info('same values')
}
else
{
  gs.info(record_A.getValue('field_A') + ' different from ' + record_B.getValue('field_B'));
}

[shloke04]

Hi,

You can do something like below:

var gr = new GlideRecord('Table 1 Name');
gr.addEncodedQuery();
gr.query();
while(gr.next()){
var check = compareWithTable2(gr.FIELD1);
gs.info('Value matched or not matched ' + check);

}

function compareWithTable2(FIELD_SYS_ID){ // FIELD_SYS_ID is the sys id of Field present in table 1
var gr1 = new GlideRecord('Table 2');
gr1.addQuery('FIELD',FIELD_SYS_ID);
gr1.query();
if(gr1.next()){
return true;
}else{
return false;
}
}

Script shared above will ensure if the id match then it will accordingly print you True and if they do not match it will print false.

Just replace the table Name and query for which you want to compare the values between the tables.

Hope this helps. Please mark the answer as correct/helpful based on impact.

Regards,
Shloke

[Hitoshi Ozawa]

It depends on the objective. sys_id's are suppose to be unique so it won't make too much sense comparing sys_id's of independent tables together. One case that I know of when this is required is with sys_attachment table which contains table_sys_id.

So, if the objective is to retrieve a sys_attachment record or to check if an attachment exists, I'll use the following code. Can't use .get() because have to query on both table name and table sys_id.

function retrive_attachment(table_name, table_sys_id) {
  var gr = new GlideRecord('sys_attachment");
  gr.addQuery('table_name', table_name);
  gr.addQuery('table_sys_id', table_sys_id);
  gr.setLimit(1);
  gr.query();
  if (gr.next()) {
    return gr.sys_id;
  }
  return;
}

.addQuery() is used to retrieve a record. If condition is used to compare values. Using "if" condition to compare sys_id's would imply these records from these 2 tables were previous retrieved. Unless the tables are small and values are previously read in to a hash to be used repeatedly, it would be better to just use .addQuery() to try to retrieve 1 record.

That is, don't do like the following code.

function retrive_attachment(table_name, table_sys_id) {
  var gr = new GlideRecord('sys_attachment");
  gr.addQuery('table_name', table_name);
  gr.query();
  while(gr.next()) {
    if (gr.table_sys_id == table_sys_id) {    
      return gr.sys_id;
    }
  }
  return;
}

[Hitoshi Ozawa]

BTW, to check if a record has an attachment or not, can use .hasAttachments()

function hasAttachment(table_name, sys_id) {
  var gr = new GlideRecord(table_name);
  if (gr.get(sys_id)) {
    if (gr.hasAttachments()) {
      return true;
    }
  }
  return false;
}