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]

Yep...still ignoring the OR conditions.

Looking through your suggestion, the thing that pops out is that there are two "new GlideRecords" for the same query. I haven't seen this method used before.  Before I spin my wheels too much, do you know this to work?

I also see an issue with the tc queries...you eliminated the tccOR variable, so now you have two add queries, then three OR addOrConditions all on the same variable (tcOR).  I think the format should be one addQuery, then addOrConditions.  Unfortunately, the first add Query is not in a OR situation, so it should be seperated by a different variable (and new GlideRecord if that is the proper way to add multiple ORs in a query).

Also, the only query in this version was for tc...I assume with three "new GlideRecords," there would need to be three queries.  There is also only on encoded query for the first "new GlideRecord," so the encoded query will only be for each "new GlideRecord."

This doesn't look right.

I have a suggestion to change this to make it easier to read and use only one variable...

[Robert_Cartwrig]

I don't think using more than one "new GlideRecord" is the way to go with this...wouldn't that generate three separate encoded strings rather than one combined one?

I changed the script a bit to remove the extras and use only one variable, and hopefully simplify in the process. It seems to return the expected number.  Here's the change I made:

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

var tc = new GlideRecord('time_card');

tc.addQuery('state','Submitted');
tc.addQuery('category','project_work').addOrCondition('category','');
tc.addQuery(pf, '').addOrCondition(port, 1).addOrCondition(port, 2).addOrCondition(port,'');


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();
}

[Robert_Cartwrig]

Interesting note on the encoded string:

*** Script: tc Query: state=Submitted^category=project_work^ORcategory=^top_task.top_portfolio=^ORtop_task.top_portfolio.category=1.0^ORtop_task.top_portfolio.category=2.0^ORtop_task.top_portfolio.category= = 17

It populated the port values with a decimal. I saw that at one point.  I never would have added decimals if writing an encoded string manually.  That seems like a good argument to build queries instead of manually writing encoded strings...

[Chris Sanford1]

Ok well the conditions your query would return would be:

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

So yes if you're only looking for records where all three of those bullet points are true, then that's the way to go. I just didn't understand your requirements.

[Robert_Cartwrig]

And you were using multiple "new GlideRecord" with only one being queried. I don't know that mutliple "new GlideRecords" wouldn't work...just never tried that...but the query was only for tc, not the tcOR.

Still, I wouldn't have gotten this far without your help! Thank you!