question Need to calculate the numeric sum values from multiple fields in Developer forum

Need to calculate the numeric sum values from multiple fields

Raul Luna1 — Thu, 16 Apr 2020 19:48:24 GMT

I have four fields that have dropdown values of 1, 2 ,3. I need to know how to add all the values that were selected in the fields and have the total populate in another field. Would I have to modify the 'Overall Reseach Score' dictionary item and write a script in the Calculated Value tab?

Fields:

Strategic Alignment (dropdown selections 1-3)
Community Support (dropdown selections 1-3)
Institutional Risk (dropdown selections 1-3)
Improve Capabilities (dropdown selections 1-3)
Overall Research Score (Totals field)

So if each of the selected values were 1. Then the 'Overall Research Score' would be 4.

Re: Need to calculate the numeric sum values from multiple fields

rajneeshbaranwa — Thu, 16 Apr 2020 20:35:50 GMT

Calculated fields are not good from performance point of view. Calculated field calculates the value each time a record is read.

Rather an insert/update business rule are better option. I believe in this case you can use before business rule on the table.

Please mark my answer correct/helpful, If it helped you.

Re: Need to calculate the numeric sum values from multiple fields

Akash Gurram1 — Thu, 16 Apr 2020 20:39:48 GMT

Hi Raul,

If you want it to be a client side operation, if performance is not the issue and if you do not want to use business rules, you can do this.
You can write 4 client script on the table, 1 onLoad and 3 onChange for each of the fields.
Then you can use the below code to include in each of the scripts.
Please replace the values('field1','field2'.....) with the necessary column or field names in the table.

function onLoad() { //Similary for onChange
   //Type appropriate comment here, and begin script below
	
	var strategicAlignment =  g_form.getValue('field1');
	var communitySupport =  g_form.getValue('field2');
	var institutionalRisk =  g_form.getValue('field3');
	var improveCapabilities = g_form.getValue('field4');
	var researchScore = parseInt(strategicAlignment) + parseInt(communitySupport) + parseInt(institutionalRisk) + parseInt(improveCapabilities);
	g_form.setValue('total_field', researchScore );
	
}

Please mark my answer correct and helpful if it works.

Thanks,
-Akash Gurram

Re: Need to calculate the numeric sum values from multiple fields

Raul Luna1 — Fri, 17 Apr 2020 12:32:01 GMT

Hi rajneeshbaranwal,

How would the business rule look like if i wanted to use that option?

Re: Need to calculate the numeric sum values from multiple fields

Raul Luna1 — Fri, 17 Apr 2020 12:45:07 GMT

Hi Akash,

So i created onChange client scripts for each of the following fields like you recommended: Strategic alignment, Community support, Institutional risk, Improve capabilities.

I also created an onLoad script with the same code. When i load the page i'm getting errors on each of the fields. Sorry i'm not coder so maybe I misinterpreted something in the code.

Re: Need to calculate the numeric sum values from multiple fields

Akash Gurram1 — Fri, 17 Apr 2020 14:02:59 GMT

Hi Raul,

I see a mistake in the var researchScore part of the code. You should be using the declared variables in the parseInt() function.

Please replace it with the following

var researchScore = parseInt(strategicAlignment) + parseInt(communitySupport) + parseInt(institutionalRisk) + parseInt(improveCapabilities);

Hope that solves the problem.

Re: Need to calculate the numeric sum values from multiple fields

rajneeshbaranwa — Fri, 17 Apr 2020 15:27:34 GMT

Table = "Your table Name";

When = Before;

Both Insert and Update will be true;

Script will be as below. Please note business rules apply whenever we make changes to the table, It could be using REST API, Import Set , Form update or list update while client script applies only on form changes.

I always prefer business rules for this kind of requirement.

var value1 = 0 ;

value2 = 0;

value3=0;

value4=0;

if (current.field1)

{value1 = current.getValue(field1);}

if (current.field2)

{value2 = current.getValue(field2);}

if (current.field3)

{value3 = current.getValue(field3);}

if (Current.field4)

{value4 = current.getValue(field3);}

current.Overall Research Score = parseInt(value1) + parseInt(value2) + parseInt(value3) + parseInt(value4) +

Re: Need to calculate the numeric sum values from multiple fields

Akash Gurram1 — Mon, 20 Apr 2020 14:53:33 GMT

Hi Raul,

Did the above answer work for you?

Please mark my solution correct and helpful if it did the work for you.

Thanks,
Akash Gurram