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

SanjivMeher · ‎03-01-2018

I would suggest building this query in Breadcrum Filter and then use addEncodedQuery to run your query.

Please mark this response as correct or helpful if it assisted you with your question.

Robert_Cartwrig · ‎03-01-2018

The encoded query is returned from the script, or I could start with the encoded query. Why is one better than the other?

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

SanjivMeher · ‎03-01-2018

Since you query is complex, it is always easier to build it in the condition builder and then use it as encoded query.

Please mark this response as correct or helpful if it assisted you with your question.

Robert_Cartwrig · ‎03-02-2018

I agree with the first part, but I still haven't heard a reason why running the encoded string rather than the query is better. They both accomplish the same thing...

What's your line of reasoning here?

SanjivMeher · ‎03-02-2018

It is more accurate and you can very the result directly while building the query on the list view at the same time. I really wont waste my energy building complex query adding AND or OR condition when it can be done easily.

Please mark this response as correct or helpful if it assisted you with your question.