Query GlideAggregate

[davide_fais]

Hi ServiceNow Community,

I've this problem and I hope someone can help me:

I've this table [u_history_task] and I've need to run a job that calcolate for each record value of columns "u_order" and "u_last".

This is a screen of table with an example of records before and How it should be after run the job:

thanks,

Best Regards.

Davide.

[Goran WitchDoc]

If you could give a bit more information about what you want to "count" we could give you a example with GlideAggregate instead. I would recommend using GlideAggregate instead of GlideRecord from the example above and also avoid using nested glide queries as the example above. Both out from performance view and counting and stuff is what GlideAggregate is for.

//Göran

[Goran WitchDoc · Accepted solution]

Ahh... Now that I after a while look at your pictures I think I get what you after.. sometimes you just stares blind on something 😃

Something like this and it only does 2 server calls.

var count = 1; //Get the counter read

var gr = new GlideRecord('change_task'); //Fetch the records we want to update

gr.orderBy('NAME OF YOUR TASK FIELD');

gr.orderBy('sys_updated_on');

gr.query();

var gr2 = new GlideRecord('change_task'); //Fetch another set of record so we can compare to the "next" record

gr2.orderBy('NAME OF YOUR TASK FIELD');

gr2.orderBy('sys_updated_on');

gr2.query();

gr2.next();//Skip ahead one record so gr2 always is one record ahead of gr

while(gr.next()){

if(!gr2.next()){//If there isn't a next record in gr2, gr is the last one

gr.setValue('u_last',true);//To handle the last record

}

gr.setValue('u_order',count);

//Check if the is is the last one and reset the counter if that is true.

if (gr.getValue('NAME OF YOUR TASK FIELD') != gr2.getValue('NAME OF YOUR TASK FIELD')){

gr.setValue('u_last',true);

count = 1;

}

//If it isn't the last one, add 1 to the counter

else {

count++;

}

gr.autoSysFields(false); //Guess you don't want the sys_updated_on field to be updated when you run your script.

gr.update();

}

//Göran

[Goran WitchDoc]

And the really short version:

var count = 1, gr = new GlideRecord("change_task");

gr.orderBy("parent");

gr.orderBy("sys_updated_on");

gr.query();

var gr2 = new GlideRecord("change_task");

gr2.orderBy("parent");

gr2.orderBy("sys_updated_on");

gr2.query();

for (gr2.next(); gr.next();) {

  gr2.next() || gr.setValue("u_last", true);

gr.setValue("u_my_order", count);

gr.getValue("parent") != gr2.getValue("parent") ? (gr.setValue("u_last", true),count = 1) : count++;

gr.autoSysFields(false);

gr.update();

}

[Rama Chandra D]

Hi Goran,

Even though the script you've provided is a 'run on demand' script, shouldn't it be a concern to query an entire table and do these database sort of operations?

When I read the post, I initially thought of a script that could update the values in these fields based on then existing records. Probably on insert or update. And also, since I know the current record which triggered the script, I know which other records( the one with same parent) need to be updated.   So, in my understanding, if I have a single entry for record X, I will set both values. Then, when I have second entry for same record X, I would clear the last field of the previous record and update the fields on the new record.

So anytime, I would've the fields populated rather than computing everything at once. Can you clarify, if I'm thinking a wrong way?

Regards,

Darshak

[Goran WitchDoc]

Yes,

I took for granted that this was a "one time" run script. And also querying a table that wasn't so big. Normally if it's huge, people tend to say so, but of course, it shouldn't always be taken for granted. But I'm guessing the requirements to run this once to hit those records that is already "done" and needed a update.

To have this in production and hitting records in the future, the script would be in a Before BR and look totally different. And I was guessing that those records that already exits where "done" so people were'nt going to update them again(and getting hit by the before BR).

//Göran