Loop through elements and count how many of each value

[kpcollier]

I've got 3 calculated fields with case statements that are being used to sum up the number of occurrences of particular values from 6 dropdowns. I believe this is a big reason for the slowness of my datapage. As it is now, every time one of the values is changed, all three of the case statements go off.

Each of the 6 dropdowns have 3 possible values - 1, 2, or 3. Hence, 3 calculated fields for the sums of each possible value. You can see how this would take a while to load with each change in value, as it runs through 6 case statements for each of the 3 calc fields before a final result is produced.

CASE WHEN [@field:IG_Sheet_Table_IG1] = '2' THEN 1 ELSE 0 END
+
CASE WHEN '[@field:IG_Sheet_Table_IG2]' = '2' THEN 1 ELSE 0 END
+
CASE WHEN '[@field:IG_Sheet_Table_IG3]' = '2' THEN 1 ELSE 0 END
+
CASE WHEN '[@field:IG_Sheet_Table_IG4]' = '2' THEN 1 ELSE 0 END
+
CASE WHEN '[@field:IG_Sheet_Table_IG5]' = '2' THEN 1 ELSE 0 END
+
CASE WHEN '[@field:IG_Sheet_Table_IG6]' = '2' THEN 1 ELSE 0 END

 

I am looking for any advice on how to do this differently... I have been unsuccessful with javascript as well, but I am not the best at that. With the script below, I haven't got to the part where I'll add the calculated value to a field. I am just using console.log to see if it works first. I am getting an error that it can't read the eventListener of null. After some digging, I am thinking it is because of the querySelector, but I am not too sure what is going on.

var ig1v = document.querySelector('select[id^="InsertRecordIG_Sheet_Table_IG1_"]');
var ig2v = document.querySelector('select[id^="InsertRecordIG_Sheet_Table_IG2_"]');
var ig3v = document.querySelector('select[id^="InsertRecordIG_Sheet_Table_IG3_"]');
var ig4v = document.querySelector('select[id^="InsertRecordIG_Sheet_Table_IG4_"]');
var ig5v = document.querySelector('select[id^="InsertRecordIG_Sheet_Table_IG5_"]');
var ig6v = document.querySelector('select[id^="InsertRecordIG_Sheet_Table_IG6_"]');

function calcGroups(){
 const numberList = [ig1v.value, ig2v.value, ig3v.value, ig4v.value, ig5v.value, ig6v.value];
 const counts = {};

for (const num of numberList) {
  counts[num] = counts[num] ? counts[num] + 1 : 1;
};

console.log(counts[3], counts[2], counts[1]);
};

ig1v.document.addEventListener("change", calcGroups);

 

[kpcollier]

This is working, however I'm inclined to believe there's got to be a better way to write this, instead of having eventListeners on each field doing the same exact function. Nonetheless, it's working.

var ig1v = document.querySelector('select[id^="InsertRecordIG_Sheet_Table_IG1_"]');
var ig2v = document.querySelector('select[id^="InsertRecordIG_Sheet_Table_IG2_"]');
var ig3v = document.querySelector('select[id^="InsertRecordIG_Sheet_Table_IG3_"]');
var ig4v = document.querySelector('select[id^="InsertRecordIG_Sheet_Table_IG4_"]');
var ig5v = document.querySelector('select[id^="InsertRecordIG_Sheet_Table_IG5_"]');
var ig6v = document.querySelector('select[id^="InsertRecordIG_Sheet_Table_IG6_"]');

var vf1 = document.getElementById("cbParamVirtual23");
var vf2 = document.getElementById("cbParamVirtual22");
var vf3 = document.getElementById("cbParamVirtual21");

document.querySelector('select[id^="InsertRecordIG_Sheet_Table_IG1_"]').addEventListener("change", function calcGroups(){
 const numberList = [ig1v.value, ig2v.value, ig3v.value, ig4v.value, ig5v.value, ig6v.value];
 const counts = {};

for (const num of numberList) {
  counts[num] = counts[num] ? counts[num] + 1 : 1;
};

vf1.value = (counts[2] === undefined) ? 0 : counts[2];
vf2.value = (counts[1] === undefined) ? 0 : counts[1];
vf3.value = (counts[3] === undefined) ? 0 : counts[3];
console.log(counts[3], counts[2], counts[1]);
 
});

document.querySelector('select[id^="InsertRecordIG_Sheet_Table_IG2_"]').addEventListener("change", function calcGroups(){
 const numberList = [ig1v.value, ig2v.value, ig3v.value, ig4v.value, ig5v.value, ig6v.value];
 const counts = {};

for (const num of numberList) {
  counts[num] = counts[num] ? counts[num] + 1 : 1;
};

vf1.value = (counts[2] === undefined) ? 0 : counts[2];
vf2.value = (counts[1] === undefined) ? 0 : counts[1];
vf3.value = (counts[3] === undefined) ? 0 : counts[3];
console.log(counts[3], counts[2], counts[1]);
 
});

document.querySelector('select[id^="InsertRecordIG_Sheet_Table_IG3_"]').addEventListener("change", function calcGroups(){
 const numberList = [ig1v.value, ig2v.value, ig3v.value, ig4v.value, ig5v.value, ig6v.value];
 const counts = {};

for (const num of numberList) {
  counts[num] = counts[num] ? counts[num] + 1 : 1;
};

vf1.value = (counts[2] === undefined) ? 0 : counts[2];
vf2.value = (counts[1] === undefined) ? 0 : counts[1];
vf3.value = (counts[3] === undefined) ? 0 : counts[3];
console.log(counts[3], counts[2], counts[1]);
 
});

document.querySelector('select[id^="InsertRecordIG_Sheet_Table_IG4_"]').addEventListener("change", function calcGroups(){
 const numberList = [ig1v.value, ig2v.value, ig3v.value, ig4v.value, ig5v.value, ig6v.value];
 const counts = {};

for (const num of numberList) {
  counts[num] = counts[num] ? counts[num] + 1 : 1;
};

vf1.value = (counts[2] === undefined) ? 0 : counts[2];
vf2.value = (counts[1] === undefined) ? 0 : counts[1];
vf3.value = (counts[3] === undefined) ? 0 : counts[3];
console.log(counts[3], counts[2], counts[1]);
 
});

document.querySelector('select[id^="InsertRecordIG_Sheet_Table_IG5_"]').addEventListener("change", function calcGroups(){
 const numberList = [ig1v.value, ig2v.value, ig3v.value, ig4v.value, ig5v.value, ig6v.value];
 const counts = {};

for (const num of numberList) {
  counts[num] = counts[num] ? counts[num] + 1 : 1;
};

vf1.value = (counts[2] === undefined) ? 0 : counts[2];
vf2.value = (counts[1] === undefined) ? 0 : counts[1];
vf3.value = (counts[3] === undefined) ? 0 : counts[3];
console.log(counts[3], counts[2], counts[1]);
 
});

document.querySelector('select[id^="InsertRecordIG_Sheet_Table_IG6_"]').addEventListener("change", function calcGroups(){
 const numberList = [ig1v.value, ig2v.value, ig3v.value, ig4v.value, ig5v.value, ig6v.value];
 const counts = {};

for (const num of numberList) {
  counts[num] = counts[num] ? counts[num] + 1 : 1;
};

vf1.value = (counts[2] === undefined) ? 0 : counts[2];
vf2.value = (counts[1] === undefined) ? 0 : counts[1];
vf3.value = (counts[3] === undefined) ? 0 : counts[3];
console.log(counts[3], counts[2], counts[1]);
 
});

 

Edited July 15, 2021 by kpcollier
Was able to set undefined values to 0