Database View to query Assessment Tables in ServiceNow

debalina1
Kilo Contributor

‎05-30-2018 09:22 AM

Hi, 

I'm trying to create a database view on Assessment Tables to get a complete report. Here is the view snapshot that I'm using. When I run the report, I'm able to view records from the highlighted tables. But unable to fetch data from asmt_metric_category and asmt_category_result table. Any help would be appreciated.


x_usaa3_sec_saas_security is a custom table. 
find_real_file.png

Labels:
Preview file
23 KB
0 Helpfuls
  • 1,807 Views
2 REPLIES 2

BobbyShaikh
Giga Contributor

‎05-30-2018 09:48 AM

  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

     

    -------

    A property called glide.db.max_view_records controls the maximum number of rows returned when running a GlideRecord query in a script. The default value for this property is 10,000. To change this value, add the property to the System Property [sys_properties] table and edit the number of rows to return.

0 Helpfuls

debalina1
Kilo Contributor

‎05-30-2018 10:27 AM

Thank you for your response. I have done the left join too. But unable to derive the exact result that I'm looking for. Data is not being fetched for these two table. asmt_metric_category and asmt_category_result.

Anyone who has created a DB view on assessment tables?

0 Helpfuls