Total Cases for each HR Service

[RadZz]

Hello team,

I have a requirement to get total cases count in a field : Total cases per each HR service.

If we open a particular HR Service then the field "Total Cases" should show the count of HR cases created for this particular HR service.

How can we achieve this?

TIA:)

@Ankur Bawiskar 

[Ankur Bawiskar]

@RadZz 

you will have to create a custom field on HR service table and have a scheduled job which runs daily and checks how many cases got created for that HR service and update that field

If my response helped please mark it correct and close the thread so that it benefits future readers.

[RadZz]

// Define a scheduled job that counts the total number of HR cases for each HR service and updates the u_total_case_count field in the hr_service table

 var HRCasesCountJob = Class.create();

HRCasesCountJob.prototype = Object.extendsObject(AbstractScheduledJob, { run: function() {

// Query the sn_hr_core_case table to count the number of cases for each HR service

var hrServiceGr = new GlideAggregate('sn_hr_core_case');

 hrServiceGr.addAggregate('COUNT');

hrServiceGr.addQuery('active', true); // Consider only active HR cases

 hrServiceGr.groupBy('hr_service'); // Group by HR service

 hrServiceGr.query();

// Update the u_total_case_count field in the hr_service table with the respective counts

 while (hrServiceGr.next()) {

var hrService = hrServiceGr.hr_service.toString();

var caseCount = parseInt(hrServiceGr.getAggregate('COUNT'), 10);

var hrServiceUpdate = new GlideRecord('hr_service');

if (hrServiceUpdate.get('sys_id', hrService)) {

hrServiceUpdate.u_total_case_count = caseCount;

hrServiceUpdate.update();

 }

}

},

type: 'HRCasesCountJob'

 });

I tried this and seems wrong some way.. Not working. Anything wrong with this?