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.

Standard User Criteria definition form.

You can then take that UC and add/configure it on a record as having the ability to ‘can read’ or ‘cannot read’ a specific record or group of records. Probably the best known examples of this are in the OOB knowledge base and service catalog applications.

KB article can read/cannot read attributes.

Available For/Not Available For related lists on catalog items.

But what if you wanted to be able to use UCs in your custom applications? The following will provide a roadmap on how you can do this!

Lets review how UCs are used for security by using the concrete example from the knowledge base. For the Knowledge Base application there are can/cannot read attributes (KB article image shown above) on the knowledge base record as well as the individual article record.  On the knowledge base record the UCs are associated as related records and shown in the ‘Can Read’/’Cannot Read’ related lists.  On the knowledge base articles there are ‘Can Read’/’Cannot Read’ glidelist attributes directly on the record. The way the OOB security has been implemented to use those UC lists for determining if a user can read a specific knowledge article is that:

• A user can NOT be in either the knowledge base or knowledge articles cannot read UCs to be able to read the article.
• If there is a can read UC on the knowledge base, the user must be in the UC to read articles in the knowledge base.
• If there is a can read UC on the knowledge article, the user must be in the UC to read the article.
• If there are no UCs defined on either the knowledge base or article, the article is readable by any user.

This is generally the way the service catalog works as well with the catalog and catalog item.

There are other OOB applications using UCs as well….but what if you wanted to be able to use UCs in other places to control access that aren’t part of OOB applications? To do this you’d need some way of evaluating a user’s inclusion in a UC. Thankfully there is a scriptable object that does just that: UserCriteriaLoader.

UserCriteriaLoader was a utility Mike Sherman (also a long-time ServiceNow employee) and I found while trying to develop the security model for a project we were working on. It’s relatively easy to use and just needs some fairly straightforward wrappers to make it reusable for our needs.

UserCriteriaLoader has several functions on it but the main one we are going to make use of is userMatches(). The userMatches() function takes the sys_id of a user you’re checking and an array of UC sys_id’s to check against. The function then evaluates the user against those UCs and returns true/false indicating if the user is in at least one of the UCs. Something like the following:

var user = gs.getUserId();
var uc_array = [“<uc_sys_id_1>”, “<uc_sys_id_2>”, “<uc_sys_id_3>”];
var ret = sn_uc.UserCriteriaLoader.userMatches( user, uc_array);

The above is a great start as it gives you the base capability to evaluate a user’s inclusion in a set of UCs, but what we really want is a utility that wrappers the above to give us a generic way of evaluating users against UCs in the way that UCs tend to be applied to records.

The script include below gives us a reusable utility to do this in the two primary ways we found UCs to be applied to a record:

• A glidelist directly on the record
• A related list with references to the record and to UC’s

UserCriteriaLoader.userMatches() requires the user’s sys_user record sys_id and an array of UC record sys_ids. The ‘trick’ to the utility is converting either the glidelist or a list of related records into that array of UC record sys_ids. Let’s look at some specific examples of how to use each function.

//'UserCriteriaLoader.userMatches()' Sample Usage
var gr = new GlideRecord("my_custom_table"); //Table name of record for which are evaluating access
gr.get(""); //sys_id of record for which you are evaluating access
var user_can_read = new UserCriteria(gs.getUserID(), gr).evalUCGlideList("my_can_read", "my_cannot_read");

The above would return true/false depending on the user’s inclusion in the can/cannot read UCs. A couple of things to note:

• The cannot read parameter on the function is optional.  If not included it is assumed that the user is NOT part of any cannot read UCs.
• If given a cannot read parameter, the function will take it into account by determining if the given user is part of the cannot read UC(s) and if they are returning false as the ‘answer’ as being included in any can read UCs would no longer matter for functionality to be consistent with the way OOB applications us UCs.
• The above is somewhat contrived as one of the biggest use cases is where you’d already HAVE a glide record to work with as part of the platform (BRs & ACLs mainly).

Usage when the UCs are associated via a related list is a bit more complicated….but just because data model is a more complicated. The overall approach is the same in that we want to build an array of UCs for the can read and cannot read related lists and feed them to base function. 

var gr = new GlideRecord("my_custom_table");
gr.get("");
var user_can_read = new UserCriteria(gs.getUserID(), gr).evalUCRelatedList("m2mCanTable", "canGrRefFieldName", "canUCRefFieldName", "m2mCannotTable", "cannotGrRefFieldName", "cannotUCRefFieldName");

//m2mCanTable: name of the table that contains the list of can read UCs related to the record in my_custom_table.
//canGrRefFieldName: reference to a record on my_custom_table.
//canUCRefFieldName: reference to a UC record
//m2mCannotTable: name of the table that contains the list of cannot read UCs related to the record in my_custom_table.
//cannotGrRefFieldName: reference to a record on my_custom_table
//cannotUCRefFieldName: reference to a UC record

Hopefully, this is helpful and gives the ServiceNow community an extra tool in their toolbox to solve their problems with the power of the ServiceNow platform!

