In this comprehensive guide, we will take you through the fundamentals of GlideQuery, from basic concepts and syntax to advanced techniques and best practices. By the end of this article, you will have a solid grasp of how to leverage GlideQuery to enhance your data handling capabilities in ServiceNow, making your development process more efficient and your applications more responsive.

Let’s dive in and explore the power of GlideQuery, unlocking new potentials in your ServiceNow development journey.

Principles of GlideQuery

Peter Bell, a software engineer at ServiceNow, initially developed these API to use as an external tool for his team. Gradually, the API became an integral part of the platform, specifically integrated into the Paris release.

This API is versatile, compatible with both global and scoped applications. In the latter case, developers need to prefix their API calls with “global” to ensure proper functionality:

var user = new GlideQuery('sys_user')

var user = new global.GlideQuery('sys_user')

The API is entirely written in JavaScript and operates using GlideRecord in a second layer. Instead of replacing GlideRecord, its purpose is to enhance the development experience by minimizing errors and simplifying usage for developers.

I. Fail Fast

Detect errors as quickly as possible, before they become bugs.

Through a new type of error, NiceError, which facilitates the diagnosis of query errors, it is possible to know exactly what is wrong in your code and quickly fix it. Examples:

Field name validation

The API checks if the field names used in the query are valid and returns an error if any of them are not. In some cases, this is extremely important as it can prevent major issues. In the example below, using GlideRecord it would delete ALL records from the user table because the field name is written incorrectly. Note that the line that would delete the records has been commented out for safety and we have added a while loop just to display the number of records that would be deleted.

var gr = new GlideRecord('sys_user');
gr.addQuery('actived', '!=', true);
gr.query();
//gr.deleteMultiple();  commented for safety 
gs.info(gr.getRowCount());
while (gr.next()) {
    gs.info(gr.getDisplayValue('name'));
}

Using GlideQuery the API returns an error and nothing is executed:

var myGQ = new GlideQuery('sys_user')
    .where('activated', '!=', true)
    .del();

Returns:

NiceError: [2024-07-22T12:58:23.681Z]: Unknown field 'activated' in table 'sys_user'. Known fields:
[
  "country",
  "calendar_integration",
  etc.

Choice field validation

The API checks whether the option chosen for the field exists in the list of available options when it is of type choice. In the example below, using GlideRecord, the script would return nothing:

var gr = new GlideRecord('incident');
gr.addQuery('approval', 'donotexist'); //invalid value for the choice field 'approval'
gr.query();
while (gr.next()) {
    gs.info(gr.getDisplayValue());
}

Using GlideQuery, the API returns an error and nothing is executed:

var tasks = new GlideQuery('incident')
    .where('approval', 'donotexist')
    .select('number')
    .forEach(gs.log);

Returns:

NiceError: [2024-07-22T12:57:33.975Z]: Invalid choice 'donotexist' for field 'approval' (table 'incident'). Allowed values:
[
  "not requested",
  "requested",
  "approved",
  "rejected"
]

Value type validation

The API checks whether the type of value chosen for the field is correct.

var tasks = new GlideQuery('incident')
    .where('state', '8') // invalid value for the choice field 'state'
    .select('number')
    .forEach(function (g) {
        gs.info(g.number);
    });

Returns:

NiceError: [2024-07-22T12:56:51.428Z]: Unable to match value '8' with field 'state' in table 'incident'. Expecting type 'integer'

II. Be JavaScript (native JavaScript)

JavaScript objects bringing more familiarity to the way queries are made and reduce the learning curve. With GlideRecord we often have problems with the type of value returned:

var gr = new GlideRecord('sys_user');
gr.addQuery('first_name', 'Abel');
gr.query();

if (gr.next()) {
    gs.info(gr.first_name);
    gs.info(gr.firt_name === 'Abel');
    gs.info(typeof(gr.first_name));
}

Returns:

*** Script: Abel
*** Script: false
*** Script: object

As we can see Abel is different from Abel!
The reason for this confusion is that GlideRecord returns a Java object.

Using GlideQuery we don’t have this problem:

var user = new GlideQuery('sys_user')
    .where('first_name', 'Abel')
    .selectOne('first_name')
    .get(); // this method can throw error if no record is found

gs.info(user.first_name);
gs.info(user.first_name === 'Abel');
gs.info(typeof (user.first_name));

Returns:

*** Script: Abel
*** Script: true
*** Script: string

III. Be Expressive

Do more with less code! Simplify writing your code!

Performance

Using GlideQuery can increase processing time by around 4%, mainly due to the conversion of the Java object to JavaScript. However, keep in mind that we will often do this conversion manually after a GlideRecord.

Stream x Optional

The API works together with 2 other APIs: Stream and Optional. If the query returns a single record, the API returns an Optional object. If it returns several records, the API returns an object of type Stream, which is similar to an array. These objects can be manipulated according to the methods of each API.

Practical Examples

Before we start with the examples, it is necessary to make 2 points clear

• The primary key of the table (in our case normally the sys_id) is always returned even if the request is not made in Select.
• Unlike GlideRecord, GlideQuery does not return all record fields. We need to inform the name of the fields we want to get:
  .selectOne([‘field_1’, ‘field_2’, ‘field_n’])

I. selectOne

It is very common that we only need 1 record and in these cases we use selectOne(). This method returns an object of type Optional and we need a terminal method to process it:

a) get

// Searches for the user and if it does not exist returns an error
var user = new GlideQuery('sys_user')
    .where('last_name', 'Luddy')
    .selectOne('first_name')
    .get(); // this method can throw error if no record is found

gs.info(JSON.stringify(user, null, 4));

If success:

Script: {
    "first_name": "Fred",
    "sys_id": "5137153cc611227c000bbd1bd8cd2005"
}

If fail:

NiceError: [2024-07-21T22:28:48.274Z]: get() called on empty Optional: Unable to find a record with the following query:
GlideQuery<sys_user> [
  {
    "type": "where",
    "field": "last_name",
    "operator": "=",
    "value": "Luddyx",
    "whereClause": true
  }
]

b) orElse

Optional method used to handle queries that do not return any value.

var user = new GlideQuery('sys_user')
    .where('last_name', 'Luddy')
    .selectOne(['first_name'])
    .orElse({  //Method in the Optional class to return a default value.
        first_name: 'Nobody'
    });

gs.info(JSON.stringify(user, null, 4));

If success:

Script: {
    "first_name": "Fred",
    "sys_id": "5137153cc611227c000bbd1bd8cd2005"
}

If fail:

Script: {
    "first_name": "Nobody"
}

c) ifPresent

var userExists = false;
new GlideQuery('sys_user')
    .where('last_name', 'Luddy')
    .selectOne(['first_name'])
    .ifPresent(function (user) {
        gs.info(user.first_name + ' - ' + user.sys_id);
        userExists = true;
    });

gs.info(userExists);

If user exists:

*** Script: Fred - 5137153cc611227c000bbd1bd8cd2005
*** Script: true

If not:

*** Script: false

d) isPresent

var userExists = new GlideQuery('sys_user')
   .where('last_name', 'Luddy')
    . selectOne(['first_name'])
    .isPresent();

gs.info(userExists);

If user exists:

*** Script: true

If not:

*** Script: false

e) isEmpty

var userExists = new GlideQuery('sys_user')
   .where('last_name', 'Luddy')
    .selectOne(['first_name'])
    .isEmpty();

gs.info(userExists);

If user exists:

*** Script: false

If not:

*** Script: true

II. get

Returns a single record using sys_id

var user = new GlideQuery('sys_user')
    .get('62826bf03710200044e0bfc8bcbe5df1', ['first_name', 'last_name'])
    .orElse({
        first_name: 'Nobody',
        last_name: 'Nobody'
    });

gs.info(JSON.stringify(user, null, 4));

If user exists:

*** Script: {
    "sys_id": "62826bf03710200044e0bfc8bcbe5df1",
    "first_name": "Abel",
    "last_name": "Tuter"
}

If not:

*** Script: {
    "first_name": "Nobody",
    "last_name": "Nobody"
}

III. getBy

Returns a single record (even if there is more than 1 record) using the keys used as parameters.

var user = new GlideQuery('sys_user')
    .getBy({
        first_name: 'Fred',
        last_name: 'Luddy'
    }, ['city', 'active']) // select first_name, last_name, city, active
    .orElse({
        first_name: 'Nobody',
        last_name: 'Nobody',
        city: 'Nowhere',
        active: false
    });

