Tip For Creating Complex Before Query Business Rules

Before query business rules are great! I absolutely love them and use them all the time.

Sometimes though, I do find myself getting into a bit of a twist with the logic in the code.

Before I show you what I do now to overcome it, i’ll quickly explain what a before query business rule is. Put simply, it’s exactly as the name implies. It’s a business rule that runs before querying the database. More specifically, it can add additional query parameters to the search automatically and invisibly to the user.

For example, if a user wants to view all users, they can go to the sys_user table with no query parameters.

The system will essentially do the following code to bring back the results:

var current = new GlideRecord('sys_user');
current.query();

Now, if you want to only allow users to view active users, this is where a before query business rule comes into play. If I gave you the above script and asked you to only return active users, you would amend the code to be:

var current = new GlideRecord('sys_user');
current.addActiveQuery();
current.query();

A before query business rule is no different. You’d create the business rule and add the line current.addActiveQuery() to the body and that’s it (side note, a before query business rule is one where the ‘when’ field is set as before and the ‘query checkbox is ticked’). So essentially, the before query business rule is made to add additional query conditions to the query GlideRecord. The above example can be seen in the business rule called ‘user query’ out of the box on the sys_user table.

Now that’s out of the way, adding a simple parameter like above to a query is simple, but when you have 5/6 different parameters and different conditions for each one, and different values, things gets a little/lot more complicated.

The way I get around that is simple.

    • I first go to the list view of the table I am running the before query business rule on
    • I then use the out of the box filter to build the conditions I want with all the Ands and Ors I like
    • Once I’ve verified that the query is returning the correct results, I right click on the breadcrumb and click copy query. For example, it might be:
      nameSTARTSWITHahmed
      ^ORnameENDSWITHr
      ^location=f90e06af0a0a0b9100a6defd15c548e9
      ^ORlocationISEMPTY
      ^employee_numberISNOTEMPTY.
      This query brings back all users whose name starts with Ahmed, or name ends with r, and a particular location or no location, and who do not have an employee number. (obviously not a real life example!)Screen Shot 2015-07-29 at 12.36.35

      • Paste that code into the business rule and manipulate the string to be as dynamic as you like:

      var str = 'nameSTARTSWITHahmed^ORnameENDSWITHr';
      str += '^location=' + gs.getUser().getLocation() + '^ORlocationISEMPTY';
      str += '^employee_numberISNOTEMPTY';

      • Once you’ve built the query up however you like, you can then add it the current query GlideRecord using the standard API current.addEncodedQuery(str);

      This I find makes it super easy to create your before query business rules. But the next step is testing it to make sure it works. What I like to do (apart from lots of impersonations back and forward), is at the final line do a gs.addInfoMessage('Query is: ' + current.getEncodedQuery());

      This will print out the final encoded query that was built as an info message when navigating to the table.

      Once you’ve taken it, deactivate the business rule and then copy this encoded query and paste it into the url as a query parameter like this: https://fakeinstance.service-now.com/sys_user_list.do?sysparm_query=<the encoded query>

      If it’s been built correctly and a valid query string, you will see the filter is populated with exactly what you want it to search with, giving you a visual indication whether you’re before query business rule is running as intended or not.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s