Skip navigation

While I was working at a pharmaceutical company, I had to generate lots of documents for compliance reasons. In some cases, I had to pull data from ServiceNow to embed them in Word documents, such as requirements, test scripts, traceability matrix, etc. Soon it became very labor intensive so I decided to automate the process using the ServiceNow ODBC driver and Microsoft Office VBA (VisualBasic for Applications, a macro language). This allowed me to extract data from ServiceNow and use them in documents; the time savings was huge.

 

I originally published this on Share shortly after presenting it at Knowledge 14 in San Francisco. Since then, I lost access to Share and, unfortunately, wasn't able to restore my access. So I'm republishing it here after making some revisions.

 

Attached are

 

  1. Slide deck that was presented at K14 with instructions on how to run the samples in the Word file.
  2. Word macro file containing VBA samples for Word, Excel, PowerPoint, Outlook; pdf files are created by exporting Office files as pdf.

 

The Word demo file was created using Word 2010 but should work with later versions. Below is the abstract from the deck:

Beyond ITSM, ServiceNow is a powerful platform for any business service request management. Enhance the power by fully integrating with Microsoft Word, Excel, Outlook and PDF, from simple form-based report generation to live, interactive documents. Automatically pull contents from ServiceNow and apply formats to meet business needs. Add password protection or digital signature for enhanced security. Simple, practical do-it-yourself solutions will be demonstrated and best practices discussed. If you ever wanted more than the standard Excel and PDF export from ServiceNow, this is a must-attend session!

 

The slide deck includes:

 

  1. Solution – Requirements Tracker
  2. Solution – Overview of exporting to Word templates
  3. Solution – Prepare Word VBA
  4. Solution – Open ServiceNow Database
  5. Solution – Query ServiceNow Database
  6. Solution – Create Word Documents
  7. Solution – Export to PDF
  8. Solution – Protect Document (using Digital Signature)
  9. Solution – Create Excel Spreadsheets
  10. Solution – Create PowerPoint from Excel
  11. Solution – Create Outlook Email
  12. Alternate Solutions
  13. Tips – ODBC
  14. Tips – VBA

 

Hope you find it useful and please post questions if you run into anything.

 

Please feel free to connect, follow, post feedback / questions / comments, share, like, bookmark, endorse.

John Chun, PhD PMP see John's LinkedIn profile

visit snowaid

Apart from the basic concepts of HTML, CSS, and JavaScript, one of the most powerful things you can learn to really feel comfortable in Service Portal is AngularJS scopes.

 

There are many resources online which do a great job of explaining AngularJS scopes; there's really no better than what can be found in the official AngularJS documentation. However, there's nothing I'm aware of which is targeted at someone who's getting started in Service Portal, has the basic knowledge of creating a widget, but still feels confused about what's actually going on behind the scenes. Hopefully after reading this article some of that fog of confusion will have lifted.

 

Once you understand scopes, the amount of road blocks you'll hit when developing widgets will dramatically lower, and you'll be well on your way to becoming a Service Portal master!

 

This is my first post in what will hopefully become a series of articles helping to explain the basic concepts of Service Portal. If you have any suggestions for topics, feel free to leave a comment below, or reach out to me on twitter with my handle @dylanlindgren.

 

The DOM

 

Given that you're reading this article, it's likely that you've ventured into the developer tools of your web browser on at least a few occasions. Perhaps after a sudden fit of anxiety at the overwhelming amount of information in there, the first thing you'll have noticed is a tree-like structure of elements, within elements, within elements, and so on. This structure is a live, visual representation of the current state of something called the Document Object Model of the page you are on; for short it's called the DOM.

 

1.png

 

The description of the DOM from the Mozilla Developer Network is as follows:

The Document Object Model (DOM) [...] represents the page so that programs can change the document structure, style and content. The DOM represents the document as nodes and objects.

Shown in another way, you can really see how this parent & child relationship that each DOM element has goes in to building what you see in your browser window. Note that the "stacking" of elements shows the parent & child relationships of the elements on the page.

 