gs.info(JSON.stringify(user, null, 4));

If user exists:

*** Script: {
    "first_name": "Fred",
    "last_name": "Luddy",
    "city": null,
    "active": true,
    "sys_id": "5137153cc611227c000bbd1bd8cd2005"
}

If not:

*** Script: {
    "first_name": "Nobody",
    "last_name": "Nobody",
    "city": "Nowhere",
    "active": false
}

IV. insert

The insert method needs an object as a parameter where each property must be the name of the field we want to fill. The insert returns an Optional with the data of the inserted object and the sys_id. We can also request extra fields for fields that are automatically populated:

var user = new GlideQuery('sys_user')
    .insert({  
        active: false,
        first_name: 'Thiago',
        last_name: 'Pereira',
    },['name'])
    .get()

gs.info(JSON.stringify(user, null, 4));

Returns:

*** Script: {
    "sys_id": "40f6e42147efc210cadcb60e316d43be",
    "active": false,
    "first_name": "Thiago",
    "last_name": "Pereira",
    "name": "Thiago Pereira"
}

V. update

The API has a method for when we want to update just one record. To use this method, the field used in the “where” must be the primary key and this way it updates just 1 record.

var user = new GlideQuery('sys_user')
    .where('sys_id', '40f6e42147efc210cadcb60e316d43be') //sys_id of the record created in the insert example
    .update({ email: 'thiago.pereira@example.com', active: true }, ['name'])
    .get();
gs.info(JSON.stringify(user, null, 4));

Returns:

*** Script: {
    "sys_id": "40f6e42147efc210cadcb60e316d43be",
    "email": "thiago.pereira@example.com",
    "active": true,
    "name": "Thiago Pereira"
}

VI. updateMultiple

var myQuery = new GlideQuery('sys_user')
    .where('active', false)
    .where('last_name', 'LIKE', 'Pereira') 
    .updateMultiple({ active: false });

gs.info(JSON.stringify(myQuery, null, 4));

If success:

*** Script: {
    "rowCount": 1
}

If fail:

*** Script: {
    "rowCount": 0
}

VII. insertOrUpdate

This method receives an object with the key(s) to perform the search. If one of the keys is a primary key (sys_id), it searches for the record and updates the other fields entered in the object. If no primary key is passed or the sys_id is not found, the method will create a new record. In this method we cannot select fields other than those passed in the object.

// Create a new record even though a user already exists with the values
var user = new GlideQuery('sys_user')
    .insertOrUpdate({
        first_name: 'Thiago',
        last_name: 'Pereira'
    })
    .orElse(null);
    
gs.info(JSON.stringify(user, null, 4));

Returns:

*** Script: {
    "sys_id": "5681fca1476fc210cadcb60e316d43b6",
    "first_name": "Thiago",
    "last_name": "Pereira"
}

// Update an existing record
var user = new GlideQuery('sys_user')
 .insertOrUpdate({
 sys_id: '40f6e42147efc210cadcb60e316d43be', //sys_id of the record created in the insert example
 first_name: 'Tiago',
 last_name: 'Pereira'})
 .orElse(null);

gs.info(JSON.stringify(user, null, 4));

Returns:

*** Script: {
    "sys_id": "40f6e42147efc210cadcb60e316d43be",
    "first_name": "Tiago",
    "last_name": "Pereira"
}

// Creates a new record as the sys_id does not exist
var user = new GlideQuery('sys_user')
    .insertOrUpdate({
        sys_id: 'xxxxxxxxxxxxxxxxxxxx',
        first_name: 'Thiago',
        last_name: 'Pereira2'})
    .orElse(null);
gs.info(JSON.stringify(user, null, 4));

Returns:

*** Script: {
    "sys_id": "50e338ed47afc210cadcb60e316d4364",
    "first_name": "Thiago",
    "last_name": "Pereira2"
}

VIII. deleteMultiple / del

There is no method to delete just one record. To do this we need to use deleteMultiple together with where() using a primary key. The method does not return any value.

var user = new  GlideQuery('sys_user')
    .where('last_name', "CONTAINS", 'Pereira2')
    .deleteMultiple();

IX. whereNotNull

Note: We will talk about dot walking later.

new GlideQuery('sys_user')
    .whereNotNull('company')
    .whereNotNull('company.city')
    .select('name', 'company.city')
    .forEach(function (user) {
        gs.info(user.name + ' works in ' + user.company.city)
    });

Returns:

*** Script: Lucius Bagnoli works in Tokyo
*** Script: Melinda Carleton works in London
*** Script: Jewel Agresta works in London
*** Script: Christian Marnell works in Prague
*** Script: Naomi Greenly works in London
*** Script: Jess Assad works in Tokyo
etc.

X. limit

new GlideQuery('sys_user')
    .whereNotNull('company')
    .whereNotNull('company.city')
    .limit(2)
    .select('name', 'company.city')
    .forEach(function (user) {
        gs.info(user.name + ' works in ' + user.company.city)
    });

Returns:

*** Script: Mildred Gallegas works in Rome
*** Script: Elisa Gracely works in Rome

XI. select

We often need queries that return several records and in these cases we use select(). This method returns an object of type Stream and we need a terminal method to process it:

a) forEach

var arrIncidens = [];
var incidents = new GlideQuery('incident')
    .where('state', 2)
    .limit(2)
    .select('number')
    .forEach(function (inc) {
        gs.info(inc.number + ' - ' + inc.sys_id);
        arrIncidens.push(inc.sys_id);
    });

if (arrIncidens.length==0)
    gs.info('Not found.')

If success:

*** Script: INC0000025 - 46f09e75a9fe198100f4ffd8d366d17b
*** Script: INC0000029 - 46f67787a9fe198101e06dfcf3a78e99

If fail:

*** Script: Not found.

b) toArray

Returns an array containing the items from a Stream. The method needs a parameter that is the maximum size of the array, the limit being 100.

var users = new global.GlideQuery('sys_user')
    .limit(20)
    .select('first_name', 'last_name')
    .toArray(2); // max number of items to return in the array

gs.info(JSON.stringify(users, null, 4));

Returns:

*** Script: [
    {
        "first_name": "survey",
        "last_name": "user",
        "sys_id": "005d500b536073005e0addeeff7b12f4"
    },
    {
        "first_name": "Lucius",
        "last_name": "Bagnoli",
        "sys_id": "02826bf03710200044e0bfc8bcbe5d3f"
    }
]

c) map

Used to transform each record in a Stream.

new GlideQuery('sys_user')
    .limit(3)
    .whereNotNull('first_name')
    .select('first_name')
    .map(function(user) {
        return user.first_name.toUpperCase();
    })
    .forEach(function(name) {
        gs.info(name);
    });

Returns:

*** Script: SURVEY
*** Script: LUCIUS
*** Script: JIMMIE

d) filter

Note: The filter in a Stream always occurs after the query has been executed. Therefore, whenever possible, we should make all possible filters before the select to avoid loss of performance.

var hasBadPassword = function(user) {
    return !user.user_password ||
        user.user_password.length < 10 ||
        user.user_password === user.last_password ||
        !/\d/.test(user.user_password) // no numbers
        ||
        !/[a-z]/.test(user.user_password) // no lowercase letters
        ||
        !/[A-Z]/.test(user.user_password); // no uppercase letters
};

new GlideQuery('sys_user')
    .where('sys_id', 'STARTSWITH', '3')
    .select('name', 'email', 'user_password', 'last_password')
    .filter(hasBadPassword)
    .forEach(function(user) {
        gs.info(user.name + ' - ' + user.sys_id)
    });

Returns:

*** Script: Patty Bernasconi - 3682abf03710200044e0bfc8bcbe5d17
*** Script: Veronica Achorn - 39826bf03710200044e0bfc8bcbe5d1f
*** Script: Jessie Barkle - 3a82abf03710200044e0bfc8bcbe5d10

e) limit

Both the GlideQuery and Stream APIs have the limit() method. In GlideQuery it is executed before the execution of the query, which is more performant. For this reason, whenever possible, we should use the limit before executing the select.

new GlideQuery('task')
    .orderBy('priority')
    .limit(3) // Good: calling GlideQuery's limit method
    .select('assigned_to', 'priority', 'description')
    //.limit(3) // Bad: calling Stream's limit method
    .forEach(function(myTask) {
        gs.info(myTask.priority + ' - ' + myTask.assigned_to)
    });

Returns:

