Your achievements

Level 1

0% to

Level 2

Tip /
Sign in

Sign in to Community

to gain points, level up, and earn exciting badges like the new
BedrockMission!

Learn more

View all

Sign in to view all badges

SOLVED

Javascript to sum cells in a column

Brian3_14
Level 3
Level 3

Hi all -

I've got an expanding table (rows can be added) and I'm trying to use the footer row of the column to compute the average of cells that don't have a value of 0.

Form Calc is usually great, but in this instance, I need to divide the sum by the number of cells that are not zero and I'm not sure how to do that via Form Calc.

Successfully used javascript to both identify the total number of instances and identify the number of instances that are zero, so the denominator part of the equation is fine. (# of instances - # of zeros = denominator.)

Now it's just the numerator. It's got to be a loop, right?

Table is called: table1

Row is called: data

cell is called: eValue

Any thoughts? Or more efficient methods? Thanks so much.

B

1 Accepted Solution
radzmar
Correct answer by
Level 10
Level 10

Hi,

a solution in FormCalc could look this way:


form1.#subform[0].table1.footer.sum::calculate - (FormCalc, client)


var c = 0


var s = 0


for i = 0 upto (_data.count - 1) do


  if (data[i].eValue gt 0) then


  c = Sum(c, 1)


  s = Sum(s, data[i].eValue)


  endif


endfor



if (s gt 0 and c gt 0) then


  $ = Round(s / c)


else


  $ = 0


endif


Put this scriot into the calculate event of the cell of the footer row.

View solution in original post

6 Replies
radzmar
Correct answer by
Level 10
Level 10

Hi,

a solution in FormCalc could look this way:


form1.#subform[0].table1.footer.sum::calculate - (FormCalc, client)


var c = 0


var s = 0


for i = 0 upto (_data.count - 1) do


  if (data[i].eValue gt 0) then


  c = Sum(c, 1)


  s = Sum(s, data[i].eValue)


  endif


endfor



if (s gt 0 and c gt 0) then


  $ = Round(s / c)


else


  $ = 0


endif


Put this scriot into the calculate event of the cell of the footer row.

View solution in original post

jasotastic81
Level 7
Level 7

Something like this?


var num = 0, den = 0, rowList = xfa.resolveNodes("Table1.Row1[*]");


 


for (var i = 0; i< rowList.length; i++){


  if (!rowList.item(i).nfNumber.isNull && parseFloat(rowList.item(i).nfNumber.rawValue) != 0) {


  num += parseFloat(rowList.item(i).nfNumber.rawValue);


  den++;


}


}


if (den !=0) this.rawValue = parseFloat(num)/parseInt(den);


else this.rawValue = 0;


Brian3_14
Level 3
Level 3

Radz,

Worked like a charm.  Thanks much - and I won't tell you the embarrassingly long way I was going.

Any recommended reading for LD and coding?

Thanks again,

Brian

Brian3_14
Level 3
Level 3

Jasotastic81,

Thanks for the reply.  I'll try working with it. What does the "nfNumber" refer to?

Brian

jasotastic81
Level 7
Level 7

That would be the name of the field. It would have been eValue in your example.

Brian3_14
Level 3
Level 3

Copy that. Think my brain's fried from living between Form Calc and Javascript on this.

Thanks much again.

B