How can you create a counter to count the number of children a Major Case has?

zag · ‎02-27-2020

I created a field called Child Count in the MIM scope.

Basically I want a business rule that add's one every time a Child is Added or Subtracts one if a child is removed.

Basically I want the Child Case related list counted. Any ideas?

Brian Lancaster · ‎02-27-2020

I tested this in my PDI. It is an after insert/update business rule which is one of the occasions where you can have an update() in the BR. In the when to run tab I have my condition as parent changes this way it can update the count if you remove a child case because it was added by accident. Then I have the following code.

(function executeRule(current, previous /*null when async*/ ) {

    // Add your code here
	var sysID = '';
	if (current.getDisplayValue('parent') == ''){ //update count if case removed
		sysID = previous.getValue('parent');
	}
	else { //update count if case added
		sysID = current.getValue('parent');
	}
	
    var gr = new GlideRecord('sn_customerservice_case');
    gr.addQuery('sys_id', sysID);
    gr.query();
    if (gr.next()) {
        gr.u_child_count = getCount(sysID);//get the count thought the below function
		gr.update();
    }

	//function to get count thought GlideAggregate as using glide record to count can cause pour performance.
    function getCount(sysID) {
        var count = new GlideAggregate('sn_customerservice_case');
        count.addQuery('parent', sysID);
        count.addAggregate('COUNT');
        count.query();
        if (count.next()) {
            return count.getAggregate('COUNT');
        }
    }

})(current, previous);

View solution in original post

Brian Lancaster · ‎02-28-2020

You cannot use a business rule for historical data. We have to query the table in scripts - background. I'm trying to test a script and will come back to you in a few minutes.

Brian Lancaster · ‎02-28-2020

In the left Nav type scripts and then you should see Scripts - Backgroud as an option. click on it and try this code. Please make sure you test this out in a Non-Prod environment first.

var gr = new GlideRecord('sn_customerservice_case');
gr.addEncodedQuery('parentISNOTEMPTY');
gr.query();
while (gr.next()){
   var parent = new GlideRecord('sn_customerservice_case');
   parent.addQuery('sys_id', gr.parent);
   parent.query()
   if (parent.next()){
      var sysID = parent.sys_id;
      parent.u_child_count = getCount(sysID);
      parent.update()
   }
   
}
function getCount(sysID) {
   var count = new GlideAggregate('sn_customerservice_case');
   count.addQuery('parent', sysID);
   count.addAggregate('COUNT');
   count.query();
   if (count.next()) {
       return count.getAggregate('COUNT');
   }
}