*** Script: 1 - 5137153cc611227c000bbd1bd8cd2007
*** Script: 1 - 681b365ec0a80164000fb0b05854a0cd
*** Script: 1 - 5137153cc611227c000bbd1bd8cd2007

f) find

Returns the first item found in the Stream according to the given condition. Important notes:

• Returns an Optional, which may be empty if no item is found.
• The first item in the Stream is returned if no condition is entered.

var hasBadPassword = function(user) {
    return !user.user_password ||
        user.user_password.length < 10 ||
        user.user_password === user.last_password ||
        !/\d/.test(user.user_password) || // no numbers
        !/[a-z]/.test(user.user_password) || // no lowercase letters
        !/[A-Z]/.test(user.user_password); // no uppercase letters
};
var disableUser = function(user) {
    var myGQ = new GlideQuery('sys_user')
        .where('sys_id', user.sys_id)
        .update({
            active: false
        }, ['user_name'])
        .get();
        gs.info(JSON.stringify(myGQ, null, 4));
};
var myUsers = new GlideQuery('sys_user')
    .select('name', 'email', 'user_password', 'last_password')
    .find(hasBadPassword)
    .ifPresent(disableUser);

Returns:

*** Script: {
    "sys_id": "0e826bf03710200044e0bfc8bcbe5d45",
    "active": false,
    "user_name": "ross.spurger"
}

g) reduce

var longestFirstName = new global.GlideQuery('sys_user')
   .whereNotNull('first_name')
   .select('first_name')
   .reduce(function (acc, cur) {
       return (cur.first_name.length > acc.length) ? cur.first_name : acc;
       }, '');

gs.info(JSON.stringify(longestFirstName));

Returns:

*** Script: "Sitemap Scheduler User"

h) Every

Executes a function on each item in the Stream. If it returns true for all items in the Stream, the method returns true, otherwise it returns false.

var numToCompare = 10;
//var numToCompare = 1000;

var hasOnlyShortDescriptions = new global.GlideQuery('task')
    .whereNotNull('description')
    .select('description')
    .every(function(t) {
        return t.description.length < numToCompare;
    });

gs.info(hasOnlyShortDescriptions);

If numToCompare  is 10, returns:

*** Script: false

If numToCompare  is 1000, returns:

*** Script: true

i) some

Executes a function on each item in the Stream. If it returns true for at least one item in the Stream, the method returns true, otherwise it returns false.

var numToCompare = 10;
//var numToCompare = 1000;

var hasLongDescriptions = new global.GlideQuery('task')
   .whereNotNull('description')
   .select('description')
   .some(function (t) { 
      return t.description.length > numToCompare; 
   });

gs.info(hasLongDescriptions);

If numToCompare  is 10, returns:

*** Script: true

If numToCompare  is 1000, returns:

*** Script: false

j) flatMap

FlatMap is very similar to map but with 2 differences:

1.  The function passed to flatMap must return a Stream.
2. The flatMap manipulates (unwraps/flattens) the returned Stream so that the “parent” method can return the result.

var records = new global.GlideQuery('sys_user')
    .where('last_login', '>', '2015-12-31')
    .select('first_name', 'last_name')
    .flatMap(function(u) {
        return new global.GlideQuery('task')
            .where('closed_by', u.sys_id)
            .where('short_description', 'Prepare for shipment')
            .select('closed_at', 'short_description')
            .map(function(t) {
                return {
                    first_name: u.first_name,
                    last_name: u.last_name,
                    short_description: t.short_description,
                    closed_at: t.closed_at
                };
            });
    })
    .toArray(50);

gs.info(JSON.stringify(records, null, 4));

Returns:

*** Script: [
    {
        "first_name": "Thiago",
        "last_name": "Pereira",
        "short_description": "Prepare for shipment",
        "closed_at": "2021-10-04 13:43:48"
    },
    {
        "first_name": "Thiago",
        "last_name": "Pereira",
        "short_description": "Prepare for shipment",
        "closed_at": "2021-10-04 13:40:22"
    },
    {
        "first_name": "Thiago",
        "last_name": "Pereira",
        "short_description": "Prepare for shipment",
        "closed_at": "2021-10-04 13:42:14"
    }
]

XII. parse

Similar to GlideRecord’s addEcodedQuery but does not accept all operators. Currently only the following operators are supported:

var myTask = new GlideQuery.parse('task', 'active=true^short_descriptionLIKEthiago^ORDERBYpriority')
    .limit(10)
    .select('short_description', 'priority')
    .toArray(10); // 10 is the max number of items to return in the array

gs.info(JSON.stringify(myTask, null, 4));

Returns:

*** Script: [
    {
        "short_description": "thiago teste update 2",
        "priority": 1,
        "sys_id": "8b2c540b47ce0610cadcb60e316d4377"
    }
]

XIII. orderBy / orderByDesc

var users = new global.GlideQuery('sys_user')
    .limit(3)
    .whereNotNull('first_name')
    .orderBy('first_name')
    //.orderByDesc('first_name')
    .select('first_name', 'last_name')
    .toArray(3); 

gs.info(JSON.stringify(users, null, 4));

Returns:

*** Script: [
    {
        "first_name": "Abel",
        "last_name": "Tuter",
        "sys_id": "62826bf03710200044e0bfc8bcbe5df1"
    },
    {
        "first_name": "Abraham",
        "last_name": "Lincoln",
        "sys_id": "a8f98bb0eb32010045e1a5115206fe3a"
    },
    {
        "first_name": "Adela",
        "last_name": "Cervantsz",
        "sys_id": "0a826bf03710200044e0bfc8bcbe5d7a"
    }
]

XIV. withAcls

It works in the same way as GlideRecordSecure, forcing the use of ACLs in queries.

var users = new GlideQuery('sys_user')
    .withAcls()
    .limit(4)
    .orderByDesc('first_name')
    .select('first_name')
    .toArray(4);

gs.info(JSON.stringify(users, null, 4));

XV. disableAutoSysFields

new GlideQuery('task')
    .disableAutoSysFields()
    .insert({ description: 'example', priority: 1 });

XVI. forceUpdate

Forces an update to the registry. Useful, for example, when we want some BR to be executed.

new GlideQuery('task')
    .forceUpdate()
    .where('sys_id', 'd71b3b41c0a8016700a8ef040791e72a')
    .update()

XVII. disableWorkflow

new GlideQuery('sys_user')
    .disableWorkflow() // ignore business rules
    .where('email', 'bob@example.com')
    .updateMultiple({ active: false });

XVIII. Dot Walking

var tokyoEmployee = new GlideQuery('sys_user')
    .where('company.city', 'Tokyo')
    .selectOne('name', 'department.id')
    .get();
gs.info(JSON.stringify(tokyoEmployee, null, 4));

Returns:

*** Script: {
    "name": "Lucius Bagnoli",
    "department": {
        "id": "0023"
    },
    "sys_id": "02826bf03710200044e0bfc8bcbe5d3f"
}

XIX. Field Flags

Some fields support metadata. The most common case is the “display value”. We use the “$” symbol after the field name and specify the desired metadata. Metadata supported so far:

1. $DISPLAY = getDisplayValue
2. $CURRENCY_CODE = getCurrencyCode
3. $CURRENCY_DISPLAY = getCurrencyDisplayValue
4. $CURRENCY_STRING = getCurrencyString

new GlideQuery('sys_user')
    .whereNotNull('company')
    .limit(3)
    .select('company', 'company$DISPLAY')
    .forEach(function(user) {
        gs.info(user.company$DISPLAY + ' - ' + user.company);
    });

Returns:

*** Script: ACME Italy - 187d13f03710200044e0bfc8bcbe5df2
*** Script: ACME Italy - 187d13f03710200044e0bfc8bcbe5df2
*** Script: ACME Italy - 187d13f03710200044e0bfc8bcbe5df2

XX. aggregate

Used when we want to make aggregations. GlideQuery also has methods for this and they are often easier to use than GlideAggregate.

a) count

Using ‘normal’ GlideAggregate

var usersGa = new GlideAggregate('sys_user');
usersGa.addAggregate('COUNT');
usersGa.query();
usersGa.next();
gs.info(typeof (usersGa.getAggregate('COUNT')));
var userCount = parseInt(usersGa.getAggregate('COUNT'));
gs.info(typeof (userCount));
gs.info(userCount);

Returns:

*** Script: string
*** Script: number
*** Script: 629

Using GlideQuery

var userCount = new GlideQuery('sys_user').count();
gs.info(typeof(userCount));
gs.info(userCount);

Returns:

*** Script: number
*** Script: 629

