Anyone know why using GlideAggregare MAX/MIN on a datetime field returns null?

DrewW · ‎12-17-2014

This is the code I have

var minmax = new GlideAggregate("change_task");
minmax.addQuery("change_request", "a7850aa9a901f500aca25ed0e13f9623"); //current.getValue("sys_id"));
minmax.addAggregate('MIN', 'work_start');
minmax.addAggregate('MAX', 'work_end');
minmax.query();
if (minmax.next()){
       gs.log("minmax.getAggregate('MIN'): " + minmax.getAggregate("MIN"), "Update Change work notes");
       gs.log("minmax.getAggregate('MAX'): " + minmax.getAggregate("MAX"), "Update Change work notes");
}

Trying to find from the change tasks for a particular change what the oldest start date is and the newest end date. The aggregate always returns null. Anyone have any thoughts?

DrewW · ‎05-26-2020

Wow, this is an old one. There are actually several issues with the code. As you mentioned I needed to add the field name, the next is that you do have to filter out the nulls like Mike mentioned because they will cause issues and the last is that it needs to be two different query's so that you get the correct MIN and MAX.

I never posted that when I figured out what I was doing wrong.

So this is what the corrected code should look like

var minmax = new GlideAggregate("change_task");
minmax.addQuery("change_request", "a7850aa9a901f500aca25ed0e13f9623"); //current.getValue("sys_id"));
minmax.addAggregate('MIN', 'work_start');
minmax.query();
if (minmax.next()){
       gs.log("minmax.getAggregate('MIN'): " + minmax.getAggregate("MIN", "work_start"), "Update Change work notes");
}

minmax = new GlideAggregate("change_task");
minmax.addQuery("change_request", "a7850aa9a901f500aca25ed0e13f9623"); //current.getValue("sys_id"));
minmax.addAggregate('MAX', 'work_end');
minmax.query();
if (minmax.next()){
       gs.log("minmax.getAggregate('MAX'): " + minmax.getAggregate("MAX", "work_end"), "Update Change work notes");
}

View solution in original post

Daniel Oderbolz · ‎05-26-2020

Dear Drew

Thanks for taking the time to wrap this up.

The reason I commented was that I used the code snippet above and ran into problems 🙂

Thanks a lot & Best
Daniel

If this answer was helpful, I would appreciate if you marked it as such - thanks!

Best
Daniel