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

The line

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

joined the two together, so after that line tc should have had the query for any of the conditions of what was previously in tc, OR the conditions of tcOR. Glad you got it working just wanted to point out that that solution is a good way to solve, had your requirements been different. If you have query logic like (A and B or C) OR (D and E or F) that's a good solution, but I guess your requirements were more along the lines of (A or B) AND (C or D), so it was unnecessary in your case.

Robert_Cartwrig · ‎03-02-2018

Oh...thanks for the explanation! That makes sense. I'll have to try it out!

Robert_Cartwrig · ‎03-02-2018

Hmmm...it doesn't seem to be adding them together. It's only using the first query...

Here's the whole script:

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('state','Submitted');
tc.addQuery('category','project_work').addOrCondition('category','');

tcOR.addQuery(port, '1').addOrCondition(port,'2').addOrCondition(port, '');
tcOR.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();
}

And here's the encoded string that is returned:

state=Submitted^category=project_work^ORcategory=^NQnull=NULL^EQ

Robert_Cartwrig · ‎03-02-2018

To be fair, it's probably balking on the OR conditions on different fields...I haven't gotten this to work yet...guess I could build a third OR...

Chris Sanford1 · ‎03-02-2018

Oops, syntax error. Should be:

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

Maybe try my script again, correcting that line and see if it works. Sorry about that, it's difficult to test code for a table that's not in my instance.