Requesting DB View to allow reporting on related Project Tasks, Changes, and Outages

[abbasshaik4]

Hi Everyone,
I'm new to working on stories and not getting the below requirement, can someone please help me on this:

Requesting DB View to allow reporting on related Project Tasks, Changes, and Outages. An M2M table exists for Changes and Project Tasks: M2m Change Reque Project Task [u_m2m_change_reque_project_task]. The expectation is a DB View with a Join to the Outage related to the target Change will allow for reporting needs.

Thanks & Regards,

Abbas Shaik

[abbasshaik4]

Hello @Dr Atul G- LNG or @Mark Manders ,
Yes i have created DB view and added tables screenshot's attached below:

when iam pulling the report data is not populating because there is no link between these  tables Project Task,Change Request,Outage and M2m Change Reque Project Task.

OR 
How to create the relationship between these tables Project Task,Change Request,Outage and M2m Change Reque Project Task.

Can you please assist me on this?

Thanks & Regards,

Abbas Shaik

[Dr Atul G- LNG]

Hi @abbasshaik4 

The WHERE clause is missing in the DB view, so it won’t work as expected. You need to identify what’s common between these tables and use that in the WHERE clause — otherwise it won’t function.

[abbasshaik4]

No, i defined the where clause still is not working so i removed it. below is the where clause condition for below tables:

                                                where clause
project task                               m2m.project_task = pt.sys_id
Change request                    ot.change_request = change.sys_id
M2m change                               change.sys_id = m2m.change_request


Regards,

Abbas

[Dr Atul G- LNG]

Hi @abbasshaik4 

• Create a simple database view for Project Tasks and Changes.

• Since, as you mentioned, it’s a many-to-many relationship, there is a field in either Change or Project that links the two. Use this field in the database view to establish the relationship.

• Validate the results:

  • Check the view in List View for Project Tasks to see if there is a corresponding Change (and vice versa).

  • If the results are correct, you can add the relevant table with the Change information.

• You can refer to the OOTB database view for guidance on building the WHERE clause and filtering the data.

[abbasshaik4]

@Dr Atul G- LNG , I'm not able to see that field can you please tell the exact field name where i need to link the relationships?


Regards,

Abbas