Note that in addition to using fewer lines of code, GlideQuey returns a number while GlideAggregate returns a string.

b) avg

var faults = new GlideQuery('cmdb_ci')
    .avg('sys_mod_count')
    .orElse(0);
gs.info(faults);

Returns:

*** Script: 7.533

c) max

var faults = new GlideQuery('cmdb_ci')    
    .max('sys_mod_count')
    .orElse(0);
gs.info(faults);

Returns:

*** Script: 172

d) min

var faults = new GlideQuery('cmdb_ci')    
    .min('sys_mod_count')
    .orElse(0);
gs.info(faults);

Returns:

*** Script: 0

e) sum

var totalFaults = new GlideQuery('cmdb_ci')
    .sum('sys_mod_count')
    .orElse(0);
gs.info(totalFaults);

Returns:

*** Script: 20972

f) groupBy

var taskCount = new GlideQuery('incident')
    .aggregate('count')
    .groupBy('state')
    .select()
    .forEach(function (g) {
        gs.info(JSON.stringify(g, null, 4));
    });

Returns:

*** Script: {
    "group": {
        "state": 1
    },
    "count": 13
}
*** Script: {
    "group": {
        "state": 2
    },
    "count": 19
}
etc.

g) aggregate

var taskCount = new GlideQuery('task')
    .groupBy('contact_type')
    .aggregate('avg', 'reassignment_count')
    .select()
    .forEach(function (g) {
        gs.info(JSON.stringify(g, null, 4));
    });

Returns:

*** Script: {
    "group": {
        "contact_type": ""
    },
    "avg": {
        "reassignment_count": 0.0033
    }
}
*** Script: {
    "group": {
        "contact_type": "email"
    },
    "avg": {
        "reassignment_count": 1
    }
}
etc.

h) having

new GlideQuery('core_company')
    .aggregate('sum', 'market_cap')
    .groupBy('country')
    .having('sum', 'market_cap', '>', 0)
    .select()
    .forEach(function (g) {
        gs.info('Total market cap of ' + g.group.country + ': ' + g.sum.market_cap);
    });

Returns:

*** Script: Total market cap of : 48930000000
*** Script: Total market cap of USA: 5230000000

The post GlideQuery Cheat Sheet appeared first on ServiceNow Guru.

This article demonstrates how to streamline this process by creating a UI Action that allows you to move multiple customer updates between update sets with a single click.

Problem Statement
When you make configurations in your instance, there are times when you need to move these updates to a different update set. The conventional method requires opening each customer update record individually and changing the update set field to the desired update set. This method becomes impractical when dealing with numerous updates.

Solution
To overcome this challenge, you can create a UI Action that facilitates the movement of multiple customer updates to another update set in one go. This approach not only saves time but also reduces the risk of manual errors.

To begin, navigate to System Definition > UI Actions and create a new UI Action:

function showConfirmDialog() {

	var entries = g_list.getChecked();
	if (!entries || entries.length == 0)
		return;

	// this function runs when the modal close
	var callback = function () {

		// To send parameters to the server script we should use GlideAjax
		var ga = new GlideAjax('MoveUpdatesToCurrentUS');
		ga.addParam('sysparm_name', 'moveEntries');
		ga.addParam('sysparm_entry_ids', entries);

		// GlideAjax callback to refresh the related list
		var afterMove = function () {
			GlideList2.get(g_form.getTableName() + '.sys_update_xml.update_set').setFilterAndRefresh('');
		};

		ga.getXMLAnswer(afterMove.bind(this));
		return true;
	};

	var dialogClass = window.GlideModal ? GlideModal : GlideDialogWindow;
	var dialog = new dialogClass('glide_confirm_standard');
	dialog.setTitle(new GwtMessage().getMessage('Confirmation'));
	dialog.setPreference('warning', true);
	dialog.setPreference('title', new GwtMessage().getMessage('move_update_set_entries'));
	dialog.setPreference('onPromptComplete', callback.bind(this));
	dialog.render();
	
}

Two points I would like to highlight in this script.
– As we need to pass the selected records as a parameter to the server script, we need to use GlideAjax.
– For the callback function I needed to update only the related list. Initially I could only update the entire record but a quick search here on the portal led me to an article explaining exactly what I needed.

For the modal message to be displayed correctly, we need to create a record in the sys_ui_message table with the text we want.  Navigate to System UI > Messages and create a new message:

Now we need to create the Script Include that will perform all the necessary validations and actions. Navigate to System Definition> Script Includes and create a new script:

var MoveUpdatesToCurrentUS = Class.create();

MoveUpdatesToCurrentUS.prototype = Object.extendsObject(AbstractAjaxProcessor, {

    moveEntries: function () {

        var arrMoved = [];
        var objMoved = {};
        var arrNotMoved = [];
        var objNotMoved = {};
        var currUS_ID = '';
        var customUpdateLink = '';
        var customUpdateType = '';
        var baseURL = gs.getProperty('glide.servlet.uri');

        //create a link to the syslog to return the logs created by this SI on last 15 minutes 
        var sysLogQuery = 'sys_created_onONLast 15 minutes@javascript:gs.beginningOfLast15Minutes()@javascript:gs.endOfLast15Minutes()^messageLIKEMoveUpdatesToCurrentUS';
        sysLogQuery = encodeURIComponent(sysLogQuery); //https://www.w3schools.com/jsref/jsref_encodeuricomponent.asp
        var sysLogURL = baseURL + 'syslog_list.do?sysparm_query=' + sysLogQuery;
        
        // get the user current US
        var currUS = gs.getPreference('sys_update_set');
        var grUS = new GlideRecord('sys_update_set');		

        if (grUS.get(currUS)) {
            currUS_ID = grUS.getUniqueValue();
        } else {
            gs.info('[MoveUpdatesToCurrentUS] Error: Unable to get Current US.');
            gs.addErrorMessage('There was an error. Please review the <a href="http://'%20+%20sysLogURL%20+%20'">system logs</a> for more details.');
            return false;
        }

        // get the variable sent by the UI Action
        var entries = this.getParameter('sysparm_entry_ids');

        //get all selected custom updates
        var customUpGr = new GlideRecord('sys_update_xml');
        customUpGr.addEncodedQuery('sys_idIN' + entries);
        customUpGr.query();

        while (customUpGr.next()) {
            
            //check if the user can write in the table
            if (customUpGr.canWrite()) {

                //create a link to the current update and get its type
                customUpdateLink = baseURL + 'sys_update_xml.do?sys_id=' + customUpGr.getValue('sys_id');
                customUpdateType = customUpGr.getValue('type');

                // check if it trying to move do a diferent US
                if (customUpGr.getValue('update_set') == currUS_ID) {

                    objNotMoved = {
                        sys_id: customUpGr.getUniqueValue(),
                        reasonHTML: 'Custom US <a href="http://'%20+%20customUpdateLink%20+%20'">' + customUpdateType + '</a> is already in your currently US.',
                        reasonText: 'Custom US ' + customUpdateType + ' - ' + customUpGr.getValue('sys_id') + ' - is already in your currently US.'
                    };

                    arrNotMoved.push(objNotMoved);

                } else {

                    // move the customer update to de current US
                    try {

                        customUpGr.setValue('update_set', currUS);
                        customUpGr.update();
                        
                        objMoved = {
                            sys_id: customUpGr.getUniqueValue(),
                            reasonHTML: 'Custom US <a href="http://'%20+%20customUpdateLink%20+%20'">' + customUpdateType + '</a> was moved to your currently US.',
                            reasonText: 'Custom US ' + customUpdateType + ' - ' + customUpGr.getValue('sys_id') + ' - was moved to your currently US.'
                        };

                        arrMoved.push(objMoved);

                    } catch (e) {

                        objNotMoved = {
                            sys_id: customUpGr.getUniqueValue(),
                            reasonHTML: 'Unable to move the Custom US <a href="http://'%20+%20customUpdateLink%20+%20'">' + customUpdateType + '</a> due: ' + e,
                            reasonText: 'Unable to move the Custom US ' + customUpdateType + ' - ' + customUpGr.getValue('sys_id') + ' - due: ' + e
                        };
    
                        arrNotMoved.push(objNotMoved);

                    }

                }				

            } else {

                objNotMoved = {
                    sys_id: customUpGr.getUniqueValue(),
                    reasonHTML: 'Unable to move the Custom US <a href="http://'%20+%20customUpdateLink%20+%20'">' + customUpdateType + '</a> due to ACL restrictions.',
                    reasonText: 'Unable to move the Custom US ' + customUpdateType + ' - ' + customUpGr.getValue('sys_id') + ' - due to ACL restrictions.'			
                };

                arrNotMoved.push(objNotMoved);

            }			

        }

        // create 2 messages: one to the syslog and one to show to user
        var sysLogMessage = '[MoveUpdatesToCurrentUS]' + '\r\r';

        if (arrMoved.length > 0) {
            
            var successScreenMessage = 'Move Updates to Current US Action' + '<br><br>';

            sysLogMessage += 'Custom Updates moved:\r';
            successScreenMessage += 'Custom Updates moved:<br>';

            for (var i = 0; i < arrMoved.length; i++) {
                sysLogMessage += arrMoved[i].reasonText + '\r';
                successScreenMessage += arrMoved[i].reasonHTML + '<br>';
            }

            gs.addInfoMessage(successScreenMessage);

        }

        if (arrNotMoved.length > 0) {

            var failureScreenMessage = 'Move Updates to Current US Action' + '<br><br>';

            sysLogMessage += '\r\rCustom Updates NOT moved:\r';
            failureScreenMessage += 'Custom Updates NOT moved:<br>';

            for (var x = 0; x < arrNotMoved.length; x++) {
                sysLogMessage += arrNotMoved[x].reasonText + '\r';
                failureScreenMessage += arrNotMoved[x].reasonHTML + '<br>';
            }

            gs.addErrorMessage(failureScreenMessage);

        }		

        gs.info(sysLogMessage);
        
        gs.addInfoMessage('Customer Update(s) transfer has been completed. Please review the <a href="http://'%20+%20sysLogURL%20+%20'">system logs</a> for more details.');

        return true;

    },

    type: 'MoveUpdatesToCurrentUS'
});

