Need help creating a report from database view (where clause help)

[Fabricio4]

Hi friends!

So, I need to creat a list type report from the pm_project table. However, in that list I also want to show the columns from the project_status table, to see in the list view if the most recent status is green, yellow or red.

For that, I created a database view using both tables. In the Where clause of the project_status table I've input the following: prj_sys_id=prjstat_project. The pm_project table has the prj variable prefix, while the project_status table has the prjstat variable prefix.

To show every record in the pm_project table, even the records that do not have a report status, I enabled the left join on the project_status table and it worked, the list shows every record from the pm_project table.

However, the list also shows repeated records. By that I mean that if a project has more than one project status report, there will be repeated records (one for each project status record related to that project). I do not want that. I want to filter the list so it only shows one record for each project, and in the case of a project with multiple status reports, only the latest one would be in the list.

Any way to do that through the Where clause? The only solution I could think of is creating a custom flag on the project_status table and a business rule to check if that report is the latest one. Then, filter the list by this flag.

Any suggestions? Many thanks!

[SoniaShridhar13]

Hi @Fabricio4  inc_sys_id = al_sys_id use this

Thanks,

Sonia

[Fabricio4]

Hello @SoniaShridhar13 ,

Unfortunately, I don't think this addresses my issue.

Thanks

[Shiela De Los 2]

Hi @Fabricio4,

I am curious if you have found a solution to your issue. I am creating a report via database view as well. And same with you, I get a repeated records and would like to remove or filter the duplicates but do not know how.

Are you able to share the solution you took if you already figured it out.

Thank you in advance!

Shiela

[Fabricio4 · Accepted solution]

Hi, @Shiela De Los 2 !

Sorry it took so long to get back to you.

Yes, I did find a solution, and it was easier than I thought. I simply used the "Left Join" parameter and it worked. Check the image attached. Hopefully it helps you or somebody else!

Cheers