Get related list row count

Kim Jenson · ‎04-17-2018

Hello Experts!

I am trying to get a row count from a related list and populate a field on the form. Is this possible? For example: I need to get a total count of Catalog Items (32 in this case) in the below screen shot of the 'sc_cat_item_category' related list, and place the value in the 'u_cat_total' on the 'sc_category' table.

Please help!! Thanks!

The SN Nerd · ‎04-17-2018

So in the 'Calculated' script, you could put the following:

(function calculateValue(current) {
var categoryCount = 0;
var gr = new GlideAggregate('sc_cat_item_category');
gr.addQuery('sc_category', current.sys_id);
gr.addAggregate("COUNT");
gr.query();
while(gr.next()) {
categoryCount = gr.getAggregate("COUNT");
}
return categoryCount;
}(current);

ServiceNow Nerd
ServiceNow Developer MVP 2020-2022
ServiceNow Community MVP 2019-2022

View solution in original post

The SN Nerd · ‎04-17-2018

I would suggest the following:

Make 'u_cat_total' a Calculated field
In the Calculation script, add code which emulates the query shown in the related list (but using a GlideAggregate count)

And your done!

ServiceNow Nerd
ServiceNow Developer MVP 2020-2022
ServiceNow Community MVP 2019-2022

Kim Jenson · ‎04-17-2018

Hi thanks for your input, but the related list doesn't really have any code.

This is the related list, no filters or script. Any ideas how I can apply this to the calculated field script column? Thanks.

Shishir Srivast · ‎04-17-2018

I think you can have before update BR on sc_category table with below script, hope this helps.

var gr = new GlideAggregate('sc_cat_item_category');
gr.addQuery('sc_category', current.sys_id);
gr.addAggregate("COUNT");
gr.query();
if(gr.next())
current.u_cat_total= gr.getAggregate("COUNT");

Kim Jenson · ‎04-17-2018

Thanks for your reply. I tried this but did not work.