Business rules that use the “Advanced” checkbox are frequently set to run on Insert or Update, but there are a couple of other options available. One of them is Query.
Here is a look at one of the uses of a Query business rule, specifically one that is set to run before Query.
Example Use Case
First, here’s an issue you might have noticed occasionally:
A user performs a search and sees a message at the bottom of the screen “Number of rows removed from this list by Security constraints: (some number)”.
Let’s see one way that could happen:
In this example, a user (Joe) runs “My Knowledge Articles” and gets nothing in return:
Joe clicks on the word “All” to show him every article:
However, the bottom of the list results has a message: “Number of rows removed from this list by Security constraints: 5”. When Joe counts the records on the screen there are only 15, not 20 records on the page, as it would seem there should be.
The next page has a similar message, with 2 rows missing.
In total 7 rows are missing, and Joe is shown 33 records even though the list total says 40.
Joe is shown all the articles he has the right to see, but the extra information returned on what he doesn’t have the right to see can be confusing: the “rows removed” messages, along with the total shown of 40. (There are 40 knowledge articles in the database, but Joe only has access to a total of 33 of them.) This is what can happen in some situations when ACLs are used to control access to rows of a table.
What happened?
In this example, by looking at the articles as Administrator, it was seen that the 7 “missing” articles were either not published (thus not supposed to be available to Joe) or were in knowledge bases to which Joe didn’t have access.
How a Before Query Business Rule can help
A business rule set to run “Before” on “Query” can be used to add criteria to the search being run. In the example with Joe, if knowledge articles are being searched, we can tell it to
- Only look for Published articles
- Only look for articles in knowledge bases to which the user has access
The business rule adds these extra conditions before the search actually runs on the database. If properly written, the previously missing rows won’t be counted and things like ACL restrictions don’t come into play, because the user won’t be asking for records to which they do not have access.
Building the Rule
- Open the business rule list from the table you want it to run on.
- Click “New”
- Fill out the needed fields:
- Name
- Check the Advanced checkbox
- Select the “When to run” tab if not already selected
- The Query checkbox should be showing now. Check it.
- Set the “When” field to “before” if it’s not already selected.
- Click “Save” to save this business rule!
- Click on the Advanced tab of the business rule. Here is where we will put the code that will append to the query that will make it return only the records we need.
- Put a condition on the rule, and/or in the Script body.
This is important. You are about to amend the query that the user is running, so be sure this rule only runs for users who need it! Otherwise, you will be blocking access to records to potentially everyone on the system.
In this example, we’re keeping it simple by saying this only runs if you’re not in the admin role and if you are a real, logged-in user making the query (as opposed to an API integration). You may want different criteria for your situation.
- Get the query you want to append in the Script field. A good way to have just what you want is to use an encoded query, which you can build by copying a search filter. In this example, we went back to the Knowledge Article search and filtered to only get articles that are Published and are not part of two test Knowledge Bases to which our users would not have access.
- Click “Run” to run the query, making sure it returns exactly what the users should see.
- Right-click on the last part of the query and select “Copy query”. We can use this in our script.
- Back on the business rule, put your query into the Script field. In this example, we’re putting in
Current.addEncodedQuery(“query you copied from the knowledge article search”);
- The Advanced tab will look like this:
- Save the business rule!
Seeing what this does
- Test to make sure this does not impact unintended users. In this case, the administrator was exempt, and it was confirmed the administrator could still see 40 records.
- Test as a user that should be impacted:
Joe runs a search to find all the Knowledge articles he can view.
Now Joe is told there are only 33 records, which is all to which he has access. All 33 are returned without any confusing messages being given.
Conclusion
Before Query business rules can be useful, as shown in the given example. A few notes of caution:
- Be careful that the Query business rule is only impacting the users you intend to impact, so you are not unintentionally altering searches for other users.
- Remember that business rules run on database activity, and they are not dependent on whether a person is on a particular page or view.
- API interactions can trigger business rules, so put a condition in to run only for interactive user sessions if that is all you need.