Business Rule for calculating field values

[snow_wizard]

Hello Guys,

I have added additional column within 'sys_user' table: u_incident_in_progress. It should show number of incidents assigned to user with state 'In Progress' ('2').

The idea is that it should appear only on lookup list for 'Assigned to' field on 'incident' form. I have successfully achieve this goal by putting simple code in calculation section within dictionary entry for newly created field:

var gr = new GlideRecord('incident');
gr.addQuery('assgined_to', current.sys_id);
gr.addQuery('state', -2);
gr.query();
current.u_incidents_assigned = gr.getRowCount();

However I have problem with achieving the same but with the use of Business Rule.

So far I have created something like this:

(function executeRule(current, previous /*null when async*/) {
var count = new GlideAggregate('incident');       
   count.addQuery('asigned_to',current.assigned_to);       
   count.addQuery('state', '2');       
   count.addAggregate('COUNT');       
   count.query();       
   var incidents = 0;       
   if (count.next()){       
   incidents = count.getAggregate('COUNT');       
   }       
   var user = new GlideRecord('sys_user');       
   user.addQuery('sys_id',current.assigned_to.sys_id);       
   user.query();       
   user.setValue('u_incidents_assigned',incidents);   
   user.update(); 
})(current, previous);

BR is set onAfter and on 'Incident' table. It's not showing the counted values. Any feedback is most welcome. Thanks in advance.

[Harsh Vardhan]

line number 3.

1.   count.addQuery('asigned_to',current.assigned_to);      

it should be assigned_to

[Harsh Vardhan]

Updated script:

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

// Add your code here

var count = new GlideAggregate('incident');

count.addQuery('assigned_to',current.assigned_to);

count.addQuery('state', '2');

count.addAggregate('COUNT');

count.query();

var incidents = 0;

if (count.next()){

incidents = count.getAggregate('COUNT');

gs.log('count is'+ incidents);

}

var user = new GlideRecord('sys_user');

user.addQuery('user_name',current.assigned_to.user_name);

user.query();

while(user.next())

{

user.setValue('u_location',incidents);   Please change the field name. i tested with another field name

user.setWorkflow(false);

user.update();

}

})(current, previous);

[Harsh Vardhan]

Hi

Any update ?

if you have any further question then please let me know.

if your queries has resolved please mark the answer correct and close this thread.

How To Mark Answers Correct From Community Inbox

[snow_wizard · Accepted solution]

Hi Harsh,

Thanks for the feedback. Your code works but I have ended up with something like this:

SI:

var GV_BAP_Task14 = Class.create();
GV_BAP_Task14.prototype = {
       initialize: function() {
       },


reCalculate:function(userID){
//This updates assigned_tickets and tickets_in_progress for a user; userID is sys_id of a user record
var incidentGA = new GlideAggregate("incident");
var userGR = new GlideRecord("sys_user");
var ticketsAssigned=0;
var ticketsInProgress=0;
incidentGA.addQuery('assigned_to',userID);
incidentGA.addAggregate('COUNT',"state");
incidentGA.groupBy("state");
incidentGA.query();
while(incidentGA.next()){
if(incidentGA.state =="2"){
//Tickets In Progress
ticketsInProgress = incidentGA.getAggregate('COUNT',"state");
}
if(incidentGA.state =="-1"){
//Tickets Assigned
ticketsAssigned = incidentGA.getAggregate('COUNT',"state");
}
}
if(userGR.get(userID)){
userGR.u_incidents_assigned = ticketsAssigned;
userGR.u_incident_in_progress = ticketsInProgress;
userGR.update();
}

},

       type: 'GV_BAP_Task14'
};

and BR:

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

// Add your code here
var countIncs = new GV_BAP_Task14();   
countIncs.reCalculate(current.assigned_to);
countIncs.reCalculate(previous.assigned_to);

})(current, previous);

So without looping in SI and passing just sys_id as function argument instead of whole gliderecord.