Help with script

[Community Alums]

Hi all,

I need a help with a transform script. I need to change the logic of this script: 

(function runTransformScript(source, map, log, target) {

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

        ignore = true;

        return;

    }

    var excelDateStr = source.u_read_submission_due__t105r_.toString();

    var excelDate = new GlideDateTime(excelDateStr);

    var dueDate = new GlideDateTime(target.due);

    var today = new GlideDateTime();

    var closestDate = new GlideDateTime('9999-12-31 23:59:59');

    if (excelDate.after(today)) {

        if (excelDate.before(closestDate)) {

            closestDate = excelDate;

        }

    }

       if (closestDate.before(dueDate) || dueDate.before(today)) {

        target.due = closestDate.getDisplayValue();

    } else {

        ignore = true;

    }

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

Currently it ignores records where the field source.u_status=Failed, so for records where the field source.u_status= Open then initializes a far future date as a placeholder for comparison, finds the closest future date from the source field that is after today’s date then updates the target.due date field with this closest future date. 

What I actually need this script to do is to: when the spreadsheet is imported into the system, the script should ignore all the records where source.u_status=Failed and will need to check and compare all the dates (one or multiples) from the source field u_read_submission_due__t105r_ records and ignore the comparison with target.due date field. For example if u_read_submission_due__t105r_ dates are 2024-12-24, 2025-10-02 or 2025-07-09 then 2024-12-24 should be updated on the target.due date field as it's the closest to todays date.

Any help? thanks

[sadif_raja · Accepted solution]

Just Check this code:

(function runTransformScript(source, map, log, target) {

// Ignore the record if the status is 'Failed'
if (source.u_status == 'Failed') {
ignore = true;
return;
}

// Assuming u_read_submission_due__t105r_ has multiple dates (comma-separated or similar format)
var dateStrArray = source.u_read_submission_due__t105r_.toString().split(','); // Modify this if the dates are in a different format.
var today = new GlideDateTime();
var closestDate = new GlideDateTime('9999-12-31 23:59:59'); // Placeholder for far future date.

// Iterate over each date in the source field
dateStrArray.forEach(function(dateStr) {
var excelDate = new GlideDateTime(dateStr.trim());

// Check if the date is in the future compared to today
if (excelDate.after(today) && excelDate.before(closestDate)) {
closestDate = excelDate;
}
});

// Update the target.due field with the closest date if a future date is found
if (closestDate.before(new GlideDateTime('9999-12-31 23:59:59'))) {
target.due = closestDate.getDisplayValue();
} else {
// If no valid future date was found, ignore the record
ignore = true;
}

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

[Community Alums]

Hi @sadif_raja I will test this today and let you know, thanks for your help

[sadif_raja]

Thanks Dev86