2.png

 

The key point to understand about the DOM is that it is a "tree" of elements, like in this hypothetical DOM tree:

3.png

 

Directives

 

A comprehensive definition of an directive can be found on the AngularJS documentation:

At a high level, directives are markers on a DOM element [...] that tell AngularJS [...] to attach a specified behavior to that DOM element [...], or even to transform the DOM element and its children.

To rephrase that, any element within the DOM can have a directive attached to it, and you can use the directive to give the element custom behaviour.

This element you're targeting could be anything, such as an image, a header, a list, or even a container div element (which may have many elements within it). And the custom behaviour you give to it could be an animation, to load some text into it from an external website, or even hide it from the page completely; it's up to the creator of the directive. This allows developers to build their own reusable components which do not exist natively in HTML, such as a login button that has a particular style/functionality, or a list of records.

 

So in the case of the hypothetical DOM tree I showed earlier, perhaps the elements with a green border below might have directives associated with them.

 

4.png

 

Note that the element with the blue border is the element on the page with the attribute ng-app. In AngularJS the element with this attribute is considered the root element of the application. It's only the elements below this which Angular treats as part of the application.

 

Let's just show the AngularJS-related elements of the DOM, and rotate it so it looks more like a "tree" (albeit, one that's upside down!)

 

5.png

 

Directives, a.k.a Widgets

 

You might now be asking yourself...

Why am I reading about directives? How does this relate to Service Portal?

 

Well, Service Portal widgets are directives. When you load a Service Portal page, a new AngularJS directive is created for each different widget on the page. This directive is then placed in the DOM wherever the widget was put on the page via Service Portal Designer or Page Editor. As you know from the previous section, a directive adds custom behaviour, and in the case of a widget this behaviour is to insert the HTML from the widget, and perform the actions you define within the client script and/or link function of the widget.

 

So why are there two names for the same thing? Well, there's lots of functionality that's been added to widgets to make them easier to work with, such as options, the addition of the server-side behaviour that happens in it's server script, and the passing of the data variable generated in it to the client script.

 

So in the case of widgets, the hypothetical structure of the directives from the previous section could be like so:

 

6.png

 

You'll notice above there's an "embedded" Widget B, and is not added via Page Designer. This widget is embedded in Widget A as described on the official ServiceNow Documentation. This is actually the only way that you'd end up having a widget sitting below another widget in the DOM.

 

Scopes

 

One characteristic that's just as important to a widget as it is to a directive is scopes. A scope can be thought of as a space where application functionality is contained to. By default a directive will share the scope of it's parent directive, however they can also be set to have a new, isolated scope.

In Service Portal a widget always has an isolated scope. This is something that has been set by the developers of Service Portal to ensure widgets play nicely together, and that you can have multiple instances of the same widget on a single page as they all have their own isolated space to play.

 

The scope tree

 

Not just is the DOM a tree, but scopes sit in a tree as well. You can think of the scope tree as a cut-down version of the DOM tree, with all the things that didn't create a new scope removed. As all widgets create a new scope then the scope tree in the example above would look like so:

 

7.png

 

Local scope vs Root scope

 

You'll have noticed in the above diagram that at the top of the tree is a thing called $rootScope. You can think of this as an overarching scope that is available to your whole application, from any widget/directive. It also allows you to listen for/broadcast events, but more on that in the next section.

 

Cross-scope communication

 

All widgets have isolated scopes, so how can we access data from one widget in another widget? There's two ways:

 

Events

 

Similar to the concept of events in ServiceNow, AngularJS events can be announced from any scope in your application. These events can be listened from any scope as well, however which method you use to announce the event will have an impact on which scopes will hear it.

 

There are two methods you can use to announce events:

 

  • With $broadcast(), the event will travel down in the scope tree from wherever it was announced, until it hits the bottom. The event will not be heard by sibling scopes. See more on the official AngularJS documentation.
  • Using $emit(), the event will travel up in the scope tree from wherever it was announced, until it hits the $rootScope. This event will not be heard by sibling scopes. See more on the official AngularJS documentation.

 

