Trying to figure sum calculation for variable values

[Cheri M]

I am trying to build a catalog item that is based on a spreadsheet that used the =sum calculation.   I've found a few ideas out there but nothing that will work for me.   We'd like it to be just for this catalog item so not a business rule.   Currently I have 5 columns, 1^st   year — 5^th year and we'd like each column to show the total at the end (a total cost of ownership).   Please see screenshot.   I got the fields figured out, now I just need a formula.   We'd like the user to be able to see the change as they did with the spreadsheet and not have the sum just sent in the form. I will make the TCO (bottom) row read-only if I get this all figured out.

Thank you!

[salvadormarchan]

Since these variables are from the catalog item; these fields are 'String' data type. So, I will do the following:

• Configure in a Catalog Client Script onSubmit (if you want to use onChange, you have cloumn x row numbers of scripts to code)

• In this script, convert each value of the variables to a number and the add them by setting the value to you subtotal below. It's something like this:

function onSubmit() {

var f1 = g_form.getValue('field1');

var f2 = g_form.getValue('field2');

var f3 = g_form.getValue('field3');

var f4 = g_form.getValue('field4');

var f5 = g_form.getValue('field5');

var f6 = g_form.getValue('field6');

var f7 = g_form.getValue('field7');

var f8 = g_form.getValue('field8');

var f9 = g_form.getValue('field9');

var t1 = Number(f1) + Number(f2) + Number(f3) + Number(f4) + Number(f5) + Number(f6) + Number(f7) + Number(f8) + Number(f9);

g_form.setValue('total1', t1);

// repeat script above for the other columns

}

Your RITM will have the subtotal...I guess you can use a loop for the fields if they are in a certain pattern. Or, you can be creative. I hope I deliver my idea.

BTW, how did you manage to have 5 columns on the Catalog Item?

I hope this helps.

Dor

[DrewW]

If you would like to see this as they enter data and before it is saved then you are going to have to use a client script.   You could do this by adding a box for total and then have each row have a prefix for the name of the var be the same, like "row_#_year_y" where the # = the row number you pass to the function and y = the year number.   Then you can do something like

function total(rowNumber){

         var total = 0;

         for(int y = 1; y <= 5; y++){

                   total += new Number(g_form.getValue("row_" + rowNumber + "_year_" + y))

         }

         g_form.setValue("row_" + rowNumber + "_total", total);

}

then you just add a onkeypress event function to call this with the row number on each of the boxes.

By the way how did you go about creating the 5 columns of fields?

[Cheri M]

They aren't actually columns and rows, so I will probably need use variable names.

I got 5 columns by embedding containers.   For 5 it's:

StartContainer

StartContainer

Variable-Single Line Text

Variable-Single Line Text

EndContainer

StartContainer

StartContainer

Variable-Single Line Text

Variable-Single Line Text

EndContainer

Variable-Single Line Text

EndContainer

EndContainer

[DrewW]

You did not give the vars names?   I would just name the vars how I suggested based on how they display and then add one that is read only for the total.

You could also use a UI macro for this also.