var UserCriteria = Class.create();
UserCriteria.prototype = {
    /**
    name: initialize
    description: Initializes the UserCriteria object by setting the user sys_id and the GlideRecord.
    param: {String} [userSysId] - sys_id of the user we want to check.
    param: {GlideRecord} [grToCheck] - GlideRecord of the record we want to check against.
    example:
    var matches = new UserCriteria(userSysID, grToCheck)
    returns: {UserCriteria} returns a initialized UserCriteria Object.
    */
    initialize: function(userSysId, grToCheck) {
        this.userSysId = userSysId;
        this.grToCheck = grToCheck;
    },

    /**
    name: evalUCGlideList
    description: Takes the field names on the table/GlideRecord that are GlideList(s) pointing too User Criteria for "Can" & "Cannot".
    param: {String} [ucCanField] - The field name of the GlideList for the "Can" User Criteria.
    param: {String} [ucCannotField] - The field name of the GlideList for the "Cannot" User Criteria.
    example:
    var matches = new UserCriteria(userSysID, grToCheck).evalUCGlideList("<can_read_uc_glidelist_field>", "<cannot_read_uc_glidelist_field>");
    returns: {boolean} Returns true if user passed in is part of any of the user criteria in the GlideList. 
    */
    evalUCGlideList: function(ucCanField, ucCannotField) {
        var ret = false;
        if (ucCannotField)
            ret = (!this._matchesUCListField(ucCannotField, false) && this._matchesUCListField(ucCanField, true));
        else
            ret = (this._matchesUCListField(ucCanField, true));

        return ret;

    },

    /**
    name: evalUCRelatedList
    description: Takes m2m tables for can and can't mappings, with ref field names of "connectors", that connects a record to User Criteria and returns true/false indicating a users access depending on related lists.
    param: {String} [m2mCanTable] - Name of the m2m table connecting the record with User Criteria 'can' access.
    param: {String} [canGrRefFieldName] - Name of the field on the m2m table that points to the GlideRecord (can access).
    param: {String} [canUCRefFieldName] - Name of the field on the m2m table that points to the User Criteria (can access).
    param: {String} [m2mCannotTable] - Name of the m2m table connecting the record with User Criteria for can't access
    param: {String} [cannotGrRefFieldName] - Name of the field on the m2m table that points to the GlideRecord (can't access).
    param: {String} [cannotUCRefFieldName] - Name of the field on the m2m table that points to the User Criteria (can't access).
    example:
    var matches = new UserCriteria(userSysID, grToCheck).evalUCRelatedList(m2mCanTable, canGrRefFieldName, canUCRefFieldName, m2mCannotTable, cannotGrRefFieldName, cannotUCRefFieldName)
    returns: {boolean} Returns true if user passed in is part of any of the user criteria in the GlideList. 
    */
    evalUCRelatedList: function(m2mCanTable, canGrRefFieldName, canUCRefFieldName, m2mCannotTable, cannotGrRefFieldName, cannotUCRefFieldName) {

        var ret = false;
        if (m2mCannotTable)
            ret = (!this._matchesUCListTable(m2mCannotTable, cannotGrRefFieldName, cannotUCRefFieldName, false) && this._matchesUCListTable(m2mCanTable, canGrRefFieldName, canUCRefFieldName, true));
        else
            ret = this._matchesUCListTable(m2mCanTable, canGrRefFieldName, canUCRefFieldName, true);
        return ret;

    },

    /**
    name: _matchesUCListField
    description: Takes the field name on the table/GlideRecord that is a GlideList pointing too User Criteria.
    param: {String} [ucField] - The field name of the GlideList for the User Criteria.
    param: {boolean} [emptyListReturn] - What to return in case the list of criteria is empty (false: for cannot read; true: for can read).

    returns: {boolean} Returns true if user passed in is part of any of the user criteria in the GlideList. 
    */
    _matchesUCListField: function(ucField, emptyListReturn) {

        var ucArray = [];
        var ucFieldValue = this.grToCheck.getValue(ucField);
        if (ucFieldValue != null)
            ucArray = ucFieldValue.split(",");

        return (ucFieldValue && !gs.isLoggedIn()) ? !emptyListReturn : this._matchesUCList(ucArray, emptyListReturn);

    },

    /**
    name: _matchesUCListTable
    description: Takes a m2m table, with ref field names of "connectors", that connects a record to User Criteria and returns true/false indicating if the user is in any of the User Criteria.
    param: {String} [m2mTable] - Name of the m2m table connecting the record with User Criteria
    param: {String} [grRefFieldName] - Name of the field on the m2m table that points to the GlideRecord.
    param: {String} [ucRefFieldName] - Name of the field on the m2m table that points to the User Criteria.
    param: {boolean} [emptyListReturn] - What to return in case the list of criteria is empty.
    
    returns: {boolean} Returns true if user passed in is part of any of the user criteria in the GlideList. 
    */
    _matchesUCListTable: function(m2mTable, grRefFieldName, ucRefFieldName, emptyListReturn) {

        //var ret = false;
        var ucArray = this._glideQuerytoArray(m2mTable, grRefFieldName, ucRefFieldName);
        return (ucArray.length > 0 && !gs.isLoggedIn()) ? !emptyListReturn : this._matchesUCList(ucArray, emptyListReturn);

    },


    /**
    name: individualUserCriteriaCheck
    description: Used to check the includance of a user in a single User Criteria. 
    param: {String} [uc] - Sys_id of a single User Criteria
    example:
    var matches = new UserCriteria(userSysID, grToCheck).individualUserCriteriaCheck("<uc_sys_id>")
    returns: {boolean} Returns true if user passed in is part of any of the user criteria. 
    */
    individualUserCriteriaCheck: function(uc) {

        var ucArrayofOne = [uc];
        return this._matchesUCList(ucArrayofOne, false);

    },

    /**
    name: _matchesUCList
    description: Makes call to OOB UserCriteriaLoader for evaluation of user/criteria and takes in to account empty user criteria.
    param: {Array} [ucArray] - Array of relevent User Criteria sys_ids.
    param: {boolean} [emptyListReturn] - what to return if User Criteria array is empty.

    returns: {boolean} boolean if user is in User Criteria passed.
    */
    _matchesUCList: function(ucArray, emptyListReturn) {

        var ret = false;
        if (this.isEmpty(ucArray))
            ret = emptyListReturn;
        else
            ret = sn_uc.UserCriteriaLoader.userMatches(this.userSysId, ucArray);
        return ret;

    },

    /**
    name: isEmpty
    description: Checks if the inpur array is empty or contains only inactive records.
    param: {Array} [ucArray] - Array of relevent User Criteria sys_ids.
    example:
    this.isEmpty(ucArray)
    returns: {boolean} True if array has no elements or if all associated records are inactive.
    */
    isEmpty: function(ucArray) {
        if (ucArray.length == 0) {
            return true;
        }
        var ucGr = new GlideRecord("user_criteria");
        ucGr.addActiveQuery();
        ucGr.addQuery("sys_id", "IN", ucArray);
        ucGr.setLimit(1);
        ucGr.query();
        return !ucGr.hasNext();
    },

    /**
    name: getAllUserCriteria
    description: Given a user sys_id returns the list of User Criteria that user is part of.
    example:
    new UserCriteria(userSysID, grToCheck).getAllUserCriteria()
    returns: {String} - List of User Criteria sys_id's that the user is part of.
    */
    getAllUserCriteria: function() {

        return sn_uc.UserCriteriaLoader.getAllUserCriteria(this.userSysId);

    },

    /**
    name: _glideQuerytoArray
    description: An internal/hidden function used to look up the related User Criteria records and build them in to an array.
    param: {String} [m2mTable] - Name of the m2m table connecting the record with User Criteria
    param: {String} [grRefFieldName] - Name of the field on the m2m table that points to the GlideRecord.
    param: {String} [ucRefFieldName] - Name of the field on the m2m table that points to the User Criteria.
    
    returns: {Array} An array of User Criteria that are related to the record via the m2m table.
    */
    _glideQuerytoArray: function(m2mTable, grRefFieldName, ucRefFieldName) {

        var ucArray = [];
        var grUC = new GlideRecord(m2mTable);
        grUC.addQuery(grRefFieldName, this.grToCheck.sys_id);
        grUC.query();
        while (grUC.next()) {
            ucArray.push(grUC.getValue(ucRefFieldName).toString());
        }

        return ucArray;
    },

    type: 'UserCriteria'
};

