Abstract

We've set up a module in ServiceNow for using authenticated QuickBase tables as a data import sources in ServiceNow. The standard HTTPS and REST modules in ServiceNow can't easily do this--they only seem to support basic authentication, and the QuickBase API implements its own authentication system using an ephemeral session tokens. That means that the valid endpoint URL is periodically changing; this modification automatically maintains it for you.

Usage

Overview

The update set modifies ServiceNow Data Sources by adding a new data source file retrieval method labeled "QuickBase" which references exactly one "QuickBase Resource."

A QuickBase Resource is a new custom ServiceNow object consisting of:

• a set of credentials
• a named QB table
• an app token (optional)
• a session token (generated)
• a constructed URL (generated).

Finally, there is a scheduled job that updates the session token and URL on an hourly basis. An update to a QuickBase Resource cascades to all data sources using it.

Downloads

[note terms of LICENSE]

Related Documentation

QuickBase API
ServiceNow Data Sources

Installation

There is an update set and a scheduled job.

1. Download and install the update set (see the relevant wiki article)
2. Download and install the scheduled job (see the relevant wiki article)

At this point you should do a cursory verification of the installation:

1. Reload the application navigator and look for "QuickBase Resources."
2. Look at the Data Source form and check that there is now a "QuickBase" file retrieval type in the dropdown.
3. Ensure there is a new Scheduled Job with "QuickBase" in the name.

...at this point, you are ready to create/modify a data source to pull data from the QB API.

Setting Up a QuickBase Data Source

1. Obtain your QuickBase credentials, app token, and target DBID for the table you want to import.
2. In the application navigator, go to "QuickBase Resources" and create and save a new record.
3. Click the "Refresh Authentication Ticket" UI Action to get a ticket and build a valid URL.
4. in your Data Source:
   • Select "QuickBase" as the file retrieval method.
   • Select "XML" as the data type.
   • Input an appropriate XPath for the row marker element in the target XML (e.g. "//row").
   • Select the appropriate QuickBase Data Source to use.
   • Save and Stay.

In a real example, the ticket would be present as well. If all goes well, the URL should contain the URL Template from the QuickBase Resource you pointed to, and you should be able to test load records from QuickBase. Then you proceed as you would with any data source: create a transform map, schedule the import, etc.

Internals

Database Modifications

• QuickBase Resource table, fields, field labels, views created
• Data Source.File retrieval method choice list modified to include "QuickBase" option

UI Modifications

• "QuickBase Resource" module added in the application navigator under the "System Import Sets" application
• Form, list view created for the new module
• The form for Data Source modified so that when QuickBase file retrieval is selected, the URL becomes read-only, superfluous fields disappear, and the reference to the QuickBase Resource becomes visible and mandatory

Business Rules

• Data Source
  • copy the connection URL from the QuickBase source if selected/as appropriate
• QuickBase Resource:
  • when resource is updated in any way, reconstruct the URL template based on the new ticket (recursiveness is caught)
  • when URL template is updated, cascade changes to all Data Sources which are related to this resource and actively using QuickBase file retrieval (should /not/ overwrite URLs for Data Sources which are vestigially related, but currently using some other retrieval method)

Script Include

• QuickBaseAuth object: implements a method for retrieving a new ticket from a set of credentials and a named target instance

  /**
   * returns an array containing the ticket (index 0) and any error messages (index 1)
   * prototype => getTicket: function(instance, username, password)
   */
  
  // example:
  var qa = new QuickBaseAuth();
  
  var result = qa.getTicket('mydomain.quickbase.com', 'myusername', 'mypassword');
  ticket = result[0];
  error = result[1];
  

UI Action, Scheduled Job

• both refresh tickets (one explicitly, one on a schedule)
• ticket generation and scheduled refresh hard-coded for one-hour tickets

Access Control

• QuickBase Resources only visible to admin, since there is hot data (passwords, app tokens)