Script to find the latest record and update a reference table value using project_status table

[Andrew Payze]

We want to find the latest project_status->as_on value for a project and update pm_project->u_status_date.  When a user creates a new project_status report, it should in theory be the latest report (latest date) based on business process, however we want to validate that it is the latest before updating the project field

Here is what I have started with however it is not working... help please?

(function executeRule(current, previous /*null when async*/) {

var prj = new GlideRecord('pm_project');  
prj.addQuery('project', current.getUniqueValue());
prj.query();

//added in an attempt to check for the latest status report for the project	

var sts = new GlideRecord('project_status');  
sts.addQuery('project', prj.getUniqueValue());
sts.setLimit(1);
sts.orderByDesc('as_on');
sts.query();

while(sts.next()) {

  var lastDate = sts.as_on;

}

prj.u_status_date = lastDate;  
prj.setWorkflow(false);   
prj.update(); 

})(current, previous);

[Naveen20]

You can just have the below. I hope the business rule is on the table pm_project

(function executeRule(current, previous /*null when async*/) { 

var sts = new GlideRecord('project_status');

sts.addQuery('project', current.getUniqueValue());

sts.setLimit(1);

sts.orderByDesc('as_on');

sts.query();

if(sts.next()) {

var lastDate = sts.as_on;

}

current.u_status_date = lastDate;

current.setWorkflow(false);

})(current, previous);

[Chuck Tomasi]

Excellent use of setLimit(1)!

Watch the scope on lastDate. If you declare it inside the if, it will come up undefined if you don't find that record and cause an issue. Declare lastDate first, then set it in the if and use it afterwards.

(function executeRule(current, previous /*null when async*/) { 

  var lastDate = '';
  var sts = new GlideRecord('project_status');
  sts.addQuery('project', current.getUniqueValue());
  sts.setLimit(1);
  sts.orderByDesc('as_on');
  sts.query();

  if(sts.next()) {
    lastDate = sts.as_on;
  }

  current.u_status_date = lastDate;
  current.setWorkflow(false);

})(current, previous);

[Andrew Payze]

Hi Chuck & Naveen,

Thanks for the help.  I am actually running the BR on the project_status table because that is where the new record is created.  I want to make sure the created record has the latest date (as_on) and that this date is updated on the project table.

If I add the BR to project and never update the project however create status reports, I am missing how the value would get updated

Thanks again for your help

Andrew

[Chuck Tomasi · Accepted solution]

I'm no specialist on PPM, but it sounds like project_status is a child record of project. If that's correct, then your BR is on the right table and to update the project record, you simply use the project field (I'm assuming) to get the project record and update it. Simplistically, it would look like this:

var projectGr = new GlideRecord('pm_project');
if (projectGr.get(current.project)) {
  // make the update to projectGr fields here
  // projectGr.update()
}