Scripting Help- How can I compare multiple dates and choose the one closest to today?

[ssellmeyer]

I have a FD Flow created that I want to call a Flow Action from. I need to add a 'Script Step' in the flow Action that will do the following:

Look up all dates from a Date Field, on all Order Line Items, associated with a Customer Order.

Compare all those dates and return (as output) the date that is closest to today's date.

The input to the Flow Action will be the Customer Order

The output will be the date

Requirement:

Get all the dates from the field 'u_expected_date' on all Order Line Items, associated with a Customer Order. Compare those dates and return the date that is closest to today's day.

Help is greatly appreciated!!!

Thank you!

[Medi C]

@ssellmeyer 

You can achieve this by having an array of these dates that you would like to check, then you can use the following function or adjust it according to your needs:

// Define the function to get the index of the closest date
function getClosestDateIndex(datesArray) {
    // Get today's date (set to midnight to ignore time)
    var today = new GlideDateTime();
    today.setDisplayValue(today.getLocalDate() + ' 00:00:00');  // Normalize today's date to midnight

    // Initialize variables to track the closest date's index and the smallest difference
    var closestIndex = -1;
    var minDiff = null;

    // Loop through the array of dates
    for (var i = 0; i < datesArray.length; i++) {
        // Convert the date string to GlideDateTime (set to midnight)
        var currentDate = new GlideDateTime(datesArray[i]);
        currentDate.setDisplayValue(currentDate.getLocalDate() + ' 00:00:00'); // Normalize time to 00:00:00

        // Calculate the absolute difference in milliseconds between today and the current date
        var diff = Math.abs(currentDate.getNumericValue() - today.getNumericValue());

        // If this is the first date or the difference is smaller than the previous one, update the closestIndex
        if (minDiff === null || diff < minDiff) {
            closestIndex = i;
            minDiff = diff;
        }
    }

    // Return the index of the closest date, or -1 if no date is found
    return closestIndex;
}

//Usage:
var dateArray = ['2025-02-25', '2025-03-01', '2025-03-10'];
var closestIndex = getClosestDateIndex(dateArray);

gs.info("Index of closest date to today is: " + closestIndex);
gs.info("Closest date is " + dateArray[closestIndex]);

• Normalization of Dates: Use setDisplayValue(currentDate.getLocalDate() + ' 00:00:00') to make sure we're only considering the date portion (ignoring the time) when comparing the dates.
• Tracking the Closest Date: Track the index of the closest date in the array and compare the differences in their numeric values (getNumericValue()).
• Returning the Index: If the closest date is found, it returns the index of that date in the array. If no date is found (or the array is empty), it returns -1.

[ssellmeyer]

@Medi C 

Thank you for your response.

Would this be an Inline Script in the FD Flow, or code to put in the FD Action Script Step?

Please let me know.

Thank you!

[Medi C]

@ssellmeyer 
You can use a script step in Flow Designer Action

[DrewW]

You can start by doing two query's where you limit the number of records to 1 and order them by u_expected_date.  First query is u_expected_date <= today and the other is u_expected_date >= today.  That will give you two records that will be the closes ones to today.  Then you can use GlideRecord.u_expected_date.getGlideObject().getNumericValue() for the value of u_expected_date for each record.  Then its just a matter of finding the one that has the smallest difference from (new GlideDateTime()).getNumericValue().

//I have not tried this code at all but I think it will work.

var recBeforeToday = false;
var recAfterToday = false;
var nowDT = new GlideDateTime();
var rec1 = new GlideRecord("table");
rec1.addQuery("u_expected_date", "<=", nowDT);
rec1.orderByDesc("u_expected_date");
rec1.setLimit(1);
rec1.query();
if(rec1.next()) {
	recBeforeToday = true;
}

var rec2 = new GlideRecord("table");
rec2.addQuery("u_expected_date", ">=", nowDT);
rec2.orderBy("u_expected_date"); //Note orderby is different
rec2.setLimit(1);
rec2.query();
if(rec2.next()) {
	recAfterToday = true;
}

//So if recBeforeToday is true and recAfterToday is false then rec1 is the record you are looking for
//So if recBeforeToday is false and recAfterToday is true then rec2 is the record you are looking for
//If both are true then you have to find the one that has the smallest difference between nowDT and rec1.u_expected_date and nowDT and rec2.u_expected_date

var diff1 = nowDT.getNumericValue() - rec1.u_expected_date.getGlideObject().getNumericValue();
var diff2 = rec2.u_expected_date.getGlideObject().getNumericValue() - nowDT.getNumericValue();

if(diff1 < diff2) {
	//Rec1 is the one.
} else {
	//Rec2 is the one.
}