Get column data sum from FormIO table element

Question

I'm trying to get the column number sum in a table element from FormIO. At the moment I'm doing this with this script:

if(data['personesAtesesNoBinari17'] >= 0 || 
  data['personesAtesesNoBinari1631'] >= 0 || 
  data['personesAtesesNoBinari3066'] >= 0 || 
  data['personesAtesesNoBinari67'] >= 0){
  
if(data['personesAtesesNoBinari17'] === undefined){
    data['personesAtesesNoBinari17'] = 0;
  }
  if(data['personesAtesesNoBinari1631'] === undefined){
    data['personesAtesesNoBinari1631'] = 0;
  }
  if(data['personesAtesesNoBinari3066'] === undefined){
    data['personesAtesesNoBinari3066'] = 0;
  }
  if(data['personesAtesesNoBinari67'] === undefined){
    data['personesAtesesNoBinari67'] = 0;
  }
  data['personesAtesesNoBinariTotal2'] = 
    data['personesAtesesNoBinari17'] +
    data['personesAtesesNoBinari1631'] + 
    data['personesAtesesNoBinari3066'] + 
    data['personesAtesesNoBinari67'];

}else{
  data['personesAtesesNoBinariTotal2'] = 0;
}

Each data[''] is a cell with a number except the output: data['personesAtesesVoluntariatTotal1'].

As you can see, the code it is not clean but is the only way I can get the sum. I've try this code too but it wont work in FormIO while in visual studio code works fine:

let result = 0;

for (let i = 1; i < (data['tableName'].rows.length-1); i++)
{
        result += parseInt(data['tableName'].rows[i].cells[1]);
}
data['outputName'] = result;

This is the JSON from the table:

{
  "label": "Table",
  "cellAlignment": "left",
  "customClass": "standardTable tableSpacing",
  "bordered": true,
  "hover": true,
  "key": "table22",
  "type": "table",
  "numRows": 6,
  "numCols": 4,
  "input": false,
  "tableView": false,
  "rows": [
    [
      {
        "components": []
      },
      {
        "components": [
          {
            "label": "HTML",
            "attrs": [
              {
                "attr": "",
                "value": ""
              }
            ],
            "content": "<div style=\"text-align:center ; background-color : #900000 ; height: 100%;\"><h3 style=\"color:white;font-weight: bold;\">No binari</h3></div>",
            "refreshOnChange": false,
            "key": "html154",
            "type": "htmlelement",
            "input": false,
            "tableView": false
          }
        ]
      }
    ],
    [
      {
        "components": [
          {
            "label": "HTML",
            "attrs": [
              {
                "attr": "",
                "value": ""
              }
            ],
            "content": "<div>Persones de menys de 16 anys</div>",
            "refreshOnChange": false,
            "key": "html155",
            "type": "htmlelement",
            "input": false,
            "tableView": false
          }
        ]
      },
      {
        "components": [
          {
            "label": "Number",
            "hideLabel": true,
            "mask": false,
            "tableView": false,
            "delimiter": false,
            "requireDecimal": false,
            "inputFormat": "plain",
            "truncateMultipleSpaces": false,
            "key": "personesAtesesNoBinari17",
            "type": "number",
            "input": true
          }
        ]
      }
    ],
    [
      {
        "components": [
          {
            "label": "HTML",
            "attrs": [
              {
                "attr": "",
                "value": ""
              }
            ],
            "content": "<div>Persones entre 16 a 29 anys</div>",
            "refreshOnChange": false,
            "key": "html156",
            "type": "htmlelement",
            "input": false,
            "tableView": false
          }
        ]
      },
      {
        "components": [
          {
            "label": "Number",
            "hideLabel": true,
            "mask": false,
            "tableView": false,
            "delimiter": false,
            "requireDecimal": false,
            "inputFormat": "plain",
            "truncateMultipleSpaces": false,
            "key": "personesAtesesNoBinari1631",
            "type": "number",
            "input": true
          }
        ]
      }
    ],
    [
      {
        "components": [
          {
            "label": "HTML",
            "attrs": [
              {
                "attr": "",
                "value": ""
              }
            ],
            "content": "<div>Persones entre 30 i 64 anys</div>",
            "refreshOnChange": false,
            "key": "html157",
            "type": "htmlelement",
            "input": false,
            "tableView": false
          }
        ]
      },
      {
        "components": [
          {
            "label": "Number",
            "hideLabel": true,
            "mask": false,
            "tableView": false,
            "delimiter": false,
            "requireDecimal": false,
            "inputFormat": "plain",
            "truncateMultipleSpaces": false,
            "key": "personesAtesesNoBinari3066",
            "type": "number",
            "input": true
          }
        ]
      }
    ],
    [
      {
        "components": [
          {
            "label": "HTML",
            "attrs": [
              {
                "attr": "",
                "value": ""
              }
            ],
            "content": "<div>Persones de 65 anys o més</div>",
            "refreshOnChange": false,
            "key": "html158",
            "type": "htmlelement",
            "input": false,
            "tableView": false
          }
        ]
      },
      {
        "components": [
          {
            "label": "Number",
            "hideLabel": true,
            "mask": false,
            "tableView": false,
            "delimiter": false,
            "requireDecimal": false,
            "inputFormat": "plain",
            "truncateMultipleSpaces": false,
            "key": "personesAtesesNoBinari67",
            "type": "number",
            "input": true
          }
        ]
      }
    ],
    [
      {
        "components": [
          {
            "label": "HTML",
            "attrs": [
              {
                "attr": "",
                "value": ""
              }
            ],
            "content": "<div>Total</div>",
            "refreshOnChange": false,
            "key": "html159",
            "type": "htmlelement",
            "input": false,
            "tableView": false
          }
        ]
      },
      {
        "components": [
          {
            "label": "Number",
            "hideLabel": true,
            "mask": false,
            "disabled": true,
            "tableView": false,
            "defaultValue": 0,
            "delimiter": false,
            "requireDecimal": false,
            "inputFormat": "plain",
            "truncateMultipleSpaces": false,
            "key": "personesAtesesNoBinariTotal2",
            "logic": [
              {
                "name": "SumatoriPersonesAtesesNoBinariTotal",
                "trigger": {
                  "type": "javascript",
                  "javascript": "if(data['personesAtesesNoBinari17'] >= 0 || data['personesAtesesNoBinari1631'] >= 0 || data['personesAtesesNoBinari3066'] >= 0 || data['personesAtesesNoBinari67'] >= 0){\n  if(data['personesAtesesNoBinari17'] === undefined){\n    data['personesAtesesNoBinari17'] = 0;\n  }\n  if(data['personesAtesesNoBinari1631'] === undefined){\n    data['personesAtesesNoBinari1631'] = 0;\n  }\n  if(data['personesAtesesNoBinari3066'] === undefined){\n    data['personesAtesesNoBinari3066'] = 0;\n  }\n  if(data['personesAtesesNoBinari67'] === undefined){\n    data['personesAtesesNoBinari67'] = 0;\n  }\n  data['personesAtesesNoBinariTotal2'] = data['personesAtesesNoBinari17'] + data['personesAtesesNoBinari1631'] + data['personesAtesesNoBinari3066'] + data['personesAtesesNoBinari67'];\n}else{\n  data['personesAtesesNoBinariTotal2'] = 0;\n}"
                },
                "actions": [
                  {
                    "name": "SumatoriPersonesAtesesNoBinariTotal",
                    "type": "value",
                    "value": "value = data['personesAtesesNoBinariTotal2'];"
                  }
                ]
              }
            ],
            "type": "number",
            "personesAtesesNoBinariTotal2": 0,
            "personesAtesesNoBinariTotal": 0,
            "input": true
          }
        ]
      }
    ]
  ]
}

