Get related list row count

[Kim Jenson]

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 · Accepted solution]

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

[Kim Jenson]

Thanks Paul, that worked like a charm! The only question I have is that it made the field read only. And I understand why, however if there is a need, what's the best practice around making that field editable? 

[The SN Nerd]

ServiceNow makes calculated fields read only by default.

You are not actually able to make it editable - and is probably for the best. 

[Kim Jenson]

I figured it's for the best to have that read only. Thank you!

[Chuck Tomasi]

I love the answer Paul gave. One note, calculations can be expensive. Consider the case when you show a list of those records and each one needs to calculate a value.

YES - the value is stored in the record once its calculated, however it needs to be refreshed periodically - especially for records that have NOT been updated and don't have the calculated value saved. Those older records w/o a value need to run the script - so it's going to query the database each time you display the record.

Thankfully, Paul used GlideAggregate instead of GlideRecord to count the rows so it's not AS bad as it could be, but be aware of what you are doing when you ask the database to get records or a count for you and where that could blow up in your face. 🙂