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

Chris Sanford1 · ‎03-02-2018

Well glad you got it working. I don't think you're even using tcOR at all though, so the lines:

var tcOR = new GlideRecord('time_card');

and

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

could probably be removed at the very least, and at the worst it would pull every record since it's checking for 'null=NULL' at the end of the encoded query. Maybe try commenting out those lines and see what happens?

Robert_Cartwrig · ‎03-02-2018

Update: it turns out that adding OR statements for different fields in one line doesn't work.

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

In the example above from my testing, only the first field is checked, whereas this works fine (other than the fact that it doesn't account for the first condition above):

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

Still working through this last piece...