The post Leveraging User Criteria in your custom applications appeared first on ServiceNow Guru.

We faced a few challenges. Our solution needed to:

• Allow data imports without elevated roles or system privileges
• Handle the same kind of data from multiple vendors (e.g. hardware asset imports)
• Handle data in a variety of file formats including text (.csv) and MS Excel
• Provide feedback to the client’s IT asset management (ITAM) team
• Check data quality and handle exception conditions defined by the client
• Run with minimal input or intervention

We started to create custom inbound email actions to process emails with attached data files as they were sent from each vendor. But we discovered some big downsides with that approach. The most serious was that there’s no way to validate the accuracy or completeness of data being sent by the vendor. Inbound email processing also leaves the client at the mercy of the vendor for the timing of the imports. Finally, it isn’t very scalable, since a new inbound email action must be created for every additional vendor and/or data source.

What the client needed was a simple way to perform a data import via a Data Source with a file attachment. They were already aware of the Load Data wizard that ServiceNow provides on the System Import Sets application menu, but that solution isn’t very user-friendly, and it requires a lot of manual input each time new data are imported.

Asset Import Wizard

To make it easy for the client’s ITAM team to import their data in to ServiceNow, we leveraged the power of the Service Catalog. Specifically, we created a Record Producer to provide a simple front-end for importing vendors’ data files. Record producers can be used by anyone. Their visibility can be limited to only interested parties. Files attached to the record producer are automatically attached to the record that is created. And the record producer’s Script field enables powerful data processing.

We configured the record producer as follows:

• Name: Asset Import Wizard
• Table: Data Source (sys_data_source)

We added a single variable to the record producer, a lookup select box to allow the ITAM team member to specify the type of import they were performing.

Import Wizard Configurations

The variable gets its list of options from a custom table called Import Wizard Configurations. This table allowed us to build a flexible framework for defining different types of file imports from any vendor.
This table has many fields that are similar to those on a Data Source record. That’s because the record producer’s script queries the Wizard Configuration table for values to use when it inserts a new record in the Data Source table. Here’s how this form breaks down.

• Vendor references records in the Company (core_company) table where the Vendor field is set to true.
• Import Type is a choice list of options that describe the kind of asset data being imported (e.g. Hardware, Software, HW end of life disposal, or Lease Contract)
• Expected Data Format is a choice list that allows either CSV or Excel (.xls) formats to be specified.
• Data Source Name Prefix is a text field for naming the data source. The record producer’s script automatically appends the current time/date stamp to the prefix when each Data Source record is created
• Import Set Table and Transform Map are fields that reference records in those tables. The import set and transform map to be used for these data imports must be created in advance.
• Header Row and Sheet Number are used to specify values when an MS Excel file is the data source. For CSV files, the client just sets these to 1.
• Active allows the client to deactivate the Wizard Configuration record if it is no longer needed. The Lookup Select Box variable on the record producer displays only active records in this table.

Record Producer Script

The record producer’s script is where the wizard’s “magic” happens. The script does several things:

1. It validates that the wizard is submitted with an attachment of the correct format.
2. It queries the Wizard Configuration table for the record that is selected in the Import Type variable, and inserts a new record into the Data Source table using values from the Wizard Configuration record.
3. It imports the data into the import set table and executes the specified transform map, using ServiceNow helper functions GlideImportSetLoader and GlideImportSetTransformerWorker
4. It provides feedback to the user upon successful execution of the script, or displays appropriate error messages if the script encountered errors. Error conditions cause the record producer to abort without creating the Data Source record.

Here is the code we used in the record producer script:

// Verify attachment is included and in correct format
var gr2 = new GlideRecord("sys_attachment");
gr2.addQuery("table_sys_id", current.sys_id);
var oC = gr2.addQuery("table_name", "sys_data_source");
oC.addOrCondition("table_name", "sc_cart_item");
gr2.query();
if (!gr2.next()) {
gs.addErrorMessage("You must attach a file to submit. Your import submission has been aborted.");
current.setAbortAction(true);
producer.redirect="com.glideapp.servicecatalog_cat_item_view.do?v=1&sysparm_id=<SysID of the Record Producer>";
}
else{
//Get the glide record for the selected import type
var gr = new GlideRecord('u_pmy_imp_wiz_cfg');
gr.addQuery('sys_id',producer.import_type);
gr.query();
if(gr.next()){
if(gr2.getRowCount() > 1){
gs.addErrorMessage("You may only attach one file at a time for this import wizard. Your import submission has been aborted.");
current.setAbortAction(true);
producer.redirect="com.glideapp.servicecatalog_cat_item_view.do?v=1&sysparm_id=<SysID of the Record Producer>";
}
//check to make sure the file format is correct on the attachment
var passedFormatCheck = false;
var errorCaught = true;
if (gr.u_format == 'CSV'){
if (gr2.file_name.endsWith('.csv') == true){
passedFormatCheck = true;
}
else{
gs.addErrorMessage("This import type is expecting submission of a CSV file (.csv), but a different file format was attached. Your import submission has been aborted.");
current.setAbortAction(true);
producer.redirect="com.glideapp.servicecatalog_cat_item_view.do?v=1&sysparm_id=<SysID of the Record Producer>";
}
}
else if (gr.u_format == 'Excel'){
if(gr2.file_name.endsWith('.xls') == true){
passedFormatCheck = true;
}
else{
gs.addErrorMessage("This import type is expecting submission of an Excel file (.xls), but a different file format was attached. Your import submission has been aborted.");
current.setAbortAction(true);
producer.redirect="com.glideapp.servicecatalog_cat_item_view.do?v=1&sysparm_id=<SysID of the Record Producer>";
}
}

if(passedFormatCheck == true){
// Create data source record (based on form import type selection record)

current.name = gr.u_ds_naming + '_' + gs.nowDateTime();
current.format = gr.u_format;
current.import_set_table_name = gr.u_import_set.name;
current.header_row = gr.u_header_row;
current.sheet_number = gr.u_sheet_number;
current.file_retrieval_method = "Attachment";
current.type = "File";

//Data source needs to be created before we can trigger the commands below, so we create the record outside of the normal record producer method
current.insert();

// Process file into data source record
var loader = new GlideImportSetLoader();
var importSetRec = loader.getImportSetGr(current);

// Import data from data source to import set table (based on form import type selection record)
var ranload = loader.loadImportSetTable(importSetRec, current);
importSetRec.state = "loaded";
importSetRec.update();

// Start appropriate transform map (will have the logic for logging exceptions within the transform map scripts, and will trigger an email once complete to the import submitter with an outline of the logged errors and warnings)
var transformMapID = gr.u_transform;
var transformWorker = new GlideImportSetTransformerWorker(importSetRec.sys_id, transformMapID);
transformWorker.setBackground(true);
transformWorker.start();

//Inform the user that a email outlining the status of the import will be sent once the import is complete
gs.addInfoMessage("Your import file has been submitted. An email will be sent to you once the import is completed to outline any errors or warnings encountered while importing.");
producer.redirect="home.do";
}

}
else{
gs.addErrorMessage('Something went wrong with the import. Please contact a system admin to investigate.');
}

// Since we inserted the data source already, abort additional insert by record producer
current.setAbortAction(true);
}

