GlideRecord addQuery on another table

[georgechen]

Hi folks,

Just want to know if GlideRecord supports the cross field checking on a date field.   for example,   Task_sla relates to task table, and I am querying task_sla records that have the same created_date as task,     task_sla.sys_created_on = task.sys_created_on,

Following is the script

var grTask = new GlideRecord('task_sla');

var queryStr = 'sys_created_on=task.sys_created_on';

queryStr += '^active=false';

queryStr += 'task.sys_class_name=u_finance';

queryStr += 'task.met_sla=false'

grTask.addEncodedQuery(queryStr);

gs.log(grTask.getRowCount());

it returns 0 record, which is incorrect.   Could anyone help?

Your response would be appreciated

[Rahul Jain11]

Hi,

This returns 0 because the sys_created_on task_sla might note be same as sys_created_on on the referenced task record in task.sys_created_on.

var queryStr = 'sys_created_on=task.sys_created_on';. In this your are querying where sys_created_on on the task_sla table equal to sys_created_on in referenced record, which might not return any record.

[georgechen]

Thanks Rahul,   I am not so sure how to specify the query string on the date comparison, but I implemented a nest loop below, to illustrate what I try to achieve.

var grTask = new GlideRecord('task_sla');

//var queryStr = 'sys_created_on=task.sys_created_on';

queryStr += '^active=false';

queryStr += '^stage!=cancelled';

queryStr += '^task.sys_class_name=u_finance';

grTask.addEncodedQuery(queryStr);

gs.log(grTask.getRowCount());

while (grTask.next()) {

                      var grFinance = new GlideRecord('u_finance');

                      var queryFinance = 'met_sla=false';

                      queryFinance += '^sys_created_on=' + grTask.sys_created_on;   // this may cause an issue,

                      grFinance.addEncodedQuery(queryFinance);

                      grFinance.query();

                                                    while (grFinance.next()) {

                                                              gs.print('created date found mathced on finance and sla task tables, Finance#' + grFinance.number);

                                                      }

}

[Pradeep Sharma]

Hi George,

Your encoded query is not correct. Please refer the below wiki for more info on encoded query strings.

Encoded Query Strings - ServiceNow Wiki

[georgechen]

Hi Pradeep,

Thanks for your response.     I read the wiki, and found the grTask.query() was missing, and also the encoded query string,

yet it is still returning 0 records.

var grTask = new GlideRecord('task_sla');

var queryStr = 'sys_created_on=task.sys_created_on';

queryStr += '^active=false';

queryStr += '^task.sys_class_name=u_finance';

grTask.addEncodedQuery(queryStr);

grTask.query();

gs.log(grTask.getRowCount());