How can I show the latest status report after joining the Project and Project Status table?

Ryan157
Kilo Contributor

‎09-26-2019 10:54 AM

Hello, I initially was able to get help to join the Project and Project Status table through a database view. This helped me list all the projects whether or not it has a status report or not. 

Now, I need help on to just show the latest and greatest status report. If the project has several status reports, it lists all the status report. I just need to see the newest one or the one that was created within the week. 

 

find_real_file.png

Labels:
Preview file
30 KB
0 Helpfuls
  • 1,956 Views
3 REPLIES 3

Adam Stout
ServiceNow Employee
ServiceNow Employee

‎09-26-2019 11:25 AM

  1. Add a field to pm_project called Last Status.
  2. Add a BR on project_status that when project or the date changes, to update the field on pm_project
  3. Report on pm_project and dot walk to get the last status report info.  Including
  • Projects without a status report
  • Projects with no status report within the last 14 days old
  • Show group by latest overall status

Ryan157
Kilo Contributor
In response to Adam Stout

‎11-01-2019 06:32 AM

@adam - haven't had any success in making this work. 

So far, I have created a report, with the following filter:

- Status date is relative to the last 7 days 

- Status date is empty 

 

I have created a last status date but can't make the BR to work... so i've been mocking the dates up and still couldn't figure out how to make the last status report show and projects with an empty status report. 

0 Helpfuls

Andrew Payze
Kilo Guru
In response to Ryan157

‎11-26-2019 11:21 AM

Hi Ryan,

Here is a business rule that does what Adam suggested.  You will need two fields in pm_project, u_project_status, date and u_project_status, reference to project_status table.

The script checks each time and grabs the latest dated status report then updates the project.  This means if a PM changes around dates, it will grab the latest one.  For the BR set it for after and on insert and update

Hope this helps

(function executeRule(current, previous /*null when async*/) {
	
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;
      gs.addInfoMessage('Updated Last Project Status Date to: ' + projectGr.u_status_date);
      projectGr.update();
   }
 
})(current, previous);

 

0 Helpfuls