A view on database views

General Add comments
by:

Database views in ServiceNow are somewhat limited, but with a bit of creativity you can still make good use of them. The two main uses of database views are: Reporting and (direct) Web Services.
This blog will try to list the options and limitations of ServiceNow database views by trying translate SQL statements to ServiceNow Database Views. Below you will find a number of Information Requirements that you could have and the technical way to deliver this information. Goal is to show a technical concept where Database Views together with Before Query Business Rules provide a much more powerful solution that Database Views alone.

1 – Show some information from incidents
SQL:
SELECT number, incident_state FROM incident;
This is an obvious one. See http://wiki.servicenow.com/index.php?title=Database_Views ;-).

2 – Show some information from incident and its related caller
SQL:
SELECT inc.number, inc.incident_state, clr.email FROM incident AS inc INNER JOIN sys_user AS clr ON inc.caller_id = clr.sys_id WHERE inc.active=true;
Fairly straight forward: Create a Database View with the two tables incident and sys_user and select the attributes “number”, “incident_state” and “caller_id” from incident and “sys_id” and “email” from sys_user (remember: attributes that you use in the where clause of the Database View need to be in the View Fields).

3 – Show incidents that are linked to an active problem
SQL:
SELECT inc.number, inc.incident_state FROM incident AS inc WHERE inc.sys_id IN (SELECT prb.parent FROM problem AS prb WHERE prb.active);
Now it gets interesting. A simple join between the incident and problem tables would create a lot of duplicates in the resulting view. For this we will introduce a “hidden” feature: Before Query Business Rule on Database Views. Through a right-mouse-click on the database view header you don’t have access to Personalize=>Business Rules, but fortunately you can still create them through the Business Rules module.

Database View:
Create a Database View on the incident (inc) table alone that shows only the attributes “sys_id” “number” and “incident_state”. You do not need to set any filter (where-clause) here.

Before Query Business Rule:
var prbQuery = current.addJoinQuery(‘problem’, ‘inc_sys_id’, ‘parent’);
prbQuery.addCondition(‘active’,’true’);

The result is a list of all Incidents that is limited via the addJoinQuery in the Query Business Rule to Incidents that are related to one or more active problems.

4 – Show Incidents that are not connected to a problem
SQL:
SELECT inc.number, inc.incident_state FROM incident AS inc WHERE inc.sys_id NOT IN (SELECT prb.parent FROM problem AS prb);
Even this is possible:

Database View:
Create a Database View with tables incident (inc) and problem (prb), left joining the problem table on inc_sys_id = prb_parent. The attributes “sys_id”, “number” and “incident_state” from incident and “parent” and “number” from problem should be available. You will see that you get a list with all incidents and their related (if any) problem information, resulting in duplicate incidents when an incident is parent for multiple problems.

Before Query Business Rule:
current.addNullQuery(‘prb_number’);
This Business Rule will filter out all the records that actually contain problem information. The result is a list with Incidents that do not have a problem connected to them.

Some notes on Access Controls versus Before Query Business Rules
When providing data for a report or a Web Service I try to avoid Access Controls, because they result in unwanted behavior:
A Web Service will return only the records that the interface user is allowed to see when you use Access Controls. This sounds OK, but when you say “I want records 100 to 200” (assuming the number of valid results > 200), you may receive only less than 100 records because the others are hidden by an ACL. If you use the Before Query Business Rule however, you will receive 100 records.

In Reports this difference also shows: When using ACLs, you will get a message that a certain amount of records is not shown due to security constraints and less than complete list of records may be shown (you get 100 pages of 20 records, and on the first page only 10 records are shown because the others are hidden by ACLs). Before Query Business Rules do not have this problem; You will simply not see the records that you are not supposed to see.
Be aware: Before Query Business rules that are set on one or more of the tables that are a source of the Database View do NOT impact the results of the Database View. The same goes for Access Controls. The other way around: Business Rules and ACLs that are set on Database Views do not impact the source tables. Look at this as an opportunity rather than an issue.

I hope this blog was useful for you. If you have questions you can mail me at .img[at].img

3 Responses to “A view on database views”

  1. Phonsie Says:

    This is great! Thanks for sharing, it saved me a heap of time 🙂

  2. Neha Says:

    I have a customised Intsk form displaying related story in the related list on the Intask form.
    The association between the Intask and the story is through a u_str_tsk field on the
    story form which stores the sys_id of the associated Intask.
    Now I wanted to have a view which displayes the list of all the active
    Intask along with the associated story number. If there is no story for the Intask then that column value should be blank.

    I have created a DB view with the tables as:
    intsk: whereclause-> intask_active=true(Left join true)
    story: wherclausee-> story_u_str_tsk=intsk_sys_id(left join false)

    With the above configurations, the DBView is only displaying the intask with the associated story. If the intask is not associated with the story then it is skipped, thus not all active intask are displayed.
    Please guide on how can I get correct data in DBView.

  3. Martijn Says:

    Response is probably a bit late:
    in this case the “left join” should be set on the Story and not on the intask

Leave a Reply