Procedure

Use the following information for creating and joining tables in the Q-SYS Reflect App for ServiceNow:

Overview

By default, the Q-SYS Reflect App for ServiceNow uses the standard Q-SYS Reflect API calls and stores the returned information in dedicated tables within ServiceNow. Some of this data includes IDs and other technical details that are not user-friendly and may need to reference additional tables and data within ServiceNow. The guide below explains how to map these tables together to create lists and reports that are easier for your users to work with.

Requirements

• ServiceNow Version: 
  • Yokohama Patch 7 Hot Fix 2 or later
• Required Roles:
  • ServiceNow Admin
• Setup:
  • Completed Installation and Configuration of the Q-SYS Reflect App for ServiceNow in your ServiceNow Tenant.   
    • For more details please see the How To | Install the Q-SYS Reflect App for ServiceNow and How To | Configure the Q-SYS Reflect App for ServiceNow articles.
  • At lease (1) Q-SYS core registered to your Q-SYS Reflect instance.

Setup

Example

In this example we will transform the data for the System Items section of the Q-SYS Reflect App for ServiceNow. Below you will see that Core ID and Design ID fields are just number but we want to be able to see the Core Name, Design Name, and System Name fields which do not exist in the x_qsc_q_sys_reflec_system_items table.

1. Navigate to All > System Definition > Database Views.
2. Select New.
3. Name the view. 
   1. Name: q_sys_reflect_system_peripherals (u_ is automatically placed at the beginning to notate its a custom table)
4. Give the view a label, plural, and description for reference later (These are optional fields but useful for know the purpose of this view.
   1. Label: System Peripheral
   2. Plural: System Peripherals
   3. Description: A Database view for all Q-SYS Reflect information by system items that links tables x_qsc_q_sys_reflec_systems and x_qsc_q_sys_reflec_system_items

5. Click the submit button to create the Database view.
6. You will now be returned to the All > System Definition > Database Views list. Find the Database view in the list and select its name to open the view and add new tables to the view.

7. Select New on the View Tables related list.
8. Choose the starting table you want to map together.  For our example we will choose the x_qsc_q_sys_reflec_system_items table.
9. Then choose a prefix name for the table which is required and used in the syntax for join tables together in the following steps.
   1. Variable prefix: sysitems
   2. All other items can remain at their default values

10. Select Submit.

11. Now repeat steps 7-9 to map a second table to this first one we have added.  For our example we will choose the x_qsc_q_sys_reflec_systems table in order for us to be able to get the System Name from System ID in our System Items table.
12. The only additional field that we need to fill out now is the Where clause.  This is how we map the data together and correlate matching fields.   We are going to use system_id because they exist in both the x_qsc_q_sys_reflec_systems and x_qsc_q_sys_reflec_system_items table.

13. Select Submit.
14. Now we are able to correlate Core ID and System ID to Core Name and System Name since both fields exist in the x_qsc_q_sys_reflec_systems table.

15. You can click the Try It button under related links on the left hand side of the page and it will open up the default view of the system items table as shown by default in the Q-SYS Reflect App for ServiceNow.

16. Right click on any column header and select Configure > List Layout to be able to change what data is displayed.

17. You will see that now we have available data from both tables to be able to display.  Fields that are named the same in both tables will shown twice with their prefixes we defined early to show which table you will be displaying.

18. Let replace Core ID and Design ID with Core Name and Design Name from the available fields and add System Name to the selected list. After adding those select Save.

1. Now the list will show the Core and Design Name in a column (Figure 13).

19. The final step would be adding this to location where you can find this list.  You can either add it to your favorites, create a new application menu to put it under, or modify a existing application menu and add it there.
    1. To add to favorites click the star button at the top right to add it your favorites.

2. To add it to a existing application menu navigate to All > System Definition > Application Menus.
3. Select the application you want to add it to.  For our example we will add it into the Q-SYS Reflect App menu.

4. Select the New button on the right hand side of the screen.
5. Give it a Title and assign it a role if so desired.

   ServiceNow Roles Further Information

   For more information from ServiceNow about roles please refer to the ServiceNow Technical Knowledge Base.

    

20. Select the Link Type tab and in the table field select our database view table.

21. Select Submit and now when using the All menu and searching for the App it will show up.

22. This example is just a demonstration on how to join tables together in ServiceNow and there are many other combinations and refinement possible.  For further information and functionality it is recommended to review ServiceNow training and work with your ServiceNow admin on implementation into your instance.