8.png

 

So now that we know how announce an event, how do we listen for it? For that, we can use the $on() method. When you use this method, you supply it with a function and whenever that event is heard by the scope the function will be called. See more about this on the official Angular JS documentation.

 

The $broadcast() and $on() functions are also available on $rootScope, which gives you another place to listen for/announce an event to ensure the right scopes will hear it.

 

Angular Providers

 

Another means of communication between widgets is a service or a factory, which in Service Portal are both types of Angular Providers. Services and factories in AngularJS basically offer a place where you can define functions and variables, and any scope which has access to it can use them.

 

The diagram below visualises where these sit in relation to the scopes, if for all 3 widgets in our example the Angular Provider was added to their related lists.Given the overwhelming amount of information in this article already, I'll cover in detail how one would use an Angular Provider in a future installment.

 

9.png

 

Conclusion

 

In the next installment in this series, we will look into how to put this theory into practice by making a series of widgets which communicate with each other. Specifically, how to create a widget showing a list, that reacts and updates with new information when you change a field on the out-of-box form widget.

 

If you'd like to read more about AngularJS scopes, I've put together some further reading links below:

 

Well, that's it for scopes! Feel free to leave feedback in the comments below, or reach out to me on twitter with my handle @dylanlindgren if you'd like to buy me a beer! 

user interface.jpgThe Knowledge Base is kept current with frequent edits and additions. Find out what is new and stay up-to-date on the latest ServiceNow Knowledge Base articles by reviewing the weekly KB digest.

 

 

The ServiceNow user interface is the main way to interact with the information and applications in your instance. Organizations can customize their user interface to incorporate their brand logos and colors. The ServiceNow UI extends to the mobile, tablet, and desktop.

 

Recently added and updated articles on UI:

 

User Interface


Customize, personalize and manage the way users view and interact with your organization or company’s interface using UI.

creatorcon challenge.PNG

 

It's on again. Yes, that's right - the CreatorCon Challenge is back for your app creation and entrepreneurial pleasure.

 

No amount of shouting about how great it all was and how "overwhelming" the response was last year is going to make any difference - the proof that last year's inaugural edition was well received by the ServiceNow developer and partner community, as well as the judges, the attendees at the finale at Knowledge17,  and most importantly the customers that have already purchased the winning apps, is that ServiceNow decided to fund it and invest in it again for the 2nd year in a row.  Proof's in the pudding as they say. Well, the pudding is here, and it's mighty tasty.

 

 

This year, we're delighted to up our challenge game in four ways (not necessarily a complete list):

 

1) ServiceNow founder Fred Luddy is a judge. Who better to pitch your company and app to on the big stage at Knowledge18 than Fred Luddy? Answer: nobody.

 

2) Your personal VC mentor - BJ Lackland, CEO of Lighter Capital , a leader in the non-dilutive VC space, is back (he was a judge last year), this time as a personal mentor to the three finalists. That is some pretty serious value for startups to get access to someone of BJ's expertise to coach and mentor you as you fine-tune your investor pitches and then to be on-stage with you at Knowledge18. Sort of like your own Angelo Dundee.

 

3) $1M in total prizes - $500K in cash investments from ServiceNow Ventures plus $500K worth of sales and marketing prizes. See the FAQ for a detailed breakdown of how the $500K in sales and marketing is calculated, if you have any doubts about what it's really worth. And that doesn't even include what it would cost you for access to a mentor such as BJ Lackland (because BJ agreed to be a mentor after the FAQ was published, natch).

 

4) Expanded distribution channels - reach new customers in new markets all by yourself with the OEM Program. The perfect complement to the ServiceNow Store. Winners can (and are required to, actually) distribute and monetize apps on the Store, OEM Program, or both!

 

