Client Script help!

shane_davis · ‎02-02-2015

I need some help creating a script for the process below. Unfortunately, my limited scripting abilities isn't getting me the required results. Any help is appreciated!

My full process is:

1. I open a Change Task which is assigned to me.

2. I choose my Failure Type and Failure Cause choices which puts an integer in the Failure Score (u_failure_score) field.

All 3 fields are on the change_task table

3. The script should take the Failure Score and add it to the Failed Change Score field (u_failed_change_score) on the User table for the user assigned to the parent Change Request.

In addition, I need to delete the value of u_failed_change_score every 91 days because we only want to keep 90 days history in this field.

Below is code I've tried to create, but it's obviously not all I need...

function onChange(control, oldValue, newValue, isLoading, isTemplate) {

 //Get value of u_failed_change_score on the sys_user table, add that to the u_failure_score on the change_task table, and then overwrite the current user's score with the new score.
 var totalusrscore = g_form.getReference('sys_user');
 // var f2 = g_form.getIntValue('totalusrscore.u_failed_change_score'); //Field on the sys_user table
 var f1 = g_form.getIntValue('u_failure_score'); //Field on the change_task table
 var total = f1 + totalusrscore.u_failed_change_score; //Add the 2 fields together to get the sum

 g_form.setValue('f2', total); //set the score on the sys_user table to the new score
}

kopp820 · ‎02-03-2015

I would create a business rule to run "After" the record has updated. This will only add the current updated score to the existing score on their user record. See the scheduled job below for how to remove anything previous to 90 days.

Here is the script:

(if you're on ServiceNow Eureka, check the Advanced checkbox)

Insert = True

Update = True

When to run = After

Condition: current.u_failure_score.changes()

Script:

var u_usr = new GlideRecord('sys_user'); 


u_usr.addQuery('sys_id',current.change_request.assigned_to); 


u_usr.query(); 


if (u_usr.next()){ 


u_usr.u_failed_change_score += current.u_failure_score; 


u_usr.update(); 


}

Also since you want to remove anything past 90 days old, I believe you should have a daily scheduled job to recalculate the score that runs this script:

var u_usr = new GlideRecord('sys_user');


var u_cTask = new GlideRecord('change_task');


var u_sum = new GlideAggregate('change_task');




//Find all change tasks created in the last 90 days


u_cTask.addEncodedQuery('sys_created_onONLast 90 days@javascript:gs.daysAgoStart(90)@javascript:gs.daysAgoEnd(0)');


u_cTask.query();


while (u_cTask.next()){


var u_at = u_cTask.change_request.assigned_to;





//aggregate the sum of the failure scores grouped by assigned to


u_sum.addAggregate('SUM', 'u_cTask.u_failure_score');


u_sum.groupBy(u_at);


u_sum.query();


while (u_sum.next()){


var total = u_sum.getAggregate('SUM','u_cTask.u_failure_code');





//Here is where you should set that value back to the user record by updating the sys_user table.


//I'm not 100% sure of the code here, but add gs.log statements in the script above to see what


//output you are getting.





//You need something like for each assigned to user, set the u_failed_change_score to be the 


//sum aggregate we got from the above query.

View solution in original post

kopp820 · ‎02-05-2015

Note that you can use previous.u_failure_score to get the value before the update.

So you'll need to evaluate if the previous value was empty. If it was, then just add. If it wasn't, you'll need to subtract the previous value, then add the current value.

shane_davis · ‎02-05-2015

I got sidetracked today with other tasks. So now I have 2 directions that I can go now with the design. The first is the way we've been thinking. The second is to calculate the score when the Change Request is closed.

I think the second option will actually be better if there's a good way to pull the failure score from the CHG's PIR Task and update the user record. I'm not sure if this can be a script activity within the workflow OR the business rule run when the CHG is closed. The first option seems more difficult because we have a minimum of 3 change tasks that have the same Successful and Unsuccessful buttons (business rule is currently working based off of correct task) AND we have workflow rollbacks on at least 2 of those task which could impact the score.

I would like your opinion before I move forward, please!

kopp820 · ‎02-05-2015

I would keep it as a business rule. your condition could be

current.u_failure_score.changes() && current.active=='false' && action.getActionName == 'unsuccessful'

shane_davis · ‎02-06-2015

I will keep it as a business rule which runs at CHG closure. I'll be busy working on this today so here goes nothing! Thank you very much for all of your help.

shane_davis · ‎02-18-2015

Kailey - I am testing your scheduled job script, but am not getting the results I expected. Please let me know if this should work.

- The current failed change score on my user record is 35.

- I searched for all of the tasks (changes assigned to me) with a failure score in the last 7 days and the sum of those scores is 48.

- I modified line 4 of your query below to be......

u_cTask.addEncodedQuery('sys_created_onRELATIVEGE@dayofweek@ago@8^sys_created_onRELATIVELT@dayofweek@ago@7');

- I executed the script thinking my new failed change score would be 48, but it did not change.

var u_cTask = new GlideRecord('change_task');
var u_usr = new GlideRecord('sys_user');


// use the encoded query to find change tasks opened on or after 91 days, but before 90 days ago
u_cTask.addEncodedQuery('sys_created_onRELATIVEGE@dayofweek@ago@91^sys_created_onRELATIVELT@dayofweek@ago@90');
u_cTask.query();
// iterate through each change task from 91 days ago
while (u_cTask.next()){
 //find the user record for the change's assigned to user
 u_usr.addQuery('sys_id',u_cTask.change_request.assigned_to);
 u_usr.query();
 if (u_usr.next()){
   u_usr.u_u_failed_change_score = u_usr.u_failed_change_score - u_cTask.u_failure_score;
   // could also try this instead of the above line>> u_usr.u_u_failed_change_score -= u_usr.u_failed_change_score;
   u_usr.update();
 } //close the user query
} //close the change task query