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

[DrewW]

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?

[mike_coletti]

Drew,

I'm having the same issue and what I found is that one of the records must have a blank work_start or work_end.

To resolve this, I would add the following:

minmax.addNotNullQuery('work_start');

minmax.addNotNullQuery('work_end');

I did the same thing to resolve the issue I was having and it worked for me.

[Daniel Oderbolz]

I know it is an old thread, but still a clarification:

If you search both for the max and the min in the same query, adding the lines

minmax.addNotNullQuery('work_start');

minmax.addNotNullQuery('work_end');

Will not produce the same result as when you do it in two queries.

I am pretty sure that doing it in two separate queries is safer (because the actual minimum of one field might have null in the other and vice-versa).

[Daniel Oderbolz]

Also, you need to provide the field name when getting the aggregate:

if (minmax.next()){


       gs.log("minmax.getAggregate('MIN'): " + minmax.getAggregate('MIN', 'work_start'), "Update Change work notes");


       gs.log("minmax.getAggregate('MAX'): " + minmax.getAggregate('MAX', 'work_end'), "Update Change work notes");


}

Best
Daniel

[DrewW · Accepted solution]

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");
}