We are once again psyched to see what our developer and partner community will create on the Now Platform with all of the new platform services  in Jakarta (such as MetricBase) and what's coming soon in Kingston.

 

Good luck -  create something amazing and pitch it to Fred. We look forward to reviewing your entry.

Martin Barclay
Director, Product Marketing
App Store and ISVs
ServiceNow
Santa Clara, CA

At a time when Twitter, Facebook, Google and Yahoo give you flexibility to select what you want and don't want to read, the email is still one of the places where "unsubscribe" is not always an option. As users go on holidays, move jobs (e.g. the account get disabled), mailboxes get full, emails are marked as spam, etc, some email relays would start bouncing back those emails, and those users would not be able to unsubscribe to those senders. On your instance, it is a good practice to review the emails bounced back and review the users for which no further notification should be sent. This requires an efficient and regular intervention, as bounced emails would slowly increase if no action is taken. From the administration point of view, if users are empower themselves to unsubscribe, you will have less work to do, and wanted emails would go out faster.

bounce-mail.-e1478601620490x.png

 

Following up my previous blog Speed up your email delivery by validating recipients, here is my review on the matter.

 

3 ways to minimize bounced emails

  1. Educate your users to unsubscribe from the unwanted notifications instead of just ignoring them
  2. Regularly execute a validation of the emails bounced back to the instance
  3. Archive junk emails to improve the email table performance

 

Educate your users to unsubscribe from unwanted notifications

To educate your users to unsubscribe, ensure to inform them this functionality is available on your instance. Users can create filters within their preference to only receive the notification if the condition set on their personalised filter are met. They could also choose to disable per notification, or disable notifications received by the user altogether. This does not affect the user active status - just the notifications targeted to them.

 

Here is one example from a notifications received by one user: angelo.ferentz.

2017-09-30_1045-email-recieved.png

 

For this, users can go to their notification preferences, then select the notification they have received and set the dial to "off." That would avoid further notifications from being sent to this user only.

2017-09-30_1045-setting-off.png

 

They can also decide to leave them ON, but set a personalised filter that avoids them under certain conditions.

2017-09-30_1050-advanced-filter.png

 

 

Finally, if users decide to stop any notification from being sent to their devices (e.g. before going on holiday), users can select to disable them altogether.

2017-09-30_1107-disable-all.png

 

From the performance point of view, if users only enable the notifications they require, there would be less emails to send. This is a much better option than generating tons of emails that go out and back again, when these settings are ignored.

If you are reading this, unsubscribe of all the unwanted notifications  to help improve performance on the services you receive and provide.

 

Regularly execute a validation of the emails bounced back to the instance

There is not standard practice to parse the emails bounced back. However, as a cloud, instance administrators are limited on the options available to avoid bounced emails.

 

Administrators can see the emails received and marked as Junk. If you recognise those are emails as bounced back, the safest option is to set the user 'notification' to Disabled. Then contact the user to inform them of the change. You can do this regularly as it improves your email reliability because it reduces the amount of emails bouncing back. If your instance is having a larger portions of emails bounced back, I have created some tools to help.

 

retrieveBouncedBackEmail is a script when executed  that would match a query and parse the emails on the body_text, which usually contains the bounced emails. It ignored the emails after the "Received:" text, as it usually contains the original senders.

 

setUserNotificationbyEmail is a script that would enable or disable the notification on the user that matches the email address.

 

Here is the script for retrieveBouncedBackEmail:

 

