Addition of Fields

[Atik]

Hi,

I am working on some PPM kind of project, the requirement is something

there are three fields 'u_total' , 'u_mdc' and 'u_tpc'

'u_total' This field should be the sum of all the records of fields ('u_mdc' and 'u_tpc' ) that are available in the table

Anyone can help with this,

Note: All three fields are decimal fields. 

Thanks!

[Sonam Tiwari · Accepted solution]

@Atik 

Try below in that case

var name_of_your_table = 'incident'// Replace 'your_table_name' with the actual table name

var gr = new GlideRecord(name_of_your_table);

gr.query();

var totalSum = 0;// always a good idea to initialize

// Loop through all records and calculate the sum

while (gr.next()) {

  var u_mdc = gr.u_test1;//your mdc field

  var u_tpc = gr.u_test2;//your tpc field

 //get the sum

  totalSum += (u_mdc + u_tpc);

}

var totalRecord = new GlideRecord('incident');

totalRecord.get('ac75cfad478071101c8053ebd36d43b1'); // replace if any filter is required

//update records with your total

totalRecord.u_test3 = totalSum;// test 3 is your total

totalRecord.update();

If you might need to this for new records then you can run a scheduled job with the desired frequency or tweak this into a BR.

[Arun_S1]

@Atik You can navigate to the respective screen and right click on the grey area in the screen and select configure--> Business Rule

in the screen that appears, click on the new button to create a new business rule and enter the below details.

Name:- Calculate Total

Advanced:- True

When:- before

Insert & update:- True

Add the below code in the Advanced tab and submit the business rule.

(function executeRule(current, previous /*null when async*/) {

	// Add your code here
	current.u_total=current.u_mdc+current.u_tpc;

})(current, previous);

After the creation of the business rule the total should be calculated automatically when the record is saved.

Please mark the appropriate response as correct answer and helpful.

Thanks!!

[Atik]

Hi @Arun_S1 ,

It should also include the previously available record's value of respective fields in the table.

[Arun_S1 · Accepted solution]

@Atik You will have write a script to update the records that were created earlier.

Please use the code below to run in the background script/ Fix script, this will retrieve all the records which has values for MDC or TPC and update the total field with the sum of MDC & TPC.

In the script, please change the table_name in the line number 1 to the actual table name.

var projects=new GlideRecord('table_name');
//please replace the table_name with the actual table name.
projects.addEncodedQuery('u_mdcISNOTEMPTY^ORu_tpcISNOTEMPTY');
projects.query();
while(projects.next()){
projects.u_total=projects.u_mdc+projects.u_tpc;
project.update();
}

Please mark the appropriate response as correct answer and helpful.

Thanks!!

[Sonam Tiwari · Accepted solution]

@Atik 

Try below in that case

var name_of_your_table = 'incident'// Replace 'your_table_name' with the actual table name

var gr = new GlideRecord(name_of_your_table);

gr.query();

var totalSum = 0;// always a good idea to initialize

// Loop through all records and calculate the sum

while (gr.next()) {

  var u_mdc = gr.u_test1;//your mdc field

  var u_tpc = gr.u_test2;//your tpc field

 //get the sum

  totalSum += (u_mdc + u_tpc);

}

var totalRecord = new GlideRecord('incident');

totalRecord.get('ac75cfad478071101c8053ebd36d43b1'); // replace if any filter is required

//update records with your total

totalRecord.u_test3 = totalSum;// test 3 is your total

totalRecord.update();

If you might need to this for new records then you can run a scheduled job with the desired frequency or tweak this into a BR.