How do I pull only the most recent Status Date on a Project # with multiple Status updates?

[jl11]

I am running a report on the Status Report Table and combining fields from the project table as well. 

I have listed the project # and Project Status # with other fields.  The report is pulling all Project Status updates for each Project.  I would like to only pull the most recent Project Status by the most recent Status Date on the Status Report Table.  

Is there a script I can write to state to  Status Date = Most Recent?

[Adam Stout]

The way I have done this is a BR on the status report table then on insert/update sets a reference field on the project table.  That makes the reporting much easier.

[priya0895]

I did that... but it runs only once for a status report. what if the status date changes again and again.

  for example forex prstat1 is status date updated yesterday
                            prstat 2 is status date updated today
                            prstat 1 status date again updated tmmrw? 

outcome : still prstat2 is coming 

[Adam Stout]

I'm not clear on what the issue is here.  Your BR should handle the logic of when to update the date and when not to.

[Padmanabam Tiru]

This script might help you. This will return the latest status report , you can tweak the script based on your requirement.

var overallHealth;
var gr = new GlideRecord('tsp1_project_status');
// gs.info("project--"+project);
gr.addQuery('project.number','=',project);
gr.orderByDesc('as_on');    // here as_on is the field which is nothing but status date of status report created
gr.setLimit(1);
gr.query();
if(gr.next()){
overallHealth = gr.getValue("overall_health");
}
else{
return null;
}