// Retrieve the list of emails on bounced emails 
// Note: The normal practice here isn't to attempt to parse the bounce message at all.
//
// Returns an array with the list of [0] messages [1] Array of emails bounced
function retrieveBouncedBackEmail(queryToMatch, maxToFindPerEmail, limitquery) {

    return _retrieveBouncedBackEmail(queryToMatch, maxToFindPerEmail, limitquery);

    function _retrieveBouncedBackEmail(queryToMatch, maxToFindPerEmail, limitquery) {
        // Set max emails retrieved from each bounced email parsed - default 10 
        maxToFindPerEmail = maxToFindPerEmail || 10;
        // Limit parsing more than limitquery emails - default 100
        limitquery = limitquery ? limitquery : 100;

        // Set the query that matches the bounced emails - default: "type=received-ignored^sys_created_onONThis week
        queryToMatch = queryToMatch || "type=received-ignored^sys_created_onONThis week@javascript:gs.beginningOfThisWeek()@javascript:gs.endOfThisWeek()";

        // Set the response message and the query to perform
        var vmessages = ["Query limit set " + limitquery, "Searching on sys_email", "Query: " + queryToMatch, "Threshold: " + maxToFindPerEmail],
            b = new GlideRecord("sys_email");
        b.addEncodedQuery(queryToMatch);
        b.setLimit(limitquery);
        b.query();
        vmessages.push(" sys_email records returned: " + b.getRowCount());
        // Loop on the matched emails and assume the body contains the list of emails bounced back
        for (var lemails = []; b.next();) {
            var a = b.body_text,
                // Ignores the text after "Received:" on the body_text as it contains the emails from the original message
                // that you do not want to parse. You want to retrieve only the ones related to the bounced email
                g = a.indexOf("Received:");
            0 < g && (a = a.substr(0, g));

            a = (a = extractEmails(a)) ? uniq(a) : [];

            // It would report on email bound back on which we could not extract an email 
            // so you need to inspected it, to validate why it did not retrieve an email
            0 == a.length && vmessages.push("Email we could not extract an email addresses: " +
                b.sys_id + " - count: " + a.length);

            // If the email contains too many emails, it will add a message
            // so you need to inspected it, to validate why there were so many emails
            a.length > maxToFindPerEmail && vmessages.push("Email ignored by threshold: " + b.sys_id + " - count: " + a.length);
            (0 < a.length && a.length) <= maxToFindPerEmail && (lemails = uniq(lemails.concat(a)))
        }

        // returns an array with the list of arrays: 
        // [0] array of message [1] array of emails
        return [vmessages, lemails]
    };

    // Generate an array of emails found on the text provided
    function extractEmails(text)
    {
        return text.match(/([a-zA-Z0-9._-]+@[a-zA-Z0-9._-]+\.[a-zA-Z0-9._-]+)/gi);
    }

    // Sort and remove non-unique strings on the array
    function uniq(a) {
        return a.sort().filter(function(item, pos, ary) {
            return !pos || item != ary[pos - 1];
        })
    }
}

 

Here is the script for setUserNotificationbyEmail:

 

// Set the User 'notification' to Enable/Disabled for the matching email addresses
// vemailarray is the array of emails to disable
// setEnable is either "enabled" or "disabled". Disabled is the defaul
// limitquery is the max number of users to set. It has a limitquery of 100
// 
function setUserNotificationbyEmail(vemailarray, setEnable, limitquery) {

    // It will only query the users that need to be enabled or disabled, and ignore the ones already set.
    var c = (setEnable = /^(enable|enabled|true|1|yes|on)$/i.test(setEnable)) ? "notification=1^emailIN" + vemailarray.join(",") : "notification=2^emailIN" + vemailarray.join(","),
        b = new GlideRecord("sys_user");
    limitquery = limitquery ? limitquery : 100;
    b.addEncodedQuery(c);
    b.setLimit(limitquery);
    b.query();
    // create the message to provide back
    c = ['Query Limit set ' + limitquery, 'Records found: ' + b.getRowCount(), 'Query executed: ' + c];
    // Here the users are being set
    for (; b.next();) c.push((setEnable ? "Enabled " : "Disabled ") + "notification: " + b.sys_id + " - user: " + b.user_name), b.notification = setEnable ? 2 : 1, b.update();
    return c
};

 

 

For an example, here is a bounced email found on an instance:

 

2017-09-30_1216-example-bounced-a.png

 

Executing:

