How do I populate/calculate the headcount field for Departments (cmn_department table) based on the Users department?

[Paul Wilson]

Hi there, 

how can I get the Head_count field in the Departments table to be calculated based on the total of users with that department selected. This needs to be updated perhaps once every 24 hours. 

Script examples would be very much appreciated 🙂

regards

Paul

[Community Alums]

Hi, please go to the dictionary entry for head_count and change it to a calculated value (must click advanced view to see this). 

Then insert the following script

(function calculatedFieldValue(current) {

	var record = new GlideAggregate("sys_user");
	record.addAggregate("COUNT");
	record.addQuery("department",current.getUniqueValue());
	record.query();
	if(record.next()){
		return record.getAggregate("COUNT");
	}else{
		return 0;
	}

})(current);

[Adam Flagg]

I would go one step further and also include this before 

record.query();

to only show active users.

record.addQuery("active=true");

[daniziz]

Hi @Aidan Wardman ,

Thanks for the solution!

Any possibility of storing it on the real table field?

My problem here is that the field HC is pretty much useless when filtering or ordering the table (I think because calculating this way is dynamic...)

If I want to use it somehow, I have to download to excel and operate with it...

Regards!

[daniziz]

Hi @Aidan Wardman 

Thanks for the solution!!

Is there any possibility of storing it on table "cmn_department" field?

My problem is that i cant filter or order by HeadCount field because of this dynamic calculation...

I have to export information to excel and operate then...

Regards!