DATA base view

[Shabbir1]

HI team,

We have two ticket tables ( 1 HR and 2. payroll) requirement is to show two tables tickets into one list. Opened for is common field which is referring to user table in both tables can we use that ..?can anyone help to create data base view step by step process confused about "where clause" field in data base view or any other best solution

[Ankur Bawiskar]

@Shabbir1 

both tables should have it's own Number prefix

So that should help them

💡 If my response helped, please mark it as correct ✅ and close the thread 🔒— this helps future readers find the solution faster! 🙏

[Tanushree Maiti]

Hi @Shabbir1 

• Navigate to Database Views
  • In the Filter Navigator, go to System Definition > Database Views.
  • Click New.
• Create the Database View Record
  • Name: Give it a unique name (e.g u_hr_payroll_view).
  • Label: Combined HR and Payroll Tickets.
  • Click Submit (save the form to add tables).
• Add Table 1: HR Tickets
  • Open your new record, go to the View Tables related list, and click New.
  • Table: Select your HR Table ( Like HR case table sn_hr_core_case).
  • Variable Prefix: hr
  • Order: 100
  • Click Submit.
• Add Table 2: Payroll Tickets
  • Click New in the View Tables related list again.
  • Table: Select your Payroll Table 
  • Variable Prefix: pay
  • Order: 200
  • Left Join: Checked.
  • Where Clause : hr_opened_for = pay_employee_name (If you want to link payroll tickets to HR tickets via employee_name // change user reference field as per your requirement)
  • Click Submit.

Next  Type u_hr_payroll_view.list in Application navigator.

Instead of importing all columns in , you can select only needed columns to improve performance:

1. In the View Table form, look at the View Fields related list.
2. Click New to select specific fields to include from that table e,g number, opened_for,employee_name,short_description etc.

For testing : Verify that both HR and Payroll records appear in the same list. 

👍

[Shabbir1]

Thanks @Tanushree Maiti 

Tried its working But in the list view its mixed up with both tables data how can assignee know which is payroll related data and which is hr related data if they want to download the excel report..? sharing screen shot for reference

[Tanushree Maiti]

Hi @Shabbir1 ,

Generally from native ui , I arrange column names of database view table in such a way so that first all column of Table 1 shows, next table 2 columns.

If you want to create Report, you check it -> KB0723527 Change a label for a report without changing the label for the underlying table 

[Shabbir1]

Hi @Tanushree Maiti  could you please assist me how can i do this ""Generally from native ui , I arrange column names of database view table in such a way so that first all column of Table 1 shows, next table 2 columns.""