Or conditions not working in Background Scripts

Robert_Cartwrig · ‎03-01-2018

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 · ‎03-01-2018

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.

View solution in original post

purbalipc · ‎03-01-2018

What does this line returns-

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

Also isn't it simple to make addEncodedQuery instead of so many lines?

Robert_Cartwrig · ‎03-01-2018

This returns the encoded query, in my case:

Script: tc Query: user!=approver^state=Submitted^port=1^ORport=2^ORport=^ORtc.tt.top_portfolio=^tc.category=project_work^ORtc.category= = 61

And I suppose you could. I could also write the query as one line as I did in my other example. What's the difference?

Michael Fry1 · ‎03-01-2018

These lines don't look right:

tcOR.addOrCondition('pf',"");
var tccOR = tc.addQuery('tc.category','project_work');
tccOR.addOrCondition('tc.category','');

you're trying to add an addOrCondition on pf, when you don't have an addQuery.

the other 2 lines have tc.category, where tc is the this whole script .Shouldn't those just be category, versus tc.category?

Robert_Cartwrig · ‎03-01-2018

It's subtle, but I think you missed a slight change in the variable. These are different queries.

Maybe this will help:

//-- addQuery 1 -------------
var tcOR = tc.addQuery('port',1);
tcOR.addOrCondition('port',2);
tcOR.addOrCondition('port',"");
tcOR.addOrCondition('pf',"");

//-- addQuery 2 -------------
var tccOR = tc.addQuery('tc.category','project_work');
tccOR.addOrCondition('tc.category','');

The first was short for "time card OR statement." The second,"time card category OR statement."