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

[Chris Sanford1 · Accepted solution]

If I understand what you're trying to accomplish, you want any record that would meet the conditions specified in 'tc' as well as one of 'tcOR', or 'tccOR'. And I'm assuming that those variables at the top are dot-walked fields from the time_card table, and user, top_task, state and category are all fields on the time_card table. So correct me if I'm wrong about those assumptions. I don't think you can accomplish what you're trying to with your 'user !=approver' condition when writing the query, at least not easily. You should check that condition after querying.

Try this:

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');

//tc.addQuery('user', '!=', approver);
tc.addQuery('state','Submitted');
tcOr.addQuery('state', 'Submitted');

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

tcOR.addQuery('category','project_work');
tcOR.addOrCondition('category','');

tc.addEncodedQuery('^NQ', tcOR.getEncodedQuery());

tc.query();

var count = 0;
while(tc.next()) {
   var porta = tc.top_task.project_manager;
   if(porta != tc.user) {
      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();
}

As has been mentioned you could build one long encoded query string, but I do think this is more readable.

Edit: I also uncommented your line right before the update at the end. If you're wanting to test the code I would comment out the 'tc.update();' line instead.

[Robert_Cartwrig]

I'm going through this and am about to try it.  I'll let you know how it goes.

That being said, I'm of the same mind regarding encoded strings.  It's difficult enough to put together a lot of conditions (and this is actually only part of the script). I find using addQuery is easier for me to arrange my thoughts while I'm putting all that together...though I suppose I could do all that separately and just write it as an encoded string.  I just find the first easier when it's not simple.

That being said, I'm open to trying other options - if there is an actual benefit to doing so.  Otherwise, it just sounds like 6 one way and half a dozen the other...

[Robert_Cartwrig]

Interesting...I kept getting "top_task" is not defined when not using the getRefRecord..didn't get it here, but it did fail on line 9 saying that the variable tcOR is not defined (which is what I usually see when I separate the OR condition from the query...I put them back together and it runs...but it's still turning up a different (larger) number than I expect - as did my version, so I don't know if the OR's are working still..

About to go through it with a fine tooth comb, but here's what I changed:

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');

//tc.addQuery('user', '!=', approver);
tc.addQuery('state','Submitted');

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

tcOR.addQuery('state', 'Submitted');
tcOR.addORCondition('category','project_work');
tcOR.addOrCondition('category','');

tc.addEncodedQuery('^NQ', tcOR.getEncodedQuery());

tc.query();

var count = 0;
while(tc.next()) {
   var porta = tc.top_task.project_manager;
   if(porta != tc.user) {
      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]

Ah, you got that error due to a capitalization typo, it should have been 'tcOR.addQuery' instead of 'tcOr.addQuery'. Strange that you got too many records. So the value of count being printed is to large? Maybe I'm not understanding the requirements correctly this would update any records with:

• state is submitted
• pf is blank or port is one of 1, 2, blank

OR:

• state is submitted
• category is project_work or blank

And after the query is performed it checks the user is not the same as top_task.project_manager and only calls putName on the ones where that condition is met. So the query itself will pull rows without that condition, but they will not get updated.

[Robert_Cartwrig]

Not quite.  These are the conditions:

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