Creating a database view

[MStritt]

I'm trying to create a database view between 3 tables (below), so I can create a report and have all the fields for all 3 tables available. But, don't seem to be able to configure the 'Where clause' successfully.

Case: sn_customerservice_case
JIRA Issues: u_m2m_cases_jira_issues
Case Task: sn_customerservice_task

[SanjivMeher]

You need to find the field which connects these tables.

How is sn_customerservice_case connected to u_m2m_cases_jira_issues table?

I believe your first table should be sn_customerservice_task, it must already have a parent field which connects to sn_customerservice_case. from sn_customerservice_case you can connect to u_m2m_cases_jira_issues

[MStritt]

Hi Sanjiv,

You mean a field that is common between the Case table and Jira Issues table? On the Case table/form, we have a Jira Issues Related List. 

I believe your first table should be sn_customerservice_task, it must already have a parent field which connects to sn_customerservice_case

Correct.

We currently have a database view already created between the Case table and Jira Issues table. I just want to add the Case Task table to that view as well. But I can't figure out how to configure the 'Where clause'. Here's what the database view looks like between the Case and Jira Issues tables.

[SanjivMeher]

In the table records, you also need to add fields that you want to use in where clause and fields you want to display in the database views.

The task table is a one to many relation. So your task table should be top in order. And in the where clause of case table you need to add t_parent = c_sys_id, assuming t is the prefix of task table and parent field holds the case 

[MStritt]

So it would be:

1. Case Tasks

2. Case

3. Jira Issues

What would be the Where clause for the Jira Issues table?