Report showing last status update for each project

[David Sloan]

I am trying to generate a report that shows any projects that do not have a status report from the last 7 days. I have created a database view called u_project_status_date with two view tables pm_project (prefix proj) and project_status (prefix rep). I marked the second table as a left join with the where clause proj_sys_id=rep_project, but this gives me all of the status reports for each project, and I want only the most recent status report for each project (or Status Date empty if applicable). How do I make my right table return only the most recent date for each project?

[David Sloan]

Well that is handy! I don't have a "Related List Conditions" option. Is this because I am using List v2 instead of List v3? Do I need to change that or is there another way to do this?

[Adam Stout]

You can do it in a report (in report designer but not in the legacy report builder), but it requires list v3 if you want to do it in a list view.  It will work in list view if you create it in a report and click to view the records and then save that as a module or a favorite.

[David Sloan]

It looks like I can get closer by creating this database view:

Then in my report I filter out anything with a status date after 6/1/19 and any inactive projects. But can I make the date dynamic? It looks like I can't use javascript in the Where clause of a View Table. Is there a way to make my Where clause dynamic?

[Adam Stout · Accepted solution]

If you want the last date submitted, I recommend adding a field to pm_project that is "Last Status Report" that is a reference to status report and is updated by a BR on status report (updates the reference when status reports are submitted).

This will allow you to dot-walk to get the last submitted date (would also allow you to filter for a report) but also allows you to dot-walk in fields like last overall status.

[David Sloan]

Thanks! That was a lot easier than everything I was trying.