addQuery operators

[john_roschella]

What are the addQuery operators...are 'in' and 'not in'supported? What about "Exists or Not Exists'?

Is there a way to build multiple table queries in Service-now

I want to get a list of problems that do not have any open incidents associated to it.

Select distinct problem.number from problem where
problem.number not in (select distinct problem.number from problem, incident where problem.number = incident.problem_id and incident.incident_state not in ('8','9'));

Can something like this be done?

[#2]

Hi John, in addition to .addQuery(), there is also .addNullQuery(field) and .addNotNullQuery(field) -- these two search where field is null/not null.

However, that doesn't really help you with the query above. I don't see any way to build that filter. The only solution I can think of is to write a UI Action that writes to the log all problems that do not have open incidents associated with them:
//--------------------------------
var probs = "";
var inc = new GlideRecord("incident");
inc.addQuery("incident_state", "!=", "7");
inc.addNotNullQuery('problem_id');
inc.query();
while (inc.next()) {
probs = probs + inc.problem_id.number + ",";
}
var pb = new GlideRecord("problem");
pb.addEncodedQuery("numberNOT IN" + probs);
pb.query();
while (pb.next()) {
gs.log("XXX: " + pb.number);
}
//--------------------------------------

You will want to clean this up some, like store the list of problems associated with an open incident in an array, sort the array and remove duplicates.

I hope this helps!
Deborah Kearney

[john_roberts]

To build on Deborah's comment....
You could modify this script to set a new boolean field on the problem record if any incidents are linked (or not linked, which ever makes since). Run the script once to properly flag all current problems.
Then you'll need a business rule on incident to capture change in problem field (current.problem_id.changes()). If problem field is populated from null you need to set the problem boolean field. If an incident problem field is cleared or changed, you'll need to query other incidents to see if the previous problem (previous.problem_id) is linked to any other incidents and update the flag as needed.

[#4]

I tried using the .addNullQuery(field) and .addNotNullQuery(field) in a client script and could not get any results. Does this not work in a client script?

[john_roberts]

They will not work in a client script. The GlideRecord on the client is very lightweight so you'll have to stick with the addQuery method.