Client Script help!

[shane_davis]

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]

So you want to update all of the existing change task records every 90 days to calculate the new score based on the user's profile? I'm not fully understanding the requirement.

[shane_davis]

Kailey,

        Below is my goal.   Maybe this will help.

1. My manager opens a Change Task which is assigned to him for a Change Request I am assigned to

2. He chooses the 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/rule should take the Failure Score and add it to the Failed Change Score field (u_failed_change_score) on my user record.   The Failed Change Score should be the value for my last 90 days change tasks.

The report I created is giving these values, but I don't know how to script that for the field on the user table so I can create field styles based off of it.

REPORT:

Type: Bar chart

Table: Change Task

Group by:   Assigned to

Stacked field: None

Aggregation:   Sum > Failure score

Display grid: checked

Conditions:

Created on Last 90 days

[kopp820 · Accepted solution]

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.

[shane_davis]

You are amazing!   I will be very glad when I learn how to create client scripts and business rules from scratch.   Maybe I need a JavaScript for Idiots book.     I always try to make it complicated.   Again, thank you very much for your help.   I really appreciate your time.

[kopp820]

Hi again,

After thinking about this some more, the daily scheduled job only needs to subtract the 91st day's total score per user, since this script will run every day. The catalog task's business rule will take care of the addition aspect when a new score is generated.

Scheduled job for subtraction, run at some hour in the night daily:

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