Query Multiple Date Fields to find the furthest one out

[Erik Nelson]

Hello all!

I'm building out an app that has multiple date and time fields that can be populated, with all fields in a format ending "_edat". The ask I've been is to automatically close the record when the furthest date out has passed. What I'm wondering is if there is a way to query all the "_edat" fields at once and return the furthest one out. I plan to use that returned date to populate a hidden field that can then drive "closing" the record. In total, unfortunately, I have 31 of those fields (it's a weird/fun app). Any thoughts or assistance is greatly appreciated!

Thanks,

Erik

[Hemanth M1]

Hi @Erik Nelson ,

Why don't you have a flow scheduled to run daily to check (loop through all the date/time fields in an array) and check if any of these fields pass the current day, then close the record (maybe you can update the record saying this field cause passed the due date, hence closing the record).

Hope this helps!

[Erik Nelson]

Hi Hemanth,

If I was looking for any field past the current day, that might work. What I'm specifically needing is for, lack of a better way to see it that I can think of, it to know the "last" field left to be past the current date. That's why I'm leaning towards something that can just populate a hidden field for use.

Thanks,

Erik

[Bert_c1]

You can do that with a script, however, you've provided few details to comment further. but here's a start.

now = new GlideDateTime();
var latestDate = new GlideDateTime('2025-01-01 00:00:00');		// initialize variable
var dateTable = new GlideRecord('u_date_table');			// name of table with 31 date fields.
dateTable.query();
// for each record, find latest date
var firstFlag = 1;
while (dateTable.next()) {
	if (firstFlag == 1) {
		firstFlag = 0;
		latestDate = dateTable.u_1_etad;
		continue;
	}
	if (dateTable.u_2_etad > latestDate) {
		latestDate = dateTable.u_2_etad;
	}
	if (dateTable.u_3_etad > latestDate) {
		latestDate = dateTable.u_3_etad;
	}
	// copy and paste the 3 lines for each field
	// ...
	if (dateTable.u_31_etad > latestDate) {
		latestDate = dateTable.u_31_etad;
	}
	// Check is latest date has passed
	if (latestDate < now) {
		// close the record
		dateTable.state = 'closed';
	}
}

use that in a Scheduled job, that runs once per day.

[Erik Nelson]

Additional details - 

I've got 31 separate fields on a form that all have a column name ending in "_edat". Each of those fields can be populated independently of each other, but will likely not all be populated (it would be VERY bad if they were all populated since the app is for tracking Wildfire risks areas).  Regardless, I need a way to return the furthest out entry in the populated fields.

Example:
1_edat - Populated with 12/07/25

2_edat - empty

3_edat - Populated with 12/24/25

I need the latest non-empty value (in this case 3_edat) for use in automation of closing out the overall record.

Thanks and please let me know if you need additional details.