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-03-2015

Hi Shane,

If you need a lookup to a score based on 2 values selected, I would try to do something like the incident priority matrix. Depending on what version of ServiceNow you are on, you would either create a client script or use a data lookup definition Data Lookup and Record Matching Support - ServiceNow Wiki.

After you get your calculated score, I would use an "After" business rule to update the field on the user table after the change task record is saved.

As for deleting the value every 90 days, that would be a scheduled job to run every 90 days with a script that removes the value from the user table. Something similar to this, although it won't take into account anything updated within the 90 days, so you may need to add a date field for when the 90 day value was last set:

var u_usr = new GlideRecord('sys_user');


u_usr.addActiveQuery();


u_usr.query();


while (u_usr.next()){


u_usr.90dayfield = '';


u_usr.update();


}

Does this help?

shane_davis · ‎02-03-2015

Kailey,

Thank you for your reply! I have the data lookup working to where when I pick the Failure Cause and Failure Type, it populates the appropriate integer into the Failure Score. All 3 of these fields are on the change_task table.

My problem is that I am new to scripting and can't get the following to work:

Add the Failure Score (change_task.u_failure_score) to the current score of the change_request.assigned_to.u_failed_change_score integer field. The u_failed_change_score field is an integer on the sys_user table.

Thank you for the help with the 90-day script too!

Shane

kopp820 · ‎02-03-2015

Do you need this score to be dynamically visible? If not, I would highly recommend using a business rule instead of a client script. A business rule would not degrade page performance and it would be a much easier script.

shane_davis · ‎02-03-2015

Kailey,

I have a correction to make. The score in the change_request.assigned_to.u_failed_change_score integer field needs to show the score for the past 90 days and not clear every 90 days. I created a report per below that sums the scores from the change_task.u_failure_score for the past 90 days. I just need the assigned_to's score to show past 90 like the report does. Sorry for the confusion.

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