Using

• Open the update set from which you want to move few customer updates
• Go to the Customer Updates Related List
• Select all the updates you want to move
• Click on [Move Updates to Current US] in the actions dropdown menu

• If everything works fine all selected customer updates have been moved to your Current US and you will see a message – simple as that.

• If any problems occur during the transfer, you will be notified.

• Everything will be recorded in syslog

Benefits

• Efficiency: Moves multiple updates simultaneously, significantly reducing the time required.
• Accuracy: Minimizes the risk of errors associated with manual updates.
• Usability: Provides a straightforward and user-friendly method for managing update sets.

By implementing this UI Action, you can enhance your productivity and ensure a smoother workflow when managing update sets in ServiceNow. This method is particularly beneficial in complex development environments where rapid and accurate configuration management is essential.

The post Easily Move Customer Updates Between Update Sets appeared first on ServiceNow Guru.

In this article I’ll show you how you can use GlideDialogWindow to pop open a dialog containing any custom UI Page information you want. I’ll also show how you can pass information into those dialogs, and how you can return information from those dialogs back to the standard form that initiated the dialog. These dialogs can be initiated from any place where you can use client scripts…client scripts, UI Macros, UI Actions, etc.

The example I’ll use here is based on one that a ServiceNow developer wrote as an example. It’s not very fancy, but it’s simple enough to show how things work without adding a bunch of confusing elements. It simply opens a dialog with information populated from the form and returns information from the dialog to the form.

The first piece of this solution is to set up some mechanism to trigger your GlideDialogWindow. For this example, I’ve chosen to use a UI Action button. Here are the details of the button. The comments in the script explain how to initialize the dialog and pass parameters on to your UI Page to populate information there.

function commentsDialog() {
//Get the values to pass into the dialog
var comments_text = g_form.getValue("comments");
var short_text = g_form.getValue("short_description");//Initialize and open the Dialog Window
var dialog = new GlideDialogWindow("task_comments_dialog"); //Render the dialog containing the UI Page 'task_comments_dialog'
dialog.setTitle("Add Task Comments"); //Set the dialog title
dialog.setPreference("comments_text", comments_text); //Pass in comments for use in the dialog
dialog.setPreference("short_text", short_text); //Pass in short description for use in the dialog
dialog.render(); //Open the dialog
}

Once you have your UI Action set up to trigger the dialog, you need to make sure that you have the correctly-named UI Page to display in the dialog. In the script above, we used ‘var dialog = new GlideDialogWindow(“task_comments_dialog”);’ to initialize a GlideDialogWindow and point to the ‘task_comments_dialog’ UI Page. Here’s what that page looks like. The comments in the HTML below explain each piece of the UI Page. The client script portion of the UI Page is used to validate the input on submission of the dialog form. If validation passes, the value of the ‘Comments’ field on the dialog is passed to the ‘Comments’ field on the original form.

<table width="100%">
<tbody>
<tr id="description_row" valign="top">
<td colspan="2">${jvar_short_text}</td>
</tr>
<tr>
<td colspan="2"></td>
</tr>
</tbody>
</table>

function validateComments() {
//Gets called if the 'OK' dialog button is clicked
//Make sure dialog comments are not empty
var comments = gel("dial_comments").value;
comments = trim(comments);
if (comments == "") {
//If comments are empty stop submission
alert("Please provide comments to submit the dialog.");
return false;
}
//If comments are not empty do this...
GlideDialogWindow.get().destroy(); //Close the dialog window
g_form.setValue("comments", comments); //Set the 'Comments' field with comments in the dialog
}

The post GlideDialogWindow: Advanced Popups Using UI Pages appeared first on ServiceNow Guru.

While the content of this post isn’t new information (additional examples can be found on the Service-now wiki), my aim is to provide a single page of information containing some common examples of these methods as a reference. This is an excellent page to keep bookmarked!

var gr = new GlideRecord('incident'); //Indicate the table to query from
//The 'addQuery' line allows you to restrict the query to the field/value pairs specified (optional)
//gr.addQuery('active', true);
gr.query(); //Execute the query
while (gr.next()) { //While the recordset contains records, iterate through them
//Do something with the records returned
if(gr.category == 'software'){
gs.log('Category is ' + gr.category);
}
}

var gr = new GlideRecord('sys_user');
gr.addQuery('name', 'Joe Employee');
gr.query(myCallbackFunction); //Execute the query with callback function//After the server returns the query recordset, continue here
function myCallbackFunction(gr){
while (gr.next()) { //While the recordset contains records, iterate through them
alert(gr.user_name);
}
}

var gr = new GlideRecord('incident');
gr.get(sys_id_of_record_here);
//Do something with the record returned
if(gr.category == 'software'){
gs.log('Category is ' + gr.category);
}

//Find the first active incident record
var gr = new GlideRecord('incident');
if(gr.get('active', true)){
//Do something with the record returned
gs.log('Category is ' + gr.category);
}

var caller = current.caller_id.getRefRecord(); //Returns the GlideRecord for the value populated in the 'caller_id' field
caller.email = 'test@test.com';
caller.update();

//Find all incidents with a priority of 1 or 2
var gr = new GlideRecord('incident');
var grOR = gr.addQuery('priority', 1);
grOR.addOrCondition('priority', 2);
gr.query();
while (gr.next()) {
//Do something with the records returned
if(gr.category == 'software'){
gs.log('Category is ' + gr.category);
}
}

//Find all incidents with a priority of 1 or 2
var gr = new GlideRecord('incident');
gr.addQuery('priority', 1).addOrCondition('priority', 2);
gr.query();

//Create a new Incident record and populate the fields with the values below
var gr = new GlideRecord('incident');
gr.initialize();
gr.short_description = 'Network problem';
gr.category = 'software';
gr.caller_id.setDisplayValue('Joe Employee');
gr.insert();

//Find all active incident records and make them inactive
var gr = new GlideRecord('incident');
gr.addQuery('active',true);
gr.query();
while (gr.next()) {
gr.active = false;
gr.update();
}

//Find all inactive incident records and delete them one-by-one
var gr = new GlideRecord('incident');
gr.addQuery('active',false);
gr.query();
while (gr.next()) {
//Delete each record in the query result set
gr.deleteRecord();
}

//Find all inactive incidents and delete them all at once
var gr = new GlideRecord('incident');
gr.addQuery('active', false);
gr.deleteMultiple(); //Deletes all records in the record set

//Find all active incidents where the category is software or hardware
var gr = new GlideRecord('incident');
var strQuery = 'active=true';
strQuery = strQuery + '^category=software';
strQuery = strQuery + '^ORcategory=hardware';
gr.addEncodedQuery(strQuery);
gr.query();

