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

[Bert_c1]

I updated my post with a script example.

[Erik Nelson]

Thanks, I'll take a look at it and try it out.

[Bert_c1]

the first line needs to be:

var now = new GlideDateTime();

(I was missing the "var " for the variable declaration)

And if your fields are GlideDate and not GlideDateTime, then change the first 2 lines to use GlideDate values.

[Bert_c1]

And I see now that the record state needs to be saved, and that only 'open' records need to be processed.  An updated example script:

var 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.addQuery('u_state', 'open');		// depends on values for the u_state field.
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';
		dateTable.update();			// save the new state
	}
}

log statements can be added if desired.