// set the qualification to match your bounced emails.  
var result = retrieveBouncedBackEmail("sys_idSTARTSWITHc3a63a97dbc14bc0d975f1c41d9619b7", 45, 1000);
gs.print("\n Results:\n" + result[0].join("\n") + "\n\nFinal list of emails " + result[1].length + "\n\n\n" + result[1].join("\n"));

 

RESULT:

*** Script:
Results:
Query limit set 1000
Searching on sys_email
Query: sys_idSTARTSWITHc3a63a97dbc14bc0d975f1c41d9619b7
Threshold: 45
sys_email records returned: 1

Final list of emails 3


andrea.sisco@abc.com.net
test-bb.aa@abc.com.net
test.aaa@abc.com.net

 

To disable the users, you can execute the following:

 

// create an array of emails, for the users you want to disable.
var todisablelist = ["andrea.sisco@abc.com.net","test-bb.aa@abc.com.net","test.aaa@abc.com.net"]

// To disable the User Notification uncomment the next line
gs.print( '\n\nSetting user disabled:\n\n' + setUserNotificationbyEmail(todisablelist,'Disabled').join('\n'));

 

RESULT:

*** Script:

 

Setting user disabled:

 

Query Limit set 100
Records found: 1
Query executed: notification=2^emailINandrea.sisco@abc.com.net,test-bb.aa@abc.com.net,test.aaa@abc.com.net
Disabled notification: 8d256345dbe983002fd876231f96196e - user: andrea.sisco

 

Or you can use them together as follow:

 

// set the qualification to match your bounced emails.  
var result = retrieveBouncedBackEmail("sys_idSTARTSWITHc3a63a97dbc14bc0d975f1c41d9619b7", 45, 1000);
gs.print("\n Results:\n" + result[0].join("\n") + "\n\nFinal list of emails " + result[1].length + "\n\n\n" + result[1].join("\n"));

// result[1] hold the array of emails, for the users you want 
// To disable *after you validate* the User Notification result[1] contains an array of the emails
gs.print( '\n\nSetting user disabled:\n\n' + 
   setUserNotificationbyEmail(todisablelist,'Disabled').join('\n'));

 

RESULT:

*** Script:
Results:
Query limit set 1000
Searching on sys_email
Query: sys_idSTARTSWITHc3a63a97dbc14bc0d975f1c41d9619b7
Threshold: 45
sys_email records returned: 1

Final list of emails 3


andrea.sisco@abc.com.net
test-bb.aa@abc.com.net
test.aaa@abc.com.net

Setting user disabled:


Query Limit set 100
Records found: 1
Query executed: notification=2^emailINandrea.sisco@abc.com.net,test-bb.aa@abc.com.net,test.aaa@abc.com.net
Disabled notification: 8d256345dbe983002fd876231f96196e - user: andrea.sisco

Please note the user notification value does not affect the "active" or "lock" status on the account. It will only affect the notifications.

I hope these actions empower the administrator to set a user account's notifications to Disable. This would dramatically reduce the amount of emails being bounced back.

 

 

Archive junk emails

ServiceNow can archive and eventually destroy email messages that you no longer need, in the case Junk emails or if your Email table is excessively large. This is especially important if you depend on emails as very large tables tend to degrade over time and delay upgrades.

 

As per performance, we are looking for email retention rules: "Emails - Ignored and over 90 days old". This rule archives email message records that were created more than 90 days prior to the current date and are of type received-ignored or sent-ignored.

2017-09-30_1326-archiving.png

 

 

Once the emails are archived, then they stay on the archive for a year before being destroyed.

2017-09-30_1331-destroying.png

 

Now, it is time to put this all in place and allow your users to unsubscribe. For those which the notifications bounce back, then set the sys_user 'notification' to Disabled. Finally, for those emails ending up on your instance Junk, archive and destroy them. If you keep doing this, your email delivery will be reliable and delays will be a thing of the past.

 

Here are some useful resources for more information :

Filter Blog

By date: By tag: