Learning: Calculated Field or Business Rule

[kyleenos]

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!

[Kajal Goti]

Hi, You can do this with the help of report , You can also schedule report as per your requirement also count the all incident as per your condition Mark as Correct and Helpful if you find any help worthy.

[Dan Martinez]

Bear in mind calculated fields have some cons:

• They can impact performance:
• • Especially if the query is performed on a table with too many records
  • Or if the calculated field is displayed on a list by default
• When ordering a list of items based on a calculated field, it won't display properly. (It tends not to order anything)

However, in some cases they are recommended, especially if the calculation is performed based on the data contained in the same record, as then "current" will give us that information without having to query the database.

Business rules may cause some issues as well. If not tested properly they may not display the actual numbers. ie: Say your BR triggers on update but you've got a transform map that ignores business rules, that may cause the field to be desync with reality.

In your case, I'd perform a few tests to see how your query behaves. It should be kind of fast as you're using the GlideAggregate class, but just in case. If performance isn't impacted then it sounds like a decent approach.

[Ashutosh Munot1 · Accepted solution]

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

[kyleenos]

Thank you Ashutosh! This is so helpful. So I will create the 2 new fields and include 1 business rule to accommodate this.

I planned on using string as the field type for both these fields. Is that fine or would you recommend another field type? They're going to be read only and not on the form.

Based on your revisions I should be able to copy/paste the code into 1 business rule as suggested? I will test thoroughly as well.

Best,

Kyle