Help with a script

[Community Alums]

Hi all, 

I have this onBefore transform script that runs when a spreadsheet is imported in the system. It currently ignores all the records where "u_status" = 'Failed' but when there are multiple records with same serial number “u_meter_serial_number” , It should update the "due" field on the target record with closest future date from todays date. Eg. If 24/09/2024 is the current todays date, and on the spreadsheet there are 25/10/2024 and 24/11/2024 it should then update the "due" field with the 25/10/2024 which is closest future date. Currently is not ignoring the incorrect dates and it updates the "due" target field with the last date listed on the spreadsheet. Any idea how can I filter out the incorrect dates? Thanks. This is the current script:

(function runTransformScript(source, map, log, target /*undefined onStart*/ ) {

if (source.u_status == 'Failed') {

        ignore = true;

        return;

    }

var nextReadDate = new GlideDate();

nextReadDate.setDisplayValue(source.u_read_submission_due__t105r_, "yyyy-MMM-dd");

var dueDate = new GlideDateTime(target.due);

var currentNextReadDate = dueDate.getDate();

if(nextReadDate < currentNextReadDate){

    ignore = true;

}

})(source, map, log, target);

[Akash4]

Hi Dev,

Maybe you need to filter out the past dates, try this below corrections.

(function runTransformScript(source, map, log, target /*undefined onStart*/ ) {

if (source.u_status == 'Failed') {
ignore = true;
return;
}

var nextReadDate = new GlideDate();
nextReadDate.setDisplayValue(source.u_read_submission_due, "yyyy-MM-dd"); //ensure right field name

var today = new GlideDate();
today.setDisplayValue(gs.nowDateTime().getDisplayValue());

if (nextReadDate.compareTo(today) > 0) {
var dueDate = new GlideDateTime(target.due);
var currentNextReadDate = dueDate.getDate();

if (nextReadDate.compareTo(currentNextReadDate) < 0 || currentNextReadDate.compareTo(today) <= 0) {
target.due = new GlideDateTime(nextReadDate);
}
}

})(source, map, log, target);

[Community Alums]

Hi @Akash4 thank you

I tried that, but I am still getting the wrong date updated in the "due" target field. I have two status=open records  with dates 2024-08-09 and 2025-05-29, should update with this 2025-05-29, but I am getting this 2024-08-09 just because is the last row on the spreadsheet. 

[Akash4 · Accepted solution]

Hi Dev,

Maybe we need to check if nextReadDate is greater than both today and the current due date before updating. Try the v2 of the below:

(function runTransformScript(source, map, log, target /*undefined onStart*/ ) {
if (source.u_status == 'Failed') {
ignore = true;
return;
}
var nextReadDate = new GlideDate();
nextReadDate.setDisplayValue(source.u_read_submission_due, "yyyy-MM-dd"); 

var today = new GlideDate();
today.setDisplayValue(gs.nowDateTime().getDisplayValue());

if (nextReadDate.compareTo(today) > 0) {
var currentDueDate = new GlideDateTime(target.due);
if (nextReadDate.compareTo(currentDueDate) > 0) {
target.due = new GlideDateTime(nextReadDate);
}
}
})(source, map, log, target);