Or conditions not working in Background Scripts

[Robert_Cartwrig]

Hi All,

I've been trying to have a background script find some records (to make a change).  I'm using addOrCondition, but it refuses to cooperate, effectively ignoring any query filter using this. The script works in that it returns a list of records that meet the criteria, but when I investigate the results, it includes records that do not meet the OR condition.

Here is the script I am using:

var tc = new GlideRecord('time_card');

var usr = user.getRefRecord();
var tt = tc.top_task.getRefRecord();
var pf = tc.tt.top_portfolio.getRefRecord();
var port = tc.tt.pf.category;
var approver = tc.tt.project_manager.getRefRecord();


tc.addQuery('user', '!=','approver');
tc.addQuery('state','Submitted');
var tcOR = tc.addQuery('port',1);
tcOR.addOrCondition('port',2);
tcOR.addOrCondition('port',"");
tcOR.addOrCondition('pf',"");
var tccOR = tc.addQuery('tc.category','project_work');
tccOR.addOrCondition('tc.category','');


tc.query();

gs.print('tc Query: ' + tc.getEncodedQuery() + ' = ' + tc.getRowCount());

while(tc.next())

{
var porta = tt.project_manager.getRefRecord();
putName(porta)
}

function putName(porta)
{
tc.setWorkflow(false);
tc.autoSysField(false);
gs.addInfoMessage(tc.sys_id);
//tc.u_approver = porta.sys_id;

tc.update();
}

I've also tried using the following option for lines 12-15 (and similar for 14, 15):

tc = addQuery('port','1').addOrCondition('port','2').addOrCondition('port','').addOrCondition('pf','');

 They both behave the same.  If I take the OR conditions off and just use the query, it works.

Yes, I know I don't need to use all the vars repeatedly...I've just been trying everything I can think of to make sure everything is pulling the correct data and it seems to be...other than the OR conditions.

What am I doing wrong?  To avoid swirl, please post only in regards to the OR condition issue. 

I've already been to the following pages:

• https://www.servicenowguru.com/scripting/gliderecord-query-cheat-sheet/
• http://www.servicenowelite.com/blog/2014/1/17/how-to-use-background-scripts
• https://docs.servicenow.com/bundle/istanbul-application-development/page/app-store/dev_portal/API_reference/glideRecordScoped/concept/c_GlideRecordScopedAPI.html
• many more...

Thanks in advance,

Robert

[Robert_Cartwrig]

I tried with the change you suggested and it adds them together in the encoded string:

state=Submitted^category=project_work^ORcategory=^top_task.top_portfolio.category=1^ORtop_task.top_portfolio.category=2^ORtop_task.top_portfolio.category=^ORtop_task.top_portfolio=^NQnull=NULL^EQ

This is a fully working script in one piece!

Final answer:

var pf = 'top_task.top_portfolio';
var port = 'top_task.top_portfolio.category';


var tc = new GlideRecord('time_card');
var tcOR = new GlideRecord('time_card');
//var tccOR = new GlideRecord('time_card');
tc.addQuery('state','Submitted');
tc.addQuery('category','project_work').addOrCondition('category','');
tc.addQuery(port, '1').addOrCondition(port,'2').addOrCondition(port, '').addOrCondition(pf,'');

tc.addEncodedQuery('^NQ' + tcOR.getEncodedQuery());
tc.query();

var count = 0;
while(tc.next()) {
   var porta = 'top_task.project_manager';
   if(user != porta) {
      putName(tc, porta);
      ++count;
   }
}

gs.print('tc Query: ' + tc.getEncodedQuery() + ' = ' + count);

function putName(tc, porta)
{
tc.setWorkflow(false);
tc.autoSysField(false);
gs.addInfoMessage(tc.sys_id);
//tc.u_approver = porta.sys_id;

tc.update();
}

[Chris Sanford1]

And that works for you? I'd be surprised since you don't add any conditions to tcOR before merging it with tc. I would think that would query every record?

[Robert_Cartwrig]

I don't think I can use a different encoded string for the same OR condition on different fields, that is will just separate the OR to make them each distinct conditions...I think it all needs to be part of one string.

Here are the conditions again:

1. time_card State = Submitted
2. time_card Category = NULL OR project_work
3. top_task.top_portfolio = NULL OR top_task.top_portfolio.category = 1 OR 2 OR NULL
4. time_card User != top_task.project_manager

Item 3 is the piece that fails when in a single OR. The first piece and second piece don't play well together.  I can query for each separately, but not together.

[Chris Sanford1]

Yeah, and the distinct conditions is what I thought you wanted. It is still possible with an encoded query, you would just replace the '^NQ' with simply '^', but it's not necessary with those conditions.

So have you figured it out or are you still having trouble?

[Robert_Cartwrig]

Yes...even with the two fields in one line.

Here is the full script (though I do have the actual action comments out until I'm ready to run it):

//THIS WORKS - Project


var pf = 'top_task.top_portfolio';
var port = 'top_task.top_portfolio.category';


var tc = new GlideRecord('time_card');
var tcOR = new GlideRecord('time_card');
//var tccOR = new GlideRecord('time_card');
tc.addQuery('state','Submitted');
tc.addQuery('category','project_work').addOrCondition('category','');
tc.addQuery(port, '1').addOrCondition(port,'2').addOrCondition(port, '').addOrCondition(pf,'');

tc.addEncodedQuery('^NQ' + tcOR.getEncodedQuery());
tc.query();

var count = 0;
while(tc.next()) {
   var porta = 'top_task.project_manager';
   if(user != porta) {
      putName(tc, porta);
      ++count;
   }
}

gs.print('tc Query: ' + tc.getEncodedQuery() + ' = ' + count);

function putName(tc, porta)
{
tc.setWorkflow(false);
tc.autoSysField(false);
gs.addInfoMessage(tc.sys_id);
//tc.u_approver = porta.sys_id;

tc.update();
}