question Re: GlideAggregate is not working in Developer forum

GlideAggregate is not working

sudhir041 — Tue, 22 Nov 2022 20:19:06 GMT

I have a script and currently it is not working with GlideAggregate can someone please rectify it:

var RITM = new GlideRecord("sc_req_item");

RITM.addEncodedQuery("state=2"); //state is in progress

RITM.query();

while (RITM.next()) {

var p= 0;

var Task = new GlideAggregate("sc_task");

Task.addQuery("request_item", RITM.sys_id);

Task.addAggregate('COUNT');

Task.query();

var total = Task.getAggregate('COUNT');

while (Task.next()) {

if (Task.active.toString() == 'false') {

p++;

}

if (p == total && total != 0) {

gs.print(RITM.number);

}

Re: GlideAggregate is not working

Goran WitchDoc — Tue, 22 Nov 2022 20:21:51 GMT

Hi,

Can you explain what you are trying to achieve with this code, I think it might be possible to make it simpler than this.

Re: GlideAggregate is not working

sudhir041 — Tue, 22 Nov 2022 20:25:07 GMT

I need the RITMs whose all tasks are active false but RITMS are still in progress. So I need to achieve this

Re: GlideAggregate is not working

Mike_R — Tue, 22 Nov 2022 20:28:42 GMT

This can be easily achieved through a report

Re: GlideAggregate is not working

sudhir041 — Tue, 22 Nov 2022 20:30:04 GMT

Hi can you please once rectify my code i was trying through scripting to achieve this

Re: GlideAggregate is not working

Mike_R — Tue, 22 Nov 2022 20:34:18 GMT

Try this

var ritm = new GlideRecord('sc_req_item'); ritm.addEncodedQuery('active=true^RLQUERYsc_task.request_item,=00^active=true^ENDRLQUERY'); ritm.query(); gs.print(ritm.getRowCount()); while(ritm.next()){ gs.print(ritm.number); }

Re: GlideAggregate is not working

sudhir041 — Tue, 22 Nov 2022 20:36:01 GMT

can i not use glide aggregate?

Re: GlideAggregate is not working

Mike_R — Tue, 22 Nov 2022 20:42:46 GMT

What are you trying to accomplish exactly? What's the end goal once you have the aggregate count?

Re: GlideAggregate is not working

Saurabh Gupta — Tue, 22 Nov 2022 20:43:25 GMT

Hi @sudhir041 ,

var ritmArr=[]; var RITM = new GlideRecord("sc_req_item"); RITM.addEncodedQuery("active=true"); RITM.query(); while (RITM.next()) { var Task = new GlideRecord("sc_task"); Task.addQuery("request_item="+RITM.getUniqueValue()+"^active=true") Task.query(); if (!Task.next()) { ritmArr.push(RITM.getValue('number')) } } gs.info(ritmArr)

This will give you all RITM without any active task.

Re: GlideAggregate is not working

sudhir041 — Tue, 22 Nov 2022 20:52:55 GMT

I need the RITM so instead of GlideRecord i wanted to go ahead with GlideAggregate.

Re: GlideAggregate is not working

Mike_R — Tue, 22 Nov 2022 20:55:29 GMT

The code I provided will give you that and also let you access the RITM details.

Re: GlideAggregate is not working

Goran WitchDoc — Tue, 22 Nov 2022 21:16:52 GMT

Here is a script that can do that. Depending on the instance it's going for, amount of RITMs, active tasks and so on it might need to be tweaked. Perhaps even run as a flow instead and so on. I would also probably throw in some more checks in case there aren't any RITMs with state "in progress" or active tasks.

Re: GlideAggregate is not working

Goran WitchDoc — Tue, 22 Nov 2022 21:17:48 GMT

I would avoid dot-walking in an encoded query.

Re: GlideAggregate is not working

Mike_R — Tue, 22 Nov 2022 21:37:09 GMT

@Goran WitchDoc , good to know. So would you always avoid using "RLQUERY"?

Re: GlideAggregate is not working

Goran WitchDoc — Tue, 22 Nov 2022 21:46:24 GMT

If possible yes. To avoid the number of calls to the server. Same thing with your code below. Avoid having nestled glideRecord calls. What I mean is having a gliderecord query inside a while loop.

E.g. My code below does 2 calls to the server no matter how many tasks or RITMs there are. In your code, if there are 1000 RITMs, it does 1001 calls to the server.

Re: GlideAggregate is not working

Mike_R — Tue, 22 Nov 2022 22:01:32 GMT

Thanks for the insight! I don't use "RLQUERY" that often but I have used it before in simple fix scripts solely for the convenience.

It would be helpful if ServiceNow had some documentation around "RLQUERY". It's my understanding that this is the same thing that's used for "Related List Conditions" in report builder. Also, I've seen several posts online of people promoting it as a useful "hidden" feature. I'll take your word and avoid it where possible but would love if ServiceNow could publish/document best practices around this.

Re: GlideAggregate is not working

Goran WitchDoc — Tue, 22 Nov 2022 22:14:17 GMT

for reports, you probably don't have any other way around it and that is the way forward if you want to have it in there.