There have been many questions about linking the project and project status tables together, primarily for reporting purposes. There has also be requests to dot walk from the project to the latest status report, as well as the ability to see the latest status date (latest report) at the project level and projects that do not have any status reports or have not had a status report since X days.
This articles covers two solution to address the above. The first solution is adding fields to pm_project that enable a user to see the last status report date and a reference to the last status report. You can use these fields in a report to check on the current status as well as dot walk. This includes a business rule that updates these fields each time a status report is either inserted or updated. The BR checks for the latest report when updating the fields.
The second solution creates a database view joining the project and project status tables. This view can show all projects even if they have a status report or not, in other words, identify projects missing status reports. While you could get a similar result from the above solution, it will only indicate if there is at least one status report, this solution will show all status reports (or as defined in a filter). If you combine this solution with the above, you could have a filter where the project->last status date and the status report->as_on date are equal to ensure you have the latest report only if needed.
Hope this provides a value add
Regards
Andrew
Solution 1 - Business Rule Script
function executeRule(current, previous /*null when async*/) {
// To update two fields in the pm_project table after checking the project_status table for
// latest project status report based on newest date
// Add as a Business Rule on the project_status table, As After and on Insert and Query
// Add two fields to pm_project, u_status_date, date and u_project_status, reference on project_status table
var lastDate = '';
var lastReport = '';
var curprj = current.project;
// Find all Project Status related records and determine the latest date
var sts = new GlideRecord('project_status');
sts.addQuery('project', curprj);
sts.setLimit(1);
sts.orderByDesc('as_on');
sts.query();
if(sts.next()) {
lastDate = sts.as_on;
lastReport = sts.sys_id;
}
// Update the project with the lastest / most current status date & reference
var projectGr = new GlideRecord('pm_project');
if (projectGr.get(current.project)) {
projectGr.u_status_date = lastDate;
projectGr.u_project_status = lastReport;
// Remove next line if you do not want a pop up message
gs.addInfoMessage('Updated Last Project Status Date to: ' + projectGr.u_status_date);
projectGr.update();
}
})(current, previous);Solution 2 - Adding a Database View
Screenshot
Andrew, thanks for the guide. I used your ideas to make it as simple as possible.
- Create a read-only date/time field on the project table (u_last_status_report).
- Create a 2-step Flow:
- Trigger on project_status table for Created.
- Update Project record with the Created date.
If you have to retroactively add these dates, you can keep running tests in flow designer. I'm sure there's a better way, but that's the no code way :).
Newbie to ServiceNow and creating reports. I want to create three separate reports that tracks what Andrew mentioned above:
- the latest status date (latest report) at the project level;
- projects that do not have any status reports; and
- have not had a status report since X days.
I am not sure what the conditions should be to create each of these reports. I have tried various combinations and to no avail. What are some suggestions for conditions to achieve these reports? 🤔
