Need Bg script to update the calculated field

[Hareesha]

I have a calculated choice field , where i am calculating number of days between start and end date. Based on it i am choosing the choice field. i did some mistake in code and choice values for all records have calculated wrong. Now i modified code of calculated choice field. Now i want to update records. Please help me the best way to update all records through bg script.

Calculated field script:

(function calculatedFieldValue(current) {
var start = new GlideDateTime(current.u_start_work_date.toString());
var end = new GlideDateTime(current.u_end_work_date.toString());
if(start=='' || end==''){
return ;}
else{
var dur = GlideDateTime.subtract(start, end);
var day=dur.getRoundedDayPart();
if(day>=0 && day<=2){

return 1;
}
if(day>2 && day<=5){
return 2;}
if(day>5 && day<=10)
{
return 3;
}
if(day>10 && day<=15)
{
return 4;
}
if(day>=16){

return 5;}}
})(current);

[Roshan Tiwari]

So you want to update all the existing record. Use Fix Script to update all records. 

[Bert_c1]

https://developer.servicenow.com/dev.do#!/learn/courses/utah#scripting-in-servicenow

Try something like the following

var tableToUpdate = new GlideRecord('table_name');
tableToUpdate.query();
while (tableToUpdate.next()) {
	var value = calculatedFieldValue(tabletoUpdate);
	gs.info ("New value = " + value);
	// update calculated field with new value, if valid
	if(value >= 0) {
		tableToUpdate.calculated_field = value;
		tableToUpdate.update();
	}
}

function calculatedFieldValue(current) {
	var start = new GlideDateTime(current.u_start_work_date.toString());
	var end = new GlideDateTime(current.u_end_work_date.toString());
	if(start=='' || end==''){
		return -1;
	}
	else{
		var dur = GlideDateTime.subtract(start, end);
		var day=dur.getRoundedDayPart();
		if(day>=0 && day<=2){
		return 1;
		}
		if(day>2 && day<=5){
		return 2;
		}
		if(day>5 && day<=10){
		return 3;
		}
		if(day>10 && day<=15){
		return 4;
		}
		if(day>=16){
		return 5;
		}
	}
};

use table name of desired table and desired field name to be updated.

[Hareesha]

Hi Bert,Thanks for the code. I have some records which was calculated wrongly previously where start and end dates are empty. how can i replace those records with none option instead of existing choice value.

[Bert_c1]

Hi,

You can use a list view of the table records. I have used the following for the change_request table:

https://[instance.service-now.com/change_request_list.do?sysparm_query=start_dateISEMPTY%5EORend_dat...

and set desired values.

Or, use the query in a script and update the fields as desired.

Or, you can also add logic to check for null and return an indication to set the new field to 'none'. If the field to be set is a string. seems it may be an integer.  I have a check for that above, where -1 is returned.