Head count field calculating but generating weird query results

[MBarrott]

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

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