Including an OR statement in a table query

[LRhodes]

Hi all,

I'm having a bit of a brain fog moment and I'm struggling to search the correct terms to what is probably a really simple amendment. I have the below script. In context we use this script on a menu item on the Service Portal to display a list of a users active Incidents. I have amended this to try and include if they're in the u_on_behalf_of field also.

So, I want to return Incidents that are active, and the current user is in the Caller of On Behalf of fields. I've amended it as below however it breaks the query and I just get all active Incidents regardless of user. Any help greatly appreciated.

var inc1 = new GlideRecord('incident');
//inc1.addActiveQuery();
inc1.addQuery('active',true)
inc1.addQuery('caller_idORu_on_behalf_of', gs.getUserID()); //edited to include on behalf of
inc1.orderByDesc('sys_updated_on');
inc1.setLimit(max);
inc1.query();

[Weird · Accepted solution]

Or condition works in a very different way in that case and can be confusing. I'd just recommend using encoded queries instead. You can basically write the condition in a condition builder (like you do on a list), right click it and select copy query. You can then paste the query inside addQuery(""); and modify it accordingly.
Here's an example where I'm saying to query active incidents where caller or on behalf of is current user.

var user = gs.getUserID();
var inc1 = new GlideRecord('incident');
inc1.addQuery("active=true^caller_id=" + user +"^ORu_on_behalf_of=" + user);
inc1.orderByDesc('sys_updated_on');
inc1.query();

When copying the query from condition builder it can contain a sys_id for the user, so you have to tweak it a bit.

in this case I created a variable user to shorten gs.getUserID() a bit.
Then I replaced the sys_id part with user
So

"active=true^caller_id=08cf21ad1b92e910f3b21022b24bcbfe^ORu_on_behalf_of=e9f3bc5878b8ed004f8b3a7d131a160b"

becomes more dynamic 

"active=true^caller_id=" + user +"^ORu_on_behalf_of=" + user

Edit:
For reference here's your query with the "standard" or condition

var inc1 = new GlideRecord('incident');
inc1.addQuery('active',true)
inc1.addQuery('caller_id', gs.getUserID()).addOrCondition('u_on_behalf_of', gs.getUserID());
inc1.orderByDesc('sys_updated_on');
inc1.setLimit(max);
inc1.query();

Here the addOrCondition is added on to addQuery to add a or condition.
In my opinion this becomes harder to write and it's faster to use encoded queries.

[LRhodes]

Thanks Weird for the explanation. I've adopted this and it has worked perfectly - thank you again.

[Sandeep Rajput]

@LRhodes Please update your script as follows.

var inc1 = new GlideRecord('incident');
//inc1.addActiveQuery();
inc1.addQuery('active',true)
inc1.addQuery('caller_id', gs.getUserID()); //edited to include on behalf of
inc1.addOrCondition('u_on_behalf_of', gs.getUserID()); 
inc1.orderByDesc('sys_updated_on');
inc1.setLimit(max); //max is not defined anywhere in the above lines
inc1.query();

You can read more about addOrCondition here https://developer.servicenow.com/dev.do#!/reference/api/tokyo/server/c_GlideQueryConditionScopedAPI

Also, please be informed that on line number 6 

inc1.setLimit(max);

You are setting the limit via a variable max which isn't defined anywhere, this might break your script during execution.

Hope this helps.