This is adapted from a partial excerpt from my article: Broken Queries & Query Business Rules in ServiceNow, on SN Pro Tips. The first couple of paragraphs of the article focus on whether query Business Rules could/should replace ACLs; which I believe they should not except in certain rare circumstances. They can often be a performance-enhancing supplement to ACLs, though.
I had an issue recently, where a user reported that even with no query on the sc_req_item table, they could not see a specific RITM that they were certain was created. After verifying that the issue did indeed exist (even for an admin), I did some digging and found that a query Business Rule was the culprit. This lead me down a rabbit hole which eventually lead to the article mentioned above, and to this Community post. However, I should mention that this issue is not specific to query business rules, but applies to any query which contains an "is not equal to" component.
Here's the really quick "TLDR" version: If you run an "[field] is not [value]" query on a given field, then it behaves as though your query was "is not blank AND is not [value]".
Longer version:
Imagine you have a table with the following records:
| Number | Material | Description |
| 01 | Suede | Blue suede shoes |
| 02 | Leather | Leather loafers |
| 03 | Silk | Silk sandals |
| 04 | Barefoot | |
| 05 | Metal | Metal moccasins |
Most of the records have a value in the Material field, but Barefoot [04] doesn't. This is perfectly fine, as Material is not a required field.
Now, let's introduce a Query Business Rule that contains the code:
//Only allow admins to see my blue suede shoes
if (!gs.hasRole('admin')) {
current.addQuery('material', '!=', 'Suede');
}This query results in the following records being returned for anyone who isn't an admin:
| Number | Material | Description |
| 02 | Leather | Leather loafers |
| 03 | Silk | Silk Sandals |
| 05 | Metal | Metal moccasins |
Now, anyone who isn't an admin will not be able to see my Blue Suede Shoes in that table. -- But wait, what's this? Where is Barefoot [04]?
A blank string "" is not equal to the string "Suede"; not even loosely equal. If we're worried we're going insane, we can even test this by running the following line of code:
console.log('' != 'Suede');(which of course, prints true).
But SQL doesn't care about your basic logic, because adding a "not equal to" query on any string field, filters out all blank values!
Why? I assume because SQL hates you is why. At least, I assume - I was unable to find any other explanation for this behavior.
But let’s say you’ve thought about it carefully, and a query Business Rule really is the best solution for what you’re trying to do. How do you get around this SQL weirdness, so you can add a “not equal to” query, using a query Business Rule? Well as it happens, you can do so in the same way that the platform does for you automatically (sometimes)!
If you have a table of shoes just like the one in the above example, and you use the query builder to do an "is not" query, you run into the same issue where records where that field is blank don't show up.
BUT - if instead of using the query builder, you right-clicked on “Suede” in the Material column, and clicked on Filter out, you would get the results you’d expect:
| Number | Material | Description |
| 02 | Leather | Leather loafers |
| 03 | Silk | Silk sandals |
| 04 | Barefoot | |
| 05 | Metal | Metal moccasins |
It works! — but why? What is this sorcery!?
What’s going on is that even though the UI shows you a neat little breadcrumb that just says “Material != Suede”, the platform pulled a sneaky on ya! If you expand the filter builder, you’ll see that the actual query says “Material is not Suede or Material is NULL”.
Taking a hint from this behavior, we can resolve the anti-logic issue whenever we want to add an “is not” query through a query Business Rule or even through the query builder on a list, by also adding an “or condition” that says “or [field name] is [‘NULL’]” - and yes, actually passing in the string “NULL”. See the following Business Rule script for an example:
(function executeRule(current, previous /*null when async*/) {
current.addQuery('u_material', '!=', 'Suede').addOrCondition('u_material', 'NULL');
})(current, previous);This issue (and solution) are not specific to query Business Rules, or even to ServiceNow - This is regular old everyday SQL behavior. If you have a filter that says “[some field] is not [some value]” and you want blank values to show up in the results, you must add “…or [that field] is ‘NULL’”.
NOTE: You could also use “…is” followed by a blank string, or “…is empty” (instead of “…is ‘NULL’”), but it’s worth mentioning that only “…is ‘NULL’” hides the sub-condition to make the query breadcrumb look nice and succinct. This doesn’t matter as much for query Business Rules though, as their conditions don’t show up in the condition builder.
Feel free to check out my other articles, over at https://snprotips.com/archive/