The Miracle of Transform Map Scripts

If the wizard works magic on the front end, then Transform Maps do the same on the back end. Even when the client’s ITAM team have an opportunity to review the vendors’ data before importing it, there can still be errors. We identified all of the potential failure points in each vendor’s data.

We used onBefore transform scripts to check each source row for exceptions before the source fields are mapped into the target table. We grouped these exceptions into two categories; Errors and Warnings. The transform scripts use log.error() and log.warn() to write exceptions to the import set log for each import run. Error exceptions cause the source row to be skipped by setting the ignore variable to true. Warning exceptions are logged, but the source row is transformed.

We also determined whether other tables required records to be updated or inserted as a result of the data import. For example, lease contract imports contain information about the hardware assets that are under lease. As each hardware record is updated, a related record has to be created in the Assets Covered table, in order to associate that asset with its lease contract.

We used onAfter transform scripts to handle these secondary table updates. OnAfter scripts run after the source record has been transformed into the target table. These scripts also logged exceptions if any were encountered during the update.

After all of the source rows have been evaluated and/or transformed, an onComplete script compiles the exceptions from the import set log in a block of text, then queues a system event. The user who initiated the import receives a notification containing that block of text. The notification provides feedback in near-real time, and lists exceptions from the import set log that would normally only be available to administrators.

The Universal Translator

The final cog in this machine is the Vendor Model Translation table. We created this custom table because the client found that their vendors used model identifiers that did not match the names in the client’s Model (cmdb_model) table. The lack of a common name, model number or some other identifier in the vendor’s data makes it impossible to match asset models.

The vendor translation table is a simple cross-reference table that associates a vendor’s name for a given model with the model record in ServiceNow. It contains just four fields:

• Vendor is a reference to a vendor record in the Company (core_company) table
• Active is a true/false value that can be used to filter records during queries
• Vendor Model is a text field that stores the identifier used by the vendor to reference the model
• ServiceNow Model is a reference to a record in the Model (cmdb_model) table

With this simple table it is possible to establish aliases for any number of models and/or vendors. It can also be used as a kind of normalization table, listing several vendor models that all refer to the same model in the client’s Model table. In the example below, three mobile phone models provided by the vendor are all associated with a single model in ServiceNow:

We put this table to use in a couple of ways. The transform map for hardware asset imports used an onBefore script to set values in the target Hardware (alm_hardware) table based on a match in the vendor translation table. This script also illustrates some of the exception logging we performed for the client:

