Learning: Calculated Field or Business Rule

kyleenos · ‎09-07-2019

Hello!

I am trying to understand either using a calculated field or business rule as best practices for a use case of mine.

I need to count the number of cases on a particular table (i.e incident) based on the case state (i.e work in progress, open, resolved, closed, etc). I need the count of these cases based on the assignment group the case is assigned to. For example, I need a total count of the cases that are in the state Open, New, Work in Progress, Reopen, Suspended and the assignment group is Group1.

I have choice fields created for state (Open, New, Work in Progress, Reopen, Suspended, Resolved, Closed, Cancelled). I planned on creating 1 new field called "All Open" and either adding a calculation script to the field or business rule to calculate the count of cases where the state is Open, New, Work in Progress, Reopen, Suspended and then I planned on creating 1 more new field called "All Closed" and doing either of the same thing but counting cases where the state is Resolved, Closed, Cancelled.

I plan on reporting on these two new fields "All Open" and "All Closed" as a list view and pivot table so would a calculation script on a field or business rule be more efficient? Based on what I need, I tried taking a shot at the script but need some help making sure it works.

Script based on "All Open" (can modify for "all closed" and change state values):

(function calculateValue(current) {
var categoryCount = 0;
var gr = new GlideAggregate('incident');
gr.addEncodedQuery('stateIN25,10,18,16,24^assignment_groupLIKEGroup1^ORassignmentgroupLIKEGroup2');
gr.addAggregate("COUNT");
gr.query();
while(gr.next()) {
categoryCount = gr.getAggregate("COUNT");
}
return categoryCount;
}(current);

Any help is much appreciated. Thank you!

Ashutosh Munot1 · ‎09-08-2019

Hi,

use business rule instead of calculated field for your use case. As Daniel said calculated field if queried for other table takes more time to show result.

I always like to give example of reassignment count field on incident which is updated by BR. More over only one Business rule and one script can be usefull for you to update both fields ie.e All open and All Close

Use same logic and use you script in business rule.

Condition of business rule: Assignment group changes and is not empty. (BEfore Update BR)

var categoryCountOpen = 0;

var categoryCountClosed = 0;

var gr = new GlideAggregate('incident'); gr.addEncodedQuery('stateIN25,10,18,16,24^assignment_groupLIKEGroup1^ORassignmentgroupLIKEGroup2'); //For All open

gr.addAggregate("COUNT");

gr.query();

if(gr.next()) {//Use if not while

categoryCountOpen = gr.getAggregate("COUNT");

current.u_all_open = categoryCountOpen ;

}

var gr = new GlideAggregate('incident');

gr.addEncodedQuery('stateIN5,15^assignment_groupLIKEGroup1^ORassignmentgroupLIKEGroup2'); //For All Close use your choice values

gr.addAggregate("COUNT");

gr.query();

if(gr.next()) {//Use if not while

categoryCountClosed = gr.getAggregate("COUNT");

current.u_all_open = categoryCountClosed ;

}

Thanks,
Ashutosh

View solution in original post

Ashutosh Munot1 · ‎09-08-2019

Hey,

I suggest the field type to be integer so it always hold integers and not invalid string.

You can use this code but don't forget to put proper table name and query.

Thanks,
Ashutosh

Mark Answer as correct or helpful.

kyleenos · ‎09-08-2019

One last question after creating the new fields and business rule. Will the new fields capture the count of all historical cases created prior to this rule? If not, how can I ensure all historical cases are captured as well?

Ashutosh Munot1 · ‎09-09-2019

HI,

Old Ticket wont have the data. You can write a Background script for this.

Thanks,
Ashutosh

Community Alums · ‎09-08-2019

Hi,

try to avoid calculated value. There is a new way to do this :

ON field definition (but when creating the field otherwise you should enable it via background script or with some util - like SNUtils) check Function Field checkbox. There is full explanation in the documentation how you use that. The benefit is this is run on database directly instead on backed, hence very fast.

Cheers,

Joro