Simplify Queries with Encoded Queries

Tutorial on using encoded queries to simplify complex GlideRecord queries.

#GlideRecord , #GlideRecord Query , #Query , #Scripting , #Server Side Scripting , #Encoded Query

Ben Sweetser

6 minute read

We are experimenting with new training content delivery methods. This tutorial blog post is one of those experiments. We are very interested in your feedback. Please let us know what you think about this format and the content in the comments below.

Introduction

With GlideRecord queries, you can find any set of records you need in ServiceNow. Not all queries are easy to write, though, especially if you need to work with date fields or OR operators. Lists have a condition builder to create more complex query logic.

Developers can use the addEncodedQuery() method to take the encoded query from a filter and apply it in a script. The topics in this post build on concepts in the GlideRecord concepts in the Server-side Scripting module.

In this post, you create a Business Rules that uses an encoded query for a date search. You then update the Business Rule with a complex query.

  • Guided: Create a Business Rule to find replaceable hardware assets by Purchased date.
  • Challenge: Update the Business Rule to use a more complex query for replaceable hardware.

NOTE: This exercise uses demo data that may vary in your instance.

Create a Business Rule to Find Replaceable Hardware Assets

When a Hardware Asset is changed to a State of In stock and a Substate of Available, you want to get a list of computers that could potentially be replaced by this newly available hardware. Replaceable hardware for this scenario is hardware purchased more than six years ago. While you could build this query with knowledge of the correct methods to work with dates, you use an encoded query to get the results.

  1. Log in to your ServiceNow instance as a System Administrator.

  2. Create a Business Rule.

    1. Use the Application Navigator to open Asset > Portfolios > Hardware Assets.
    2. Click the Additional actions ( Additional actions button ) button and select the Configure > Business Rules menu item.
    3. Click the New button.
    4. Configure the Business Rule.
      1. Name: List Old Hardware
      2. Advanced: selected (checked)
    5. Configure the When to run section.
      1. When: after
      2. Insert: selected (checked)
      3. Update: selected (checked)
      4. Filter conditions: [Substate] [is] [Available]

    6. Configure the Advanced section.

      1. Replace the contents of the script field with this script. You add the encoded query to this script later.

        
    (function executeRule(current, previous /*null when async*/) {

        // Create a GlideRecord object  
        var grHardware = new GlideRecord('alm_hardware');

        // Add encoded query for hardware - State is In use AND Purchased date is before six years ago 
        grHardware.addEncodedQuery('PASTE_ENCODED_QUERY_HERE');

        // Add ordering to show the oldest hardware first  
        grHardware.orderBy('purchase_date');

        // Execute query  
        grHardware.query();

        // Create variable to hold the list of replaceable hardware  
        var msg = current.display_name + " could potentially replace: \n";

        // Process returned records
        while(grHardware.next()){
            // Update the message to list replaceable hardware  
            msg = msg + "* " + grHardware.display_name + "\n" ;
        }

        current.setValue('comments',msg);
        current.update();

    })(current, previous);

        NOTE: A list of replaceable hardware could be added to a task to make it more useful. You add the list to the Comments field here for simplicity.

        DEVELOPER TIP: Encoded query structure is not documented and is not always easy to decipher. Add a comment describing the filter you applied as a translation for your encoded query. Your colleagues and your future self will thank you.

    7. Click the Submit button.

  3. Filter on In use hardware that was purchased more than six years ago.

    1. Use the Application Navigator to open Asset > Portfolios > Hardware Assets.

    2. Click the Show / hide filter icon to open the filter builder.

      The Show / hide filter icon

    3. Configure the filter for In use hardware assets.

      1. Click the Choose field choice list and select the State field.
      2. Leave the operator as is.
      3. Select the In use value.

    4. Configure the filter for hardware assets purchased more than six years ago.

      1. Click the AND button on the filter builder header bar.
      2. Click the Choose field choice list and select the Purchased field.
      3. Set the operator choice list to relative.

      4. Complete the filter configuration [before] [6] [Years] [ago].

        The filter configured for State is In use AND Purchased relative before 6 Years ago.

      5. Click the Run button.

      6. Make note of how many records are in the list and the asset tags for a couple of the old hardware assets.

  4. Right-click the end of the filter breadcrumb and select the Copy query menu item.

    The breadcrumb context menu allows you to copy the encoded query to use in a script.

  5. Add the encoded query to the Business Rule.

    1. Open the List Old Hardware Business Rule for editing.

    2. Replace the text PASTE_ENCODED_QUERY_HERE with the encoded query copied previously.

      NOTE: Use double quotes (“) around the encoded query if the encoded query contains single quotes(‘).

    3. Click the Update button.

  6. Test the Business Rule.

    1. Change the State of a hardware asset from In use to In stock with a Substate of Available.
      1. Use the Application Navigator to open Asset > Portfolios > Hardware Assets.
      2. Click the Asset tag for any In use hardware asset to open the record for editing.
      3. Configure the hardware asset.
        1. State: In stock
        2. Substate: Available
        3. Stockroom: Southern California Warehouse
      4. Click the Additional actions button and select the Save menu item.
      5. Review the Comments section. Compare the number of hardware assets and the asset tags with the number of records and asset tags recorded previously.

Did you do the hands-on exercise in this blog? Click here to let us know!

Challenge - Update the Business Rule to Use a More Complex Query

Update the query to also list hardware assets that are in maintenace and have a past due warranty expiration. Use this screenshot to build the filter to create the encoded query.

The filter is configured for State is In use AND Purchased relative before 6 Years ago OR State is In maintenance AND Warranty expiration before today.

Starting tips:

  • Build the filter pictured and copy the encoded query.
  • Update the Business Rule with the encoded query.
  • Update a couple hardware assets to have a State of In maintance with expired Warranty expiration dates. The Warranty expiration field is on the Contracts form section.

If you need help, see the Answers section at the bottom of this post for a sample script.

Closing Thoughts

Use the filter builder to build complex queries. You can then copy that encoded query in a script instead of building out a complex series of addQuery() and addOrCondition() statements. Encoded queries also remove the need to know underlying values for choice lists or methods to convert date values.

Answers

Challenge: Here is an example script for a Business Rule with the more complex encoded query.


    (function executeRule(current, previous /*null when async*/) {

        // Create a GlideRecord object  
        var grHardware = new GlideRecord('alm_hardware');

        // Add encoded query  
        grHardware.addEncodedQuery('install_status=1^purchase_dateRELATIVELT@year@ago@6^NQinstall_status=3^warranty_expiration<javascript:gs.beginningOfToday()');

        // Add ordering to show the oldest hardware first  
        grHardware.orderBy('purchase_date');

        // Execute query 
        grHardware.query();

        // Create variable to hold the list of replaceable hardware  
        var msg = current.display_name + " could potentially replace: \n";

        // Process returned records
        while(grHardware.next()){
            // Add hardware to the list
            msg = msg + "* " + grHardware.display_name + "\n" ;
        }

        current.setValue('comments',msg);
        current.update();

    })(current, previous);
Comments