(function runTransformScript(source, map, log, target /*undefined onStart*/ ) {
var errorCondition = false;
var itemModel = '';
var modelDisp = '';
var vendorName = 'Name of the vendor company';
//Set the sourceRow variable to allow for input into the log statements the row from the source that failed
var sourceRow = source.sys_import_row + 2;
var excPrefix = 'Exception: Asset Import HW ASN ' + vendorName + ': Source Data Row ' + sourceRow + ': ';
// Do not transform the source record unless the source u_inventory_category field contains the text string "computer.system". This filters out non-computer hardware that may be included in the ASN. Log an Error if the inventory category value does not contain ".Computer Systems.".
if (source.u_inventory_category.indexOf('.COMPUTER SYSTEMS.') == -1){
//log.error(excPrefix + 'Item not a hardware asset (inventory category field does not contain ".computer system.")');
errorCondition = true;
}
else{
// Check for empty vendor model number in source, Log ERROR with message per error exception
if(JSUtil.nil(source.u_mfg_part_num) == true){
log.error(excPrefix + 'Manufacturer part number (mfg part num) missing in source data');
errorCondition = true;
}
else{
// Perform lookup against custom translation table, using vendor and vendor model key as the unique identifiers
//query the table for model translations for the selected vendor
var gr2 = new GlideRecord('u_vendor_translation');
gr2.addQuery('u_vendor', 'SysID of the vendor's record in the core_company table');
gr2.addQuery('u_vendor_model', source.u_mfg_part_num);
gr2.addActiveQuery();
gr2.query();
// Confirm if match found, if not, raise ERROR exception into import log
if(gr2.next()){
//Set the MODEL FIELD on the record so we don't need to query the table again in a field map script
source.model = gr2.u_sn_model.sys_id;
target.model = gr2.u_sn_model;
//set the itemModel field for use in another query later
itemModel = gr2.u_sn_model;
modelDisp = gr2.u_sn_model.getDisplayValue();
target.model_category = gr2.u_sn_model.cmdb_model_category;
}
else{
log.error(excPrefix + 'Source model ' + source.u_mfg_part_num + ' does not match a model in Service-Now. Check vendor translation table to ensure a translation is set up.');
errorCondition = true;
}
}

// Check for empty serial number, if empty, Log ERROR with message per error exception logging section below
if (JSUtil.nil(source.u_serial_num) == true){
log.error(excPrefix + 'Serial number missing in source data');
errorCondition = true;
}
else{
// Confirm that the serial number does not exist in the alm_asset table already as that same model, if so, raise ERROR exception
var modelDesc = '';
var gr1 = new GlideRecord('alm_asset');
gr1.addQuery('serial_number', source.u_serial_num);
//If we found a model through the translation table record, perform an additional filter on the model to make sure we don't have a duplicate model + serial number combo
if (itemModel != ''){
gr1.addQuery('model', itemModel);
//set the modelDesc variable to include that optionally in an error code if applicable
modelDesc = 'with model number ' + modelDisp + ' ';
}
gr1.query();
if(gr1.next()){
log.error(excPrefix + 'Serial number ' + source.u_serial_num + ' ' + modelDesc + 'matches existing hardware asset record ' + gr1.getDisplayValue());
errorCondition = true;
}
}

if(JSUtil.nil(source.u_customer_po_num) == true){
log.error(excPrefix + 'Customer PO Num field is empty on this record.');
errorCondition = true;
}
else{
// Perform lookup on the PO Line item table based on the PO provided and the model of hardware *Assume no more than one line item per model
var enc = 'purchase_order.u_po_number='+ source.u_customer_po_num +'^purchase_order.status!=canceled^model=' + itemModel;
var pol = new GlideRecord('proc_po_item');
pol.addEncodedQuery(enc);
pol.orderByDesc('sys_created_on');
pol.query();
if(pol.next()){
//Set the source record's Purchase Line to the sys_id of the Purchase Order Line Item
source.purchase_line = pol.sys_id;
}
else{
log.error(excPrefix + 'Unable to find a PO Line item under PO number ' + source.u_customer_po_num + ' for product model ' + modelDisp + '. This could be an issue with the vendor translation table record, or the wrong model was selected on a line item.');
errorCondition = true;
}
}

// If the Vendor is not found in ServiceNow's core_company table, log a Warning
var gr3 = new GlideRecord('core_company');
gr3.addQuery('sys_id', 'bbb81b896f8641009e4decd0be3ee4b1'); //sys_id of the vendor from the core_company table
gr3.query();
if(!gr3.next()){
log.warn(excPrefix + 'Vendor ABCDEFG (bbb81b896f8641009e4decd0be3ee4b1) does not match a record in the ServiceNow.');
}

// Check if the source record's quantity is greater than 1, if so, Log ERROR with message per error exception logging section below
if(source.u_qty_shipped > 1){
log.error(excPrefix + 'QTY shipped is greater than 1.');
errorCondition = true;
}
}

//Skip importing the source record if the transfer map is attempting to update an existing record
if (action == "update" && errorCondition != true){
log.error(excPrefix + 'Record is attempting to update an existing record.');
ignore = true;
}
// Skip importing the source record if any exceptions are found (ignore = true )
if(errorCondition == true){
ignore = true;
}

})(source, map, log, target);

For mobile device imports we wrote an onStart transform script that uses the Vendor Model Translation table to update source rows before any source rows are transformed. That reduces the ITAM team’s administrative task load by leveraging information already in ServiceNow’s Model table.

(function runTransformScript(source, map, log, target /*undefined onStart*/ ) {
/***
* Before transforming any source rows we'll try to match them to models using the
* Vendor Model Translation table or the ServiceNow Model table
***/
log.info('import set ' + import_set.sys_id.toString());
//Query the import set table for rows in the import set we're transforming
var row = new GlideRecord('u_mobile_device_import');
row.addQuery('sys_import_set', import_set.sys_id.toString());
row.query();
while(row.next()){
if(!row.u_device_model_.nil()){
//Check the Vendor Model Translation table for a record that matches the source's Device Model
var gr2 = new GlideRecord('u_vendor_translation');
gr2.addQuery('u_vendor_model', row.u_device_model_);
gr2.addActiveQuery();
gr2.query();
if(gr2.next()){
//Set the MODEL FIELD on the row so that we can coalesce
row.u_device_model_ = gr2.u_sn_model.sys_id.toString();
row.update();
}
else{
//No match in the Vendor Translation table, so check ServiceNow's Model table for a match
var gr3 = new GlideRecord('cmdb_model');
gr3.addQuery('display_name', row.u_device_model_);
gr3.addQuery('status','In Production');
gr3.query();
if(gr3.next()){
//Set the MODEL FIELD on the row so that we can coalesce
row.u_device_model_ = gr3.sys_id.toString();
row.update();
}
}
}
}
})(source, map, log, target);

Putting It All Together

The Import Wizard gave the client a simple way to initiate data imports from any number of vendors at a convenient time, after they verified the quality of the data. The Wizard Configuration table provided a means to extend the wizard’s functionality for multiple vendors and data imports. And the Vendor Model Translation table allowed the ITAM team to associate a vendor’s model information with Model records in the client’s ServiceNow instance.

The client’s ITAM team reports that the wizard has already paid off.  In a few short days it has simplified their work, reduced errors, and made processing asset data much more efficient. And project managers are making plans to bring more vendors on board as they work toward enforcing common data formats across all of their vendors.

Standing on the Shoulders of Giants

This article wouldn’t be complete without acknowledging the assistance of others. We based our design for the Import Wizard record producer on this ServiceNow Community post by Michael Ritchie and endorsed by everyone’s favorite bow-tie wearing technical genius, Chuck Tomasi. The post is well worth reading, since Michael leads you through the steps you’ll need to follow before using the record producer to perform a data import.

The post Simplifying Data Imports from Third Parties appeared first on ServiceNow Guru.

Creating or Editing a Property

Most of the system properties can be found in your left navigation under the ‘System Properties’ application as shown above. ALL of the properties in the system are stored in the ‘sys_properties’ table and can be viewed and edited there by typing ‘sys_properties.list’ into the left navigation filter.
You can add a property to your system simply by creating a new record in the ‘sys_properties’ table as shown in the wiki.

Adding your property to a properties page

It’s great to be able to modify properties from the properties table, but it’s a lot more convenient to make the edit through a properties page. Properties pages are what you get when you click on a module under the ‘System Properties’ application in the left navigation of your ServiceNow instance.
Properties pages are really just a URL that pulls together one or more System Property Categories into a single UI page to be viewed. You can create or modify these categories by navigating to ‘System Properties->Categories’. Here’s what one of the properties categories looks like for the Email settings out-of-box…

As you can see, there’s really not a whole lot to it. Simply give the category a name (which will be referenced in your page URL), a title (which will be displayed as a section header), and add your properties to the related list at the bottom of the page.

Accessing a properties page

A properties category in and of itself doesn’t do anything for you though. In order for these to be displayed as part of a page you need to create a module that points to the correct URL to include the categories and present them all on one page. This is exactly how the email properties page works. Here’s the module URL…

https://demo.service-now.com/system_properties_ui.do?sysparm_title=Email%20Properties&sysparm_category=Email,POP3,Email%20Security,Email%20Auto%20User,Email%20Advanced

And here’s what that page looks like…

A system properties page URL needs to have 3 different parts…

1. A pointer to the properties UI page (https://demo.service-now.com/system_properties_ui.do)
2. A ‘sysparm_title’ parameter to add a title to your page (sysparm_title=Email%20Properties)
3. A ‘sysparm_category’ parameter to indicate which properties categories should be included in the page (sysparm_category=Email,POP3,Email%20Security,Email%20Auto%20User,Email%20Advanced)

So this example points you to a properties page with a title of ‘Email Properties’ that includes 5 properties categories – ‘Email’, ‘POP3’, ‘Email Security’, ‘Email Auto User’, and ‘Email Advanced’. One thing that I noticed as I tested this is that the ‘%20’ escaping for the spaces in the URL is necessary for this to work properly.

Either way you choose to display your properties, you’ll need to access them in script for them to be of any real use. Here’s how you can do that…

Accessing a property in server-side script (Business Rules, UI Actions, UI Pages, etc.)

//Return the value of the ‘your.property.name.here’ system property

var propertyValue = gs.getProperty('your.property.name.here');

Accessing a property in client-side script (Client Scripts, UI Policies, etc.)

There isn’t a way to directly access a system property in client script and the need to do this is probably extremely rare. If you do have a situation where you need to access a system property in client script you can do so in a few ways.

1. 1. Add the property to your session client data as shown here.

The nice thing about this method is that you don’t have to do an expensive GlideRecord query to get your property information. It gets added to the client session data when the session is established for a particular user.

1. 1. Add the property to the ‘g_scratchpad’ object using a ‘Display’ business rule as shown here.

This option is similar to the previous one, but doesn’t set the property for the entire session…just for the table you’re working on.

1. 1. Create a GlideRecord Query and pull from the ‘sys_properties’ table.

While this method is probably the most familiar to you and seems the simplest, it can also be a huge performance killer. Because of this, this method should only be used if no other method is readily available. Any time you’re going to perform a client-side GlideRecord query, I highly recommend setting your script up to run that query asynchronously as described here.
You can query for a system property like this…

var rec = new GlideRecord('sys_properties');
rec.addQuery('name', 'your.property.name.here');
rec.query();
if(rec.next()){
var yourPropertyValue = rec.value;
}

The post Working with System Properties appeared first on ServiceNow Guru.

The scripts below can be pasted into the ‘Wait For condition’ activity script field as shown here…

//Query for all tasks to see if they are inactive
var rec = new GlideRecord('sc_task');
rec.addQuery('request_item', current.sys_id);
rec.addQuery('active', true);
rec.query();
if(rec.hasNext()){
answer = false;
}
else{
//Continue
answer = true;
}

//Query for all tasks to see if they are inactive
var rec = new GlideRecord('sc_task');
rec.addQuery('request_item', current.sys_id);
rec.addNotNullQuery('wf_activity');
rec.addQuery('active', true);
rec.query();
if(rec.hasNext()){
answer = false;
}
else{
//Continue
answer = true;
}

//Query for all tasks to see if they are inactive
var rec = new GlideRecord('change_task');
rec.addQuery('change_request', current.sys_id);
rec.addQuery('active', true);
rec.query();
if(rec.hasNext()){
answer = false;
}
else{
//Continue
answer = true;
}

//Query for all tasks to see if they are inactive
var rec = new GlideRecord('change_task');
rec.addQuery('change_request', current.sys_id);
rec.addNotNullQuery('wf_activity');
rec.addQuery('active', true);
rec.query();
if(rec.hasNext()){
answer = false;
}
else{
//Continue
answer = true;
}

The post Wait for Closure of all Tasks in Graphical Workflow appeared first on ServiceNow Guru.

The problem in this example was that there were emails being sent from Jim’s Service-now system that contained sensitive information. It was necessary to send this information as part of an integration with a 3rd party system but they didn’t want to retain that information in Service-now to be viewed in logs and in the activity history of task records.

The solution that we talked about was to set up a business rule on the ‘sys_email’ table to intercept these emails immediately after they were sent and parse out the sensitive information. This way, the email could be sent to the 3rd party system, but there would be no record of that sensitive information retained in Service-now. Jim then went and did the hard work and here is the business rule he ended up with…

var index = current.body.indexOf('Service Request Form');
if (current.recipients == 'abc@xyz.com' && index != -1 ){
current.body = 'Log information sent to 3rd party system';
current.body_text = 'Log information sent to 3rd party system';
}

The post Manipulating Outbound Email in the ‘sys_email’ Table appeared first on ServiceNow Guru.

The following method works in much the same way, but it copies by performing an insert against the current record (rather than starting from a brand new change record and supplying each value). Because of this, you’re concerned about overriding any of the values (such as start and end dates) that you DON’T want to be copied over from the record you are copying. This method works better if you know you want to copy over all (or the majority) of the field values from a given change.

copyChange();
function copyChange() {
	//Get the current sys_id value for querying
	var chgID = current.sys_id.toString();
	//Initialize new change for insertion
	var newChange = current;
	newChange.number = getNextObjNumberPadded(); //Get next change number
	newChange.requested_by_date = 'NULL';
	newChange.start_date = 'NULL';
	newChange.end_date = 'NULL';
	newChange.calendar_duration = 'NULL';
	newChange.opened_at = current.opened_at;
	newChange.opened_by = current.opened_by;
	newChange.sys_created_on = current.sys_created_on;
	newChange.sys_created_by = current.sys_created_by;
	current.insert();
	
	//Copy attachments for this change
	if (typeof GlideSysAttachment != 'undefined')
		GlideSysAttachment.copy('change_request', chgID, 'change_request', newChange.sys_id);
	else
		Packages.com.glide.ui.SysAttachment.copy('change_request', chgID, 'change_request', newChange.sys_id);
	
	//Copy associated tasks and CIs
	copyTask(chgID);
	copyCI(chgID);
	gs.addInfoMessage('Change ticket ' + newChange.number + ' created.')
	action.setRedirectURL(newChange);
}

function copyTask(chgID) {
	//Find the current change tasks and copy them
	var tasks = new GlideRecord('change_task');
	tasks.addQuery('change_request', chgID);
	tasks.query();
	while(tasks.next()){
		var taskID = tasks.sys_id.toString();
		var newTask = tasks;
		if (typeof GlideNumberManager != 'undefined')
			newTask.number = GlideNumberManager.getNumber('change_task');
		else
			newTask.number = Packages.com.glide.db.NumberManager.getNumber('change_task'); //Get next change task number
		newTask.change_request = current.sys_id;
		tasks.insert();
		
		//Copy attachments for this task
		if (typeof GlideSysAttachment != 'undefined')
			GlideSysAttachment.copy('change_task', taskID, 'change_task', tasks.sys_id);
		else
			Packages.com.glide.ui.SysAttachment.copy('change_task', taskID, 'change_task', tasks.sys_id);
	}
}

function copyCI(chgID) {
	//Get the task record being copied
	var tskRec = new GlideRecord('task');
	if(tskRec.get(chgID)){
		//Copy over the affected CI list
		var cis = new GlideRecord('task_ci');
		cis.addQuery('task', chgID);
                cis.addNullQuery('u_ci_group'); //Added to ensure that copying does not duplicate Group CIs
		if(gs.getProperty('change.conflict.mode')=='advanced' && tskRec.cmdb_ci!=''){
			//Prevent duplicate CI from being added
			cis.addQuery('ci_item', '!=', tskRec.cmdb_ci.toString());
		}
		cis.query();
		while(cis.next()){
			var newCI = cis;
			newCI.task = current.sys_id;
			cis.insert();
		}
	}
}

One other method for copying a change (or any other ticket) is to reference the record to copy and copy from the referenced record. You could use the script below to copy a change that is pulled from a reference field on any form. Just provide the name of the field to grab the change from in line 3.

//Get the current sys_id value for querying
//Provide the name of the reference field to copy change from
var chgID = current.your_change_reference_field.toString();
var rec = new GlideRecord('change_request');
rec.get(chgID);
copyChange();

function copyChange() {
	//Initialize new change for insertion
	var newChange = rec;
	newChange.number = getNextObjNumberPadded(); //Get next change number
	newChange.requested_by_date = 'NULL';
	newChange.start_date = 'NULL';
	newChange.end_date = 'NULL';
	newChange.calendar_duration = 'NULL';
	newChange.opened_at = current.opened_at;
	newChange.opened_by = current.opened_by;
	newChange.sys_created_on = current.sys_created_on;
	newChange.sys_created_by = current.sys_created_by;
	rec.insert();
	
	//Copy attachments for this change
	if (typeof GlideSysAttachment != 'undefined')
		GlideSysAttachment.copy('change_request', chgID, 'change_request', newChange.sys_id);
	else
		Packages.com.glide.ui.SysAttachment.copy('change_request', chgID, 'change_request', newChange.sys_id);
	
	//Copy associated tasks and CIs
	copyTask(chgID);
	copyCI(chgID);
	gs.addInfoMessage('Change ticket ' + newChange.number + ' created.');
	action.setRedirectURL(rec);
}

function copyTask(chgID) {
	//Find the current change tasks and copy them
	var tasks = new GlideRecord('change_task');
	tasks.addQuery('change_request', chgID);
	tasks.query();
	while(tasks.next()){
		var taskID = tasks.sys_id.toString();
		var newTask = tasks;
		if (typeof GlideNumberManager != 'undefined')
			newTask.number = GlideNumberManager.getNumber('change_task');
		else
			newTask.number = Packages.com.glide.db.NumberManager.getNumber('change_task'); //Get next change task number
		newTask.change_request = rec.sys_id;
		tasks.insert();
		
		//Copy attachments for this task
		if (typeof GlideSysAttachment != 'undefined')
			GlideSysAttachment.copy('change_task', taskID, 'change_task', tasks.sys_id);
		else
			Packages.com.glide.ui.SysAttachment.copy('change_task', taskID, 'change_task', tasks.sys_id);
	}
}

function copyCI(chgID) {
	//Get the task record being copied
	var tskRec = new GlideRecord('task');
	if(tskRec.get(chgID)){
		//Copy over the affected CI list
		var cis = new GlideRecord('task_ci');
		cis.addQuery('task', chgID);
                cis.addNullQuery('u_ci_group'); //Added to ensure that copying does not duplicate Group CIs
		if(gs.getProperty('change.conflict.mode')=='advanced' && tskRec.cmdb_ci!=''){
			//Prevent duplicate CI from being added
			cis.addQuery('ci_item', '!=', tskRec.cmdb_ci.toString());
		}
		cis.query();
		
		while(cis.next()){
			var newCI = cis;
			newCI.task =
			rec.sys_id;
			cis.insert();
		}
	}
}

The post ‘Copy’ UI action for Change requests (Part 2!) appeared first on ServiceNow Guru.

current.hasAttachments();

action.setRedirectURL('exportAttachmentsToZip.do?sysparm_sys_id=' + current.sys_id + '&sysparm_table=' + current.getTableName());

var sysid = g_request.getParameter('sysparm_sys_id');
var table = g_request.getParameter('sysparm_table');var theRecord = new GlideRecord(table);
theRecord.addQuery('sys_id', sysid);
theRecord.query();
theRecord.next();

var zipName = 'attachments.zip';

var StringUtil = GlideStringUtil;

var gr = new GlideRecord('sys_attachment');
gr.addQuery('table_sys_id', theRecord.sys_id);
gr.addQuery('table_name', theRecord.getTableName());
gr.query();

if (gr.hasNext()){
g_response.setHeader('Pragma', 'public');
g_response.addHeader('Cache-Control', 'max-age=0');
g_response.setContentType('application/octet-stream');
g_response.addHeader('Content-Disposition', 'attachment;filename=' + zipName);
var out = new Packages.java.util.zip.ZipOutputStream(g_response.getOutputStream());
var count=0;
while (gr.next()){
var sa = new GlideSysAttachment();
var binData = sa.getBytes(gr);

var file = gr.file_name;
addBytesToZip(out, zipName, file, binData);
count ++;
}
// Complete the ZIP file
out.close();
}

function addBytesToZip (out, dir, file, stream){
// Add ZIP entry to output stream.
out.putNextEntry(new Packages.java.util.zip.ZipEntry(file));
out.write(stream, 0, stream.length);
out.closeEntry();
}

That’s all there is to it. The result will appear like the following screen shot.

The post Download Attachments as a ZIP File appeared first on ServiceNow Guru.

“The requirement is to add an extra task to a set of tasks defined in a graphical workflow once it is running AND have a way of specifying the order AND have the new tasks start automatically when its predecessor completes and start the next one when it completes. In the days of execution/delivery plans I had done this using a rule which set the new predecessor/successor entries but can anyone advise how to approach it with workflow?”

This isn’t an easy problem to solve. I still don’t know if I’ve found a really good way to approach it yet, but here are a couple of things I’ve done in the past (that are currently being used in production systems) that may give you some ideas. Take it for what it’s worth. These probably aren’t perfect solutions, but it’s better than nothing I suppose! I’d love to hear of any feedback you have as you try these out (if you do) because this is an issue that I’m probably going to have to tackle in another month or so again.

Option #1:

Using execution plans and a custom business rule to allow insertion and re-ordering of tasks after the execution plan has initially been attached.–
What I’ve found is that execution plans are still more forgiving when it comes to ad-hoc tasks than workflow is. Because of this, if you have a need for ad-hoc tasks in Change requests, I think it makes a lot of sense to split the workflow processing and use Graphical workflow for approvals, and use Execution plans for task generation. If there’s no need for ad-hoc tasks, then I think Graphical workflow works best.

The challenge with ad-hoc tasks is the sequencing of those tasks. This business rule basically re-orders the entire list of associated change tasks in the execution plan each time the order on a change task changes. This includes when a new task is inserted.

-Table: Change task
-Order: 100
-Runs after insert/update
-Condition: current.order.changes()

syncTaskOrder();

function syncTaskOrder(){
//Query for all tasks that are associated to the same parent
var rec = new GlideRecord('change_task');
rec.addQuery('parent', current.parent);
rec.orderBy('order');
rec.query();
while(rec.next()){
//Delete all of the existing successor/predecessor records for the task
var rec1 = new GlideRecord('execution_plan_local');
var qc = rec1.addQuery('predecessor', rec.sys_id);
qc.addOrCondition('successor', rec.sys_id);
rec1.query();
while(rec1.next()){
rec1.deleteRecord();
}
}

//Query for all tasks that are associated to the same parent
var tsk = new GlideRecord(current.sys_class_name);
tsk.addQuery('parent', current.parent);
tsk.orderBy('order');
tsk.query();
var lastID = '';
var lastOrder;
var myIDArray=new Array();
while(tsk.next()){
if(tsk.order > lastOrder){
//Iterate through the sys_id array and create a new successor/predecessor record for each item
for(x in myIDArray){
//Initialize the creation of a new Task Sequencing record
var tsk1 = new GlideRecord('execution_plan_local');
tsk1.initialize();
tsk1.predecessor = myIDArray[x];
tsk1.successor = tsk.sys_id;
tsk1.insert();
}
//Empty the existing array
myIDArray.length = 0;
//Populate the current task sys_id into the array
myIDArray[0] = tsk.sys_id.toString();
}
else if((tsk.order == lastOrder) || !lastOrder){
var myIDArrayLength = myIDArray.length;
if(myIDArrayLength > 0){
//Get the last item in the array
var arrayIDVal = myIDArray[myIDArrayLength - 1];
//Query the Task Sequencing table for that item
var ps = new GlideRecord('execution_plan_local');
ps.addQuery('successor', arrayIDVal);
ps.query();
while(ps.next()){
//Create a new successor/predecessor record for the current task
var ps1 = new GlideRecord('execution_plan_local');
ps1.initialize();
ps1.predecessor = ps.predecessor;
ps1.successor = tsk.sys_id;
ps1.insert();
}
}
//Populate the current task sys_id into the array
myIDArray[myIDArrayLength] = tsk.sys_id.toString();
}
else{
}
lastOrder = Math.round(tsk.order);
lastID = tsk.sys_id.toString();
}
}

I also had to include this business rule to get the orders on the tasks to sync with execution plan task orders initially (although this may have been a bug that has since been fixed so this one may be optional).

-Table: Change task
-Order: 1,100 – This is important!
-Runs before insert
-Condition: current.delivery_task != ”

current.order = current.delivery_task.order;

I suppose the above method could also be used with Service requests, but I haven’t tried it.

Option #2:

I’ve seen this option used for service requests in ServiceNow. You can set up ‘dummy’ service request items that can be added to a service request with a defined number and ordering of tasks. This method could be used with either Graphical workflow or Execution plans.

The client that used this method (like most other customers) didn’t have a defined Service catalog, but wanted to use our Service catalog anyway. Basically what they ended up doing was having a single generic Service catalog item with a couple of steps that got added to any service request. Then they allowed their technicians to add other pre-defined request items to the request on the back end using the ‘Add new item’ button on the Request form. They defined as much as they could, but they also had pre-defined items that had tasks associated with them like ‘One task item’, ‘Three task item’, etc.
The nice thing about this method is that you can still attach your workflow or execution plan directly to those items. The person who needs to add more tasks can simply add another item to the request and it already has its workflow associated with it.

Option #3:

Create sub-tasks that can be manually added to the generated change or catalog tasks. Here is an example of how you could handle this for change requests. Let’s say that all of your change requests have to go through the same 3 steps. However, in certain cases, one or more ad-hoc tasks are needed. You could set up an execution plan (or workflow) to create 3 change tasks, and then create another table called ‘subtask’ that could be manually added to each auto-generated change task as necessary like this…

*Change Request
*Change task 1 –> FROM EXECUTION PLAN
*Subtask 1 –> MANUALLY ADDED
*Subtask 2 –> MANUALLY ADDED
*Change task 2 –> FROM EXECUTION PLAN
*Change task 3 –> FROM EXECUTION PLAN

So Subtasks 1 and 2 are children of Change task 1, and change task 1 does not get closed until subtasks 1 and 2 are closed.

Option #4:

Use a script in a ‘Wait for’ activity in your workflow to query for associated tasks and wait for their completion before moving on. This approach doesn’t deal with the ordering directly, but it does allow you to control tasks that are added outside of the workflow scope. The full solution is documented here in the Service-now wiki.

The post Managing Ad-hoc Tasks in ServiceNow Workflow appeared first on ServiceNow Guru.

Since both of these technologies are used in ServiceNow though, this confusion often presents itself so it is good to be informed. First of all, you don’t need to know a thing about Java (or even care about it) to be successful in the administration, implementation, or configuration of ServiceNow.  No Java NecessaryYou don’t need to know a thing about Java to be successful in the administration, implementation, or configuration of ServiceNow.You heard correctly.  YOU DO NOT NEED TO KNOW JAVA!  This statement runs contrary to what you might hear from your boss, project manager, or even a ServiceNow developer or consultant job posting.  The underlying code that the ServiceNow platform is built on is built using the Java programming language.  Even the title ‘ServiceNow Developer’ is probably a little bit excessive unless you are one of the people working in the Development group in Solana Beach.  Those people know (and are required to know) Java extremely well and they use it (along with JavaScript) to bring us the awesome features that are part of the ServiceNow software platform.  Having said all of that, it certainly doesn’t hurt to have some knowledge of Java simply because that gives you an understanding of programming and scripting in general.

JavaScript, on the other hand, is the world that ServiceNow administrators and consultants live in.  Business Rules, Client Scripts, and UI Actions are all written in JavaScript.  JavaScript is the scripting language used to perform HTML manipulations in the browser (client-side g_form, etc.) and interact with the actual Java code on the back-end (server-side gs., current., etc.).  I don’t think it’s too much of a stretch to say that to configure or enhance a mid to large sized ServiceNow deployment it is essential that you have someone who has a decent amount of knowledge about JavaScript or some other scripting language.  At the very minimum, they need to be someone who can pick up on it quickly.

Lots of very smart people have already written about the differences between Java and JavaScript.  If you’d like to learn more I’ll refer you to your preferred search engine.  Here are a couple of results I found today on Google.

http://kb.mozillazine.org/JavaScript_is_not_Java

http://www.ericgiguere.com/articles/javascript-is-not-java.html

The post JavaScript is not Java appeared first on ServiceNow Guru.