Head count field calculating but generating weird query results

MBarrott · ‎03-12-2025

I'm having the cmn_department table's head_count field calculate based on the number of active users assigned to that respective department.

The query is returning accurate values however the list views and query results are not calculating correctly.

For example, I cannot order the Department list view by Head count. Additionally, grouping by Head count throws every department into the column of (empty), despite there being data present.

Any idea what's happening here?

Script:

(function calculatedFieldValue(current) 
{
	// declare GlideAggregate var for sys_user table and count all ACTIVE users in a respective department
	var record = new GlideAggregate("sys_user");
	record.addAggregate("COUNT");
	record.addActiveQuery();
	record.addQuery("department", current.getUniqueValue());
	record.query();

	if(record.next())
	{
		return record.getAggregate("COUNT"); // return total users found in department
	}
	else
	{
		return 0; // return 0 is no users are found 
	}
})(current);

MBarrott · ‎03-14-2025

I was actually able to resolve this issue myself via a Scheduled Job.

This will run every day to give me an accurate head count for active users within a department.

var deptgr = new GlideRecord('cmn_department');
deptgr.query();

while(deptgr.next())
{
	// declare GlideAggregate var for sys_user table and count all ACTIVE users in a respective department
	var record = new GlideAggregate("sys_user");
	record.addAggregate("COUNT");
	record.addActiveQuery();
	record.addQuery("department", deptgr.getUniqueValue());
	record.query();

	if(record.next())
	{
		deptgr.head_count = record.getAggregate("COUNT"); // return total users found in department
	}
	else
	{
		deptgr.head_count = 0; // return 0 if no users are found 
	}

	deptgr.update();
}

View solution in original post

Bert_c1 · ‎03-12-2025

Seems a business rule defined on the cmn_department table would work better using your logic to set the 'head_count' value on that table.

MBarrott · ‎03-12-2025

Hey @Bert_c1 - I actually just fixed this issue using a scheduled job script, but I'm curious on what you recommend BR wise. Always open to more optimal solutions. My requirement is that I need this value populated, up to date and readily available on the backend for future queries against the table.

Bert_c1 · ‎03-12-2025

I would use a BR defined on the 'sys_user' table that run when the 'department' field changes, to adjust the 'head_count' on both the 'current' and 'previous' value of the 'department' field. Now that you have a fix script that initiazes the value.

Toderean alexan · ‎03-12-2025

Hello @MBarrott,

head_count field is it defined as an integer data-type or string? If I am not wrong, getAggregate() method returns s tring data-type. How do you update the field? There is a periodically update?