How to enter related incident count of a problem into a field in the problem form?

peterwoodward · ‎05-22-2020

On the Problem form I have added a field called Related Incident Count. I need to populate this field with the number incidents that are related to the problem for all open problems. This way the field can be a column included in a report that can either be printed or appear in a widget on a homepage.

I found a business rule that will display the number of related incidents in the Related Incident Count field. But because it is a display business rule, it only appears there when the problem is opened. Because of this, the field does not actually contain any data, so nothing appears in the column of the report.

How do I change this display BR to actually enter the number into the field?

Here is the script:

On the Problem table

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

// Add your code here
var gr = new GlideRecord("incident");
gr.addQuery('problem_id', "=", current.sys_id);
gr.query();
if (gr.next()) {
}
//custom string field to store count
current.u_related_incident_count= gr.getRowCount();

})(current, previous);

I have attached a screenshot of the BR. Any assistance with this is greatly appreciated.

DScroggins · ‎05-22-2020

Hello,

Few things it is not best practice to perform record updating in display business rules. Also when you simply need the count of records using GlideAggregate is best practice and provides better performance as you do not need to return all the records just to get a count. This is what getRecordCount() does. So one thing you could do is add an After Business Rule to the incident table when Problem field changes. The BR would look like:

The advanced script would count all incidents related to the same problem then update the problem record field. If the problem field changes to blank the script will use the previous value:

(function executeRule(current, previous) {
    var incCount = 0;
    var prbSysID = (!current.problem_id.nil())? current.problem_id : previous.problem_id;
	
    var ga = new GlideAggregate("incident");
    ga.addAggregate('COUNT');
    ga.addQuery('problem_id', prbSysID);  
    ga.query();
    if (ga.next()) {
        incCount = ga.getAggregate('COUNT');
    }

    var problemGR = new GlideRecord('problem');
	problemGR.get(prbSysID);
        problemGR.setValue('u_related_incident_count', incCount);
	problemGR.update();
    

})(current, previous);

Hope this helps.

--David

View solution in original post

peterwoodward · ‎05-22-2020

Wow. This works perfectly. Thank you so very much for your help.

peterwoodward · ‎05-22-2020

It works perfectly except for one thing I just discovered: if the problem_id field is cleared and the incident is saved, the field is not reflecting this decrement to the number. How do I account for this?

DScroggins · ‎05-22-2020

Odd. When I try the same thing the count is properly updated. If you add the following log statement do you see the count after you add / remove the Problem reference?

    var incCount = 0;
    var prbSysID = (!current.problem_id.nil())? current.problem_id : previous.problem_id;
    gs.info('Problem Incident Count Sys ID: ' + prbSysID);
    var ga = new GlideAggregate("incident");
    ga.addAggregate('COUNT');
    ga.addQuery('problem_id', prbSysID);  
    ga.query();
    if (ga.next()) {
        incCount = ga.getAggregate('COUNT');
	    gs.info('Problem Incident Count #: ' + incCount);
    }

    var problemGR = new GlideRecord('problem');
	    problemGR.get(prbSysID);
        problemGR.setValue('u_related_incident_count', incCount);
	    problemGR.update();