Add a table to the database view

Specify the table to join to the database view.

About this task

The Table field in the View Table form names the table to join to the database view. A Variable prefix can be assigned and used later when specifying a Where clause to define the conditions for the join. These conditions can refer to any field, but typically define the join by matching a field in the table to a field in another table that is part of the database view. When writing the Where clause, add the field name to the Variable prefix of its table with an underscore. For example, in the following screenshot, in the Where clause field, mi_id refers to the id field in the Metric Instance [metric_instance] table (mi) and the inc_sys_id refers to the sys_id field in the Incident [incident] table (inc). Database views can not be created on tables that participate in Table Rotation.

Figure 1. Database View Table form
Database View Table form

The Where clause supports these JavaScript conditional operators:

=, !=, <, <=, >, >=, &&, ||

Procedure

  1. From the Database View form, click New on the View Tables related list.
  2. Configure the form and add the Left join field (a check box) to the form.
  3. Click Save.
  4. Complete the form and select the Left join check box.
    • Selecting Left join causes the left-hand table in the database view to display all records, even if the join condition does not find a matching record on the right-hand table. Select this check box for view tables that specify a Where clause. Selecting Left join for view tables without a Where clause does not affect the query.
    • Joined tables are ordered left to right from lowest to highest Order values.
  5. Click Submit.
  6. Personalize the View Tables related list to show the Left join column.

    The Left join field shows a value of true.

  7. Click a record to view a table.

    The View Table form appears.

  8. To add an OR to your where clause use ||.

    For example, to query all incidents related to RFCs OR all incidents that are the parent of a change request, use the following syntax:

    inc_rfc = chg_sys_id || chg_parent = inc_sys_id

    Figure 2. Database view where clause
    Database view where clause