Hi, thanks for your question. Can you provide a [minimally reproducible example](https://stackoverflow.com/help/minimal-reproducible-example) via a JSFiddle or even just your form's JSON so we can have a better idea of what you're trying to do? Thanks! — Brendan Bond, Jan 05 '23 at 12:32
Hi @BrendanBond. Thanks for comment. I've add the JSON and change the logic example with a shorter one so It could be readed easily. — Javi, Jan 09 '23 at 10:25

Brendan Bond · Accepted Answer · 2023-01-12T14:04:59.293

0

Rather than use custom logic here, I'd use the calculated value field, which is much more in line with what you're trying to accomplish. Check out this working example on JSFiddle.
The table component is an older component more geared towards layout concerns and is less ergonomic when making calculations like this (as you can see from the code above, where I have to double check that the personesAtesesNoBinariTotal2 key is not being counted towards the total value). The data grid component is much more suited for this kind of thing, check out this working example on JSFiddle. The code to calculate this value is much more in line how you expected your for loop to work:
```
value = data.expenses.reduce(function(total, row) {
  return total + row.amount;
}, 0);
```
which could easily be written as:
```
for (let row of data.expenses) {
  value += row.amount;
}
```

edited Jan 12 '23 at 14:04

answered Jan 11 '23 at 14:59

Brendan Bond

1,737
1
10
8

I've found this very useful but with data grids as you say. I'm trying to use calculated value on the table but It won't work... I must use tables because users can't edit forms and make a table with an edit grid or data grid is not that easy. Thank you for the help. – Javi Jan 12 '23 at 08:57
@Javi What about the first JSFiddle isn't working? That uses your table – Brendan Bond Jan 12 '23 at 13:57
OH WOW I linked the wrong thing, I've fixed that first JSFiddle link @Javi – Brendan Bond Jan 12 '23 at 14:05
Oh sorry I forgot to mention about the link... what a mess... Thank you so much @BrendanBond , works fine!! – Javi Jan 12 '23 at 16:44
Hi again @BrendanBond. Despite the fact that your solution works, sometimes it fails to do the sum correctly. In some tables, if I set a 1 in the first input and another 1 in the second input, the total adds up to 32 (1+1 = 32). Would you know why this is? Also, how could be the script to sum specific fields and not the whole table? If you think this deserves another question, tell me and I'll create a new one. Thanks. – Javi Jan 13 '23 at 10:48
@Javi yeah might be best to start fresh in another question – Brendan Bond Jan 13 '23 at 16:01
Hi @BrendanBond. This is the new question https://stackoverflow.com/questions/75148537/problem-with-table-data-sum-in-formio-table-element – Javi Jan 17 '23 at 15:18

Get column data sum from FormIO table element

1 Answers1

Linked