Hi
Here is my proposal. I created a new table as you described to test out:
I created a new Business rule on the Incident table:
I added a script doing the following steps:
a) find all incidents, that are "closed_by" with the "current" user.
b) create / insert a record in your scoring table
c) find all incidents, that are "closed_by" the user in the "previous" user (if there was one user)
d) create / insert a record in your scoring table
NOte: you need to take steps c) and d), otherwise you miss to correct the user who was captured in the field "closed_by" BEFORE any change (if it was NOT empty before).
Here is the script for copy and paste:
(function executeRule(current, previous /*null when async*/) {
var closedby = current.closed_by;
var closedcount = 0;
// find the number of incidents this user closed and count them
var grinc = new GlideRecord('incident');
grinc.addQuery('closed_by', closedby);
grinc.query();
while(grinc.next()) {
closedcount = closedcount + 1;
}
gs.addInfoMessage("anzahl" + closedcount);
// create / update numbers record in separate table
var grtotal = new GlideRecord('u_closed_incidents');
grtotal.addQuery('u_name', closedby);
grtotal.query();
if (grtotal.next()) {
// a record for the total already exists => UPDATE the existing record
grtotal.u_numbers = closedcount;
grtotal.update();
} else {
// no record for the total exists => INSERT a new record
grtotal.newRecord();
grtotal.u_name = closedby;
grtotal.u_numbers = closedcount;
grtotal.insert();
}
// REMEMBER !!! You need to also update the "previous" user, if the
// Incident "closed_by" filed CHANGES from one user to another
closedby = previous.closed_by;
if (closedby.length == 0) {
return;
}
closedcount = 0;
// find the number of incidents this user closed and count them
grinc = new GlideRecord('incident');
grinc.addQuery('closed_by', closedby);
grinc.query();
while(grinc.next()) {
closedcount = closedcount + 1;
}
gs.addInfoMessage("anzahl" + closedcount);
// create / update numbers record in separate table
grtotal = new GlideRecord('u_closed_incidents');
grtotal.addQuery('u_name', closedby);
grtotal.query();
if (grtotal.next()) {
// a record for the total already exists => UPDATE the existing record
grtotal.u_numbers = closedcount;
grtotal.update();
} else {
// no record for the total exists => INSERT a new record
grtotal.newRecord();
grtotal.u_name = closedby;
grtotal.u_numbers = closedcount;
grtotal.insert();
}
})(current, previous);
Feel free to optimze the code regarding your concrete requirements.
I hope that answers your question, and you can set as correct.
Just let me know.
BR
Dirk
