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

Andrew Payze · ‎11-25-2019

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);

Chuck Tomasi · ‎11-26-2019

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()
}

View solution in original post

Andrew Payze · ‎11-26-2019

Chuck

This helped me get it right:-)

Thanks as always

Andrew