//Find all active incidents and log a count of records to the system log
var gr = new GlideAggregate('incident');
gr.addQuery('active', true);
gr.addAggregate('COUNT');
gr.query();
var incidents = 0;
if (gr.next()){
incidents = gr.getAggregate('COUNT');
gs.log('Active incident count: ' + incidents);
}

//Find all active incidents and order the results ascending by category then descending by created date
var gr = new GlideRecord('incident');
gr.addQuery('active', true);
gr.orderBy('category');
gr.orderByDesc('sys_created_on');
gr.query();

//Find all incidents where the Short Description is empty
var gr = new GlideRecord('incident');
gr.addNullQuery('short_description');
gr.query();

//Find all incidents where the Short Description is not empty
var gr = new GlideRecord('incident');
gr.addNotNullQuery('short_description');
gr.query();

//Log the number of records returned by the query
var gr = new GlideRecord('incident');
gr.addQuery('category', 'software');
gr.query();
gs.log('Incident count: ' + gr.getRowCount());

//Log the number of records returned by the query
var gr = new GlideRecord('incident');
gr.addQuery('category', 'software');
gr.query();
alert('Incident count: ' + gr.rows.length);

//Find the last 10 incidents created
var gr = new GlideRecord('incident');
gr.orderByDesc('sys_created_on');
gr.setLimit(10);
gr.query();

//Find the last 10 incidents created
var gr = new GlideRecord('incident');
gr.orderByDesc('sys_created_on');
gr.chooseWindow(10, 20);
gr.query();

//Change the category of all 'software' incidents to 'hardware' without triggering business rules on updated records
var gr = new GlideRecord('incident');
gr.addQuery('category', 'software');
gr.query();
while(gr.next()){
gr.category = 'hardware';
gr.setWorkflow(false);
gr.update();
}

//Change the category of all 'software' incidents to 'hardware' without updating sys fields
var gr = new GlideRecord('incident');
gr.addQuery('category', 'software');
gr.query();
while(gr.next()){
gr.category = 'hardware';
gr.autoSysFields(false);
gr.update();
}

//Force an update to all User records without changing field values
var gr = new GlideRecord('sys_user');
gr.query();
while (gr.next()) {
gr.setWorkflow(false); //Do not run business rules
gr.autoSysFields(false); //Do not update system fields
gr.setForceUpdate(true); //Force the update
gr.update();
}

The post GlideRecord Query Cheat Sheet appeared first on ServiceNow Guru.

But what if you need to do both? The classic case is when you want to click a button to make an update to a record, but only if the user has provided the correct input first. An example would be a ‘Reopen Incident’ button that changes the state on an incident record from ‘Resolved’ to ‘Active’. Usually you want to require the user to provide some sort of comment or additional information explaining why they are reopening the ticket. The problem is that you don’t always want the ‘Comments’ field to be mandatory so the validation needs to happen at the time the ‘Reopen Incident’ button gets clicked. Validation of mandatory fields needs to happen client-side but the update to your record needs to happen server-side. How can you accomplish both of these things with a single UI Action? This article shows you how.

The basic format for using a Client Script and Business Rule in the same UI Action looks something like this…

//Client-side 'onclick' function
function runClientCode(){
if( == false){
return false; //Abort submission
}
//Call the UI Action and skip the 'onclick' function
gsftSubmit(null, g_form.getFormElement(), ''); //MUST call the 'Action name' set in this UI Action
}//Code that runs without 'onclick'
//Ensure call to server-side function with no browser errors
if(typeof window == 'undefined')
runBusRuleCode();//Server-side function
function runBusRuleCode(){
current. = ;
current.update();
gs.addInfoMessage('You did it!');
action.setRedirectURL(current);
}

So why does this work? I had to go to a Service-now developer to find out. The reason is that UI Actions can run scripts at two different times. The first time is when the UI Action gets clicked. When you define a ‘Client’ UI Action you also give that UI Action the name of a function in your ‘Script’ field to execute. This function has to be called explicitly (through the ‘onclick’ event) or it doesn’t run at all.
The second time is on the way to the server. This is how any UI Action without the ‘Client’ checkbox selected gets run. On the way to the server the entire UI Action script gets executed regardless of whether or not the ‘Client’ checkbox is checked. What this means is that any script you include in your UI Action that isn’t enclosed in a function will be run on the way to the server. The script above takes advantage of this fact by making a specific call to the ‘Client’ function, performing client-side validation, and then the UI Action calls itself if the client-side validation passes.
When the UI Action calls itself it bypasses the ‘onclick’ function because the button didn’t get clicked the second time. So the script continues to the first point where there is something to execute. At that point you can call your Server-side function! The only thing you need to be careful of is that you only call the Server-side function if the script isn’t running in the client anymore. That’s what the check in the middle does…and eliminates any browser errors saying that ‘current’ (or any other Server-side function or object) isn’t defined.

Here is a solution I’ve used in the past to give users the ability to reopen an incident record. The solution uses a UI Action button to check if the ‘Comments’ field has been filled in (this is the ‘Client-side’ portion). If the validation passes, then the incident record gets updated.

//Client-side 'onclick' function
function reopen(){
if(g_form.getValue('comments') == ''){
//Remove any existing field message, set comments mandatory, and show a new field message
g_form.hideFieldMsg('comments');
g_form.setMandatory('comments', true);
g_form.showFieldMsg('comments','Comments are mandatory when reopening an Incident.','error');
return false; //Abort submission
}
//Call the UI Action and skip the 'onclick' function
gsftSubmit(null, g_form.getFormElement(), 'reopen_incident'); //MUST call the 'Action name' set in this UI Action
}

//Code that runs without 'onclick'
//Ensure call to server-side function with no browser errors
if(typeof window == 'undefined')
serverReopen();

function serverReopen(){
//Set the 'State' to 'Active', update and reload the record
current.state = 2;
current.update();
gs.addInfoMessage('Incident ' + current.number + ' reopened.');
action.setRedirectURL(current);
}

The post Client & Server Code in One UI Action appeared first on ServiceNow Guru.

Available icons

You’ve undoubtedly seen them in various parts of the system, but you may not have realized until now that there are over 250 icons available to choose from in ServiceNow! You can use these icons in various ways (client scripts, UI pages, UI macros, CMS and Service Portal), including with UI action buttons as I show here. All you have to do to show an icon is to add a class attribute with a name matching the icon you want to add.
ServiceNow had previously published a style guide that you could reference to view all of the icons (and corresponding names to reference them by). For the time being, you can access the icons in your instance by navigating to https://your_instance.service-now.com/styles/retina_icons/retina_icons.html

The following script can be used in any standard form client script. The key is the ‘transformButton’ function at the bottom. This could be included as a global UI script in your instance so that you don’t need to include it in every single client script. Once that’s in place (whether in a global UI script or in the client script itself) you can just call ‘transformButton’ with the appropriate parameters to change the button however you want. The parameters used are as follows…

1. UI action name (Mandatory)
2. Button background color (Optional)
3. Button text color (Optional)
4. Button icon name (Optional)

function onLoad() {
//Change the color of the 'Approve' button to green
transformButton('approve', '#77FF77', 'white', 'icon-success-circle');
//Change the color of the 'Reject' button to red
transformButton('reject', '#FF0022', 'white', 'icon-error-circle');
//Change the color of the 'Info' button to blue
transformButton('info_button', '#31708f', 'white', 'icon-info');
//Change the color of the 'Warning' button to yellow
transformButton('warning_button', '#f0ad4e', 'white', 'icon-warning-circle');
}

function transformButton(buttonID, buttonBackgroundColor, buttonTextColor, buttonIconName) {
try{
//Find the button(s) by ID and change the background color
$$('button[id=' + buttonID + ']').each(function(elmt) {
elmt.style.backgroundColor = buttonBackgroundColor;
if(buttonTextColor){
elmt.style.color = buttonTextColor;
}
if(buttonIconName){
elmt.addClassName(buttonIconName);
//Add some spacing between the icon and button label
elmt.innerHTML = ' ' + elmt.innerHTML;
}
});
}catch(e){}
}

The post Adding Icons to UI Action Buttons in ServiceNow appeared first on ServiceNow Guru.

After a bit of digging I was able to figure out how ServiceNow was doing those types of comparisons for update sets and versions of Business Rules, UI Pages, etc. They’ve got a number of different script includes that take the different XML updates and then compare the various parts of them. Down under the hood a little ways there is a class that they have to compare between actual text values. Finding this was the jackpot since it allowed comparing just those things that I specifically wanted to compare. Using this class ended up being a relatively straight forward process, but did require some scripting (including JellyScript) so it may not be for the faint of heart.

Here’s what it will look like in the end:

Here’s what it will look like in the end.

The biggest part of the challenge is handled by ServiceNow’s “Differ” script include. This class has one main function that is used to do the magic: diff.

The diff function takes in four parameters:

• text1 – The first string that will be used in the comparison
• text2 – The second string that will be used in the comparison
• name – The label for what you’re comparing. Most likely going to be the field label for the field that you’re comparing. This shows up in a column on the left of the comparison
• change – An optional parameter that indicates whether to return a result if the strings match

The end result of this is a string with part of a table that contains the strings compared line by line just like you see when comparing differences in an update set preview. It is assumed that the results of this function will be included as part of a table given a specific class so that the rows are formatted correctly. There is an XML header that needs to be removed from the result but essentially this is the code needed to use the result of the function:

diff_output
<table class="diff">
<thead>
<tr>
<th class="top_left_field"></th>
<th class="texttitle" colspan="2">Left Label</th>
<th class="texttitle" colspan="2">Right Label</th>
</tr>
</thead>
</table>

The left and right labels identify what it is that you’re comparing and diff_output is the output from the function.

There are a few different ways this functionality can be used; after looking at it I opted to use a GlideDialog window and UI Page to provide a similar experience to what is already used in other places. To do this a UI Action needs to be set up to open the dialog window and a UI Page needs to be set up to do the comparison and show the results.

Here’s what’s needed in the UI Action. It’s relatively generic other than the UI Page that is referenced. For the fields that I haven’t included you can use the default or adjust it as you see fit:

Name: Compare to Current
Table: table that has the version info you want to compare to the current record
Client: true
Form link: true
List context menu: true
Onclick: showTheComparison()
Script:

function showTheComparison(){
//Get the values to pass into the dialog
var sysId;
// Check if this is called from a List, if so, rowSysId will have the Sys ID of the record
if (typeof rowSysId == 'undefined'){
// If it's called from a form, get the Sys ID of the record being shown
sysId = gel('sys_uniqueValue').value;
} else {
sysId = rowSysId;
}
//Initialize and open the dialog
//Instantiate the comparison dialog, the parameter is the name of your UI Page
var dialog = new GlideDialogWindow("compare_kb_to_version");
//Set the dialog title
dialog.setTitle("Version Comparison");
// Pass in the Sys ID of the record to compare
dialog.setPreference("version_sys_id", sysId);
// Set the size
dialog.setSize('95%', '95%');
//Open the dialog
dialog.render();
}

The UI Page has a little more to it since it needs to reference the specific tables and fields where the two versions are stored.

Name: compare_kb_to_version
HTML:

<!--?xml version="1.0" encoding="utf-8" ?-->


/*
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Diff magic using the SN Differ class
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
*/
// variable to aggregate the differences for the fields into
var diff = "";
// get the SN object that does the comparing
var differ = new Differ();

/*
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Values needed for the diff
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
*/
var left_label = "Published Article";
var right_label = "";

// Get the version info from the dialog property
var version_sys_id = RP.getWindowProperties().get('version_sys_id');
var version = new GlideRecord("kb_submission");
if (version.get(version_sys_id)) {
// Set the label for the right side of the difference
right_label = "Version: " + version.number;

/*~~~~~ Short Description field ~~~~~*/
// Pass in the strings that need comparing
// u_kb_article is the reference field that refers to the KB article that the version is for
var kb_short_description = differ.diff(version.u_kb_article.short_description, version.short_description, version.short_description.getLabel(), true);
// Strip off the XML header
var starter = kb_short_description.indexOf("?>");
kb_short_description = kb_short_description.substring(starter + 2);
diff += kb_short_description;

/*~~~~~ Text field ~~~~~*/
// Pass in the strings that need comparing
var kb_text = differ.diff(version.u_kb_article.text, version.text, version.text.getLabel(), true);
// Strip off the XML header
var starter = kb_text.indexOf("?>");
kb_text = kb_text.substring(starter + 2);
diff += kb_text;

}

 

${diff}
<table class="diff">
<thead>
<tr>
<th class="top_left_field"></th>
<th class="texttitle" colspan="2">${left_label}</th>
<th class="texttitle" colspan="2">${right_label}</th>
</tr>
</thead>
</table>

The first section of code in the g:evaluate tag sets up the Differ class. Then we get the labels for each side of the comparison. Then we get the comparisons for each of the fields that we’re trying to compare. In this case we’re comparing the Short Description and the Text fields. The comparison results are cleaned up and concatenated in a string to be used inside the wrapper HTML code that sets up the header and styles.

Once you’ve got all of that in place, seeing the differences is just a click or two away.

The post Comparing the Differences Between Two Strings appeared first on ServiceNow Guru.

To help answer this question, we recently created a really useful admin utility to find all places where a record is referenced. In this article I’ll show you how you can set it up in your instance!

The script to produce the kind of data you need in this case could be run from a variety of places. We chose to make it a simple global UI action so that it would be easy to access and use on any record in the system. The UI action works by first querying the system dictionary for all reference, document_id, and condition fields that reference the table you initiate the action from. It filters out unnecessary system and log tables. Then it iterates through all of the remaining records, performing a table/sys_id query on each table where a match exists. The query results are then output to the browser in an information message.

You can set the UI action up in your instance by creating a new UI action with the following settings. Once set up, you’ll have a ‘Find Record References’ link at the bottom of each form in your system.

//Client-side 'onclick' function
function confirmFindReferences() {
if (confirm('Performing this action will query multiple tables and records and may take a long time to complete. Are you sure you want to continue?') == false) {
return false; //Abort submission
}
//Call the UI Action and skip the 'onclick' function
gsftSubmit(null, g_form.getFormElement(), 'find_references'); //MUST call the 'Action name' set in this UI Action
}//Code that runs without 'onclick'
//Ensure call to server-side function with no browser errors
if (typeof window == 'undefined')
findReferences();//Server-side function
function findReferences() {
var msg = '<b>Matching tables and columns where this record is referenced (if any) are displayed below...</b>
';
var refTable = new TableUtils(current.getTableName()).getTables();
gs.include("j2js");
refTable = j2js(refTable).join();
var refRecordID = current.sys_id;
//Query dictionary table for reference, document_id, and condition fields
var dict = new GlideRecord('sys_dictionary');
dict.addQuery('reference', 'IN', refTable).addOrCondition('internal_type', 'document_id').addOrCondition('internal_type', 'conditions');
//Do not query audit and log fields
dict.addQuery('name', 'DOES NOT CONTAIN', 'var__m_');
dict.addQuery('name', 'DOES NOT CONTAIN', 'ecc_');
dict.addQuery('name', 'DOES NOT CONTAIN', 'ha_');
dict.addQuery('name', 'DOES NOT CONTAIN', 'syslog');
dict.addQuery('name', 'DOES NOT CONTAIN', 'sys_history');
dict.addQuery('name', 'DOES NOT CONTAIN', '_log');
dict.addQuery('name', 'DOES NOT CONTAIN', 'text_search');
dict.addQuery('name', 'DOES NOT CONTAIN', 'ts_');
dict.addQuery('name', 'DOES NOT CONTAIN', 'sys_watermark');
dict.addQuery('name', 'DOES NOT CONTAIN', 'sys_audit');
dict.addQuery('name', 'NOT LIKE', 'v_%');
dict.orderBy('name');
dict.orderBy('element');
dict.query();
while (dict.next()) {
var tblName = dict.name.toString();
// Skip tables used for Table Rotation
var gr = new GlideRecord("sys_table_rotation_schedule");
gr.addQuery("name.name", '!=', tblName);
gr.addQuery("table_name", tblName);
gr.query();
if (!gr.hasNext()) {
var recMessage = ' records found';
var filterOperator = '=';
var refType = dict.internal_type;
if (refType == 'glide_list' || refType == 'conditions') {
filterOperator = 'LIKE';
}

//Query each table for matching records
var rec = new GlideRecord(tblName);
if (refType == 'glide_list' || refType == 'conditions') {
rec.addQuery(dict.element, 'CONTAINS', refRecordID);
} else {
rec.addQuery(dict.element, refRecordID);
}
rec.query();
if (rec.getRowCount() == 1) {
recMessage = ' record found';
}
if (rec.getRowCount() > 0) {
//Display table/column info
msg = msg + '<b>Table: </b><i>' + tblName + '</i>' + ' - <b>Column [Column type]: </b><i>' + dict.element + '</i> [' + dict.internal_type + ']' + ' --- ' + '<span style="color: #136fb0;">' + '<a href="' + dict.name + '_list.do?sysparm_query=' + dict.element + filterOperator + refRecordID + '" target="_blank" rel="noopener">' + rec.getRowCount() + recMessage + '</a></span>.
' + '';
}
}
}

//Query for workflow variable values
tblName = 'sys_variable_value';
var vVal = new GlideRecord(tblName);
vVal.addQuery('value', 'CONTAINS', refRecordID);
vVal.query();
if (vVal.getRowCount() == 1) {
recMessage = ' record found';
}

if (vVal.getRowCount() > 0) {
//Display table/column info
msg = msg + '<b>Table: </b><i>' + tblName + '</i>' + ' - <b>Column [Column type]: </b><i>' + 'value' + '</i> [' + 'string' + ']' + ' --- ' + '<span style="color: #136fb0;">' + '<a href="' + tblName + '_list.do?sysparm_query=' + 'valueLIKE' + refRecordID + '" target="_blank" rel="noopener">' + vVal.getRowCount() + recMessage + '</a></span>' + '.' + '';
}

gs.addInfoMessage(msg);
action.setRedirectURL(current);
}

The post Find all System References to a Specific Record appeared first on ServiceNow Guru.

Okay, enough of the high-pressure sales pitch :). This morning I saw a post on the ServiceNow community asking how to display a google map based on a ServiceNow location record. I think this is a fantastic idea and I decided to see if I could come up with a couple of solutions for it this morning. In this post I’ll show you how you can set up a UI Action or UI Macro link to pop open a Google Map window based on any location record in your ServiceNow instance!

The simplest way to set up a Google Map link in ServiceNow is to use a client-side UI Action to construct the URL and pop open a new window. Google Map URLs follow a basic convention that can include the street address or latitude/longitude coordinates of the location you want to display like this…

http://maps.google.com/?q=1200 Pennsylvania Ave SE, Washington, District of Columbia, 20003

Here’s the code and configuration for a UI Action…

//Pop open google maps window of location specified
//URL should follow this format...http://maps.google.com/?q=1200 Pennsylvania Ave SE, Washington, District of Columbia, 20003

function openGoogleMap() {
//Retrieve the 'Location' record
var sysId = typeof rowSysId == 'undefined' ? gel('sys_uniqueValue').value : rowSysId;
var gr = new GlideRecord('cmn_location');
gr.get(sysId);

//Create and display the Map URL
var mapURL = "http://maps.google.com/?q=";
//Try location and city
if(gr.street && gr.city){
mapURL = mapURL + gr.street + ',' + gr.city + ',' + gr.state + ',' + gr.zip + ',' + gr.country;
}
//Else try latitude and longitude
else if(gr.latitude && gr.longitude){
mapURL = mapURL + gr.latitude + ',' + gr.longitude;
}
//Strip '#' symbols to avoid encoding errors
mapURL = mapURL.replace(/#/g, "");
window.open(mapURL);
}

Once the UI Action is created, you should be able to right-click a location record from a list or click the ‘Open Google Map’ link as shown here…

While it’s nice to be able to open a map from a location record, it’s probably even more useful to be able to open a map directly from a referenced location record. The code for the UI Macro is a bit more complex, but once created it’s very simple to include for any reference field in your system by personalizing the dictionary entry for the location reference field (or the ‘cmn_location’ table dictionary entry if you want to include the option on every location field in your system). You can create the UI Macro using these settings.

<!--?xml version="1.0" encoding="utf-8" ?-->



var id = __ref__.getSysIdValue();
if (id == null)
"none";
else {
var loc = new GlideRecord('cmn_location');
loc.get(id);
if ((!loc.street.nil() $[AND] !loc.city.nil()) || (!loc.latitude.nil() $[AND] !loc.longitude.nil()))
"";
else
"none";
}

<a id="${jvar_n}" style="display: $[jvar_show_google_map_display];" title="${gs.getMessage('Open a Google Map for this location')}" name="${jvar_n}"></a>
<img src="https://servicenowguru.com/wp-content/uploads/2024/02/favicon2." width="16" height="16" border="0" />

<script>
      needsRefreshLoc = false;
      function onChange_location_show_google_map(element, original, changed, loading) {
         var s = '${ref}'.split('.');
         var referenceField = s[1];
         if (needsRefreshLoc == false) {
            needsRefreshLoc = true;
            return;
         }
         if (changed.length == 0) {
            $('${jvar_n}').hide();
            return;
         }
         var locRec = g_form.getReference(referenceField, locationGoogleMapReturn);
      }
      
      function locationGoogleMapReturn(locRec) {
         var e = $('${jvar_n}');
         if ((locRec.street $[AND] locRec.city) || (locRec.latitude $[AND] locRec.longitude))
            e.show();
         else
            e.hide();
      }
      
      //Event.observe(g_form.getControl(${ref}.split('.')[1]), 'change', onChange_cmn_location_show_google_map);
      var l = new GlideEventHandler('onChange_location_show_google_map', onChange_location_show_google_map, '${ref}');
      g_event_handlers.push(l);

      //Pop open google maps window of location specified
      //URL should follow this format...http://maps.google.com/?q=1200 Pennsylvania Ave SE, Washington, District of Columbia, 20003

      function openGoogleMap(reference) {
         var s = reference.split('.');
         var referenceField = reference.substring(s[0].length+1);
         var sysId = g_form.getValue(referenceField);
         //Retrieve the 'Location' record
         var gr = new GlideRecord('cmn_location');
         gr.get(sysId);

         //Create and display the Map URL
         var mapURL = "http://maps.google.com/?q=";
         //Try location and city
         if(gr.street $[AND] gr.city){
            mapURL = mapURL + gr.street + ',' + gr.city + ',' + gr.state + ',' + gr.zip + ',' + gr.country;
         }
         //Else try latitude and longitude
         else if(gr.latitude $[AND] gr.longitude){
            mapURL = mapURL + gr.latitude + ',' + gr.longitude;
         }
	 //Strip '#' symbols to avoid encoding errors
	 mapURL = mapURL.replace(/#/g, "");
         window.open(mapURL);
      }
   </script>

Once the UI Macro has been created and the ‘ref_contributions’ attribute added to the reference field or table dictionary, you should see a google map icon displayed next to your location field whenever a location is referenced that includes address or latitude/longitude details.

The post Open Google Map from a Location Record appeared first on ServiceNow Guru.

You’re probably already familiar with the ‘Show Workflow’ and ‘Workflow Context’ UI action links that show up on task records when a workflow gets associated to it. These UI actions are extremely useful for identifying the state of the workflow as it relates to the task. As an administrator or consultant, you’re often trying to troubleshoot or fix an issue with the workflow, which requires the workflow editor. Unfortunately, this means you have to navigate to the module in the left nav and try to find the correct workflow to edit. The purpose of this solution is to cut out all of those extra steps to allow you to edit the workflow directly from the record it is bound to.

Here’s the UI action code. Notice that it’s set on the ‘Global’ table so that it will be available as a link on every form in the system. You’ll also notice that the ‘Condition’ field is set to allow access to only the ‘workflow_admin’ role, and to only display the link when a record has a workflow associated to it.

function showWorkflowEditor(){
//Open the workflow editor in a new window
var wf = new GlideRecord('wf_context');
wf.addQuery('id', g_form.getUniqueValue());
wf.query();
if(wf.next()){
getTopWindow().popupOpenFocus('workflow_ide.do?sysparm_sys_id=' + wf.workflow_version, 'show_workflow_version', 950, 700, '', false, false);
}
}

If you’ve set this global UI action up correctly, clicking the ‘Edit Workflow’ link will take you directly to the workflow editor for that specific workflow just as if you would have navigated through the module in the left nav.

Bonus UI Action

Here’s another UI action that you can use on the ‘wf_context’ table so that you have a quick link to edit the workflow when viewing a given workflow context. The basic ideas are the same, but the script and condition are a little bit different because we’re initiating the UI action from a specific table in this case.

function showWorkflowEditor(){
//Open the workflow editor in a new window
var id = g_form.getValue('workflow_version');
getTopWindow().popupOpenFocus('workflow_ide.do?sysparm_sys_id=' + id, 'show_workflow_version', 950, 700, '', false, false);
}

The post Edit a Workflow Directly from a Task appeared first on ServiceNow Guru.