Wrong calculation value saved

itapps · August 8, 2022, 6:49am

I have a datatable saved in a filed like that.

Before I submit the form, that table value is like that.

However, the value saved wrongly like that

[{"Column1":"Opening Cash","Column2":"0.00","Column3":"-700000.00","Column4":"-2150000.00","Column5":"-3678000.00","Column6":"-4246000.00","Column7":"-4314000.00"},{"Column1":"Additional Funding (Same as above)","Column2":"0.00","Column3":"0.00","Column4":"0.00","Column5":"0.00","Column6":"0.00","Column7":"0.00"},{"Column1":"Sales Revenue (same as above)","Column2":"0.00","Column3":"0.00","Column4":"212000.00","Column5":"1632000.00","Column6":"3682000.00","Column7":"7532000.00"},{"Column1":"Total Cash In","Column2":"0.00","Column3":"-700000.00","Column4":"-1938000.00","Column5":"-2046000.00","Column6":"-564000.00","Column7":"3218000.00"},{"Column1":"Cash Out","Column2":"700000.00","Column3":"1450000.00","Column4":"1740000.00","Column5":"2200000.00","Column6":"3750000.00","Column7":"4250000.00"},{"Column1":"Net Balance","Column2":"-700000.00","Column3":"-2150000.00","Column4":"-3678000.00","Column5":"-4246000.00","Column6":"-4314000.00","Column7":"-1032000.00"}]

The highlighted column is wrong and make other calculation in this table wrong.
Why it will saved a wrong value after submit?

itapps · August 8, 2022, 6:50am

The calculation coding is like that

function AllCal()
{
//column2 Opening Cash
if (fd.control('DataTable2').value[0].Column2!=''){
fd.control('DataTable2').value[0].Column2=parseFloat(fd.control('DataTable2').value[0].Column2).toFixed(2);
}
//column2 Additional Funding
fd.control('DataTable2').value[1].Column2=parseFloat(getNum(parseFloat(fd.control('DataTable3').value[0].Column2))+getNum(parseFloat(fd.control('DataTable3').value[1].Column2))+getNum(parseFloat(fd.control('DataTable3').value[2].Column2))+getNum(parseFloat(fd.control('DataTable3').value[3].Column2))).toFixed(2);
//column2 Sales Revenue
fd.control('DataTable2').value[2].Column2=parseFloat(getNum(parseFloat(fd.control('DataTable5').value[0].Column2))+getNum(parseFloat(fd.control('DataTable5').value[1].Column2))).toFixed(2);
//column2 Total Cash In
fd.control('DataTable2').value[3].Column2=parseFloat(getNum(parseFloat(fd.control('DataTable2').value[0].Column2))+getNum(parseFloat(fd.control('DataTable2').value[1].Column2))+getNum(parseFloat(fd.control('DataTable2').value[2].Column2))).toFixed(2);
//column2 Cash Out
if (fd.control('DataTable2').value[4].Column2!=''){
fd.control('DataTable2').value[4].Column2=parseFloat(fd.control('DataTable2').value[4].Column2).toFixed(2);
}
//column2 Net Balance
fd.control('DataTable2').value[5].Column2=parseFloat(getNum(parseFloat(fd.control('DataTable2').value[3].Column2))-getNum(parseFloat(fd.control('DataTable2').value[4].Column2))).toFixed(2);

//column3 Opening Cash
fd.control('DataTable2').value[0].Column3=parseFloat(getNum(parseFloat(fd.control('DataTable2').value[5].Column2))).toFixed(2);
//Column3 Additional Funding
fd.control('DataTable2').value[1].Column3=parseFloat(getNum(parseFloat(fd.control('DataTable3').value[0].Column3))+getNum(parseFloat(fd.control('DataTable3').value[1].Column3))+getNum(parseFloat(fd.control('DataTable3').value[2].Column3))+getNum(parseFloat(fd.control('DataTable3').value[3].column3))).toFixed(2);
//Column3 Sales Revenue
fd.control('DataTable2').value[2].Column3=parseFloat(getNum(parseFloat(fd.control('DataTable5').value[0].Column3))+getNum(parseFloat(fd.control('DataTable5').value[1].Column3))).toFixed(2);
//Column3 Total Cash In
fd.control('DataTable2').value[3].Column3=parseFloat(getNum(parseFloat(fd.control('DataTable2').value[0].Column3))+getNum(parseFloat(fd.control('DataTable2').value[1].Column3))+getNum(parseFloat(fd.control('DataTable2').value[2].Column3))).toFixed(2);
//column3 Cash Out
if (fd.control('DataTable2').value[4].Column3!=''){
fd.control('DataTable2').value[4].Column3=parseFloat(fd.control('DataTable2').value[4].Column3).toFixed(2);
}
//Column3 Net Balance
fd.control('DataTable2').value[5].Column3=parseFloat(getNum(parseFloat(fd.control('DataTable2').value[3].Column3))-getNum(parseFloat(fd.control('DataTable2').value[4].Column3))).toFixed(2);

//column4 Opening Cash
fd.control('DataTable2').value[0].Column4=parseFloat(getNum(parseFloat(fd.control('DataTable2').value[5].Column3))).toFixed(2);
//column4 Additional Funding
fd.control('DataTable2').value[1].Column4=parseFloat(getNum(parseFloat(fd.control('DataTable3').value[0].Column4))+getNum(parseFloat(fd.control('DataTable3').value[1].Column4))+getNum(parseFloat(fd.control('DataTable3').value[2].Column4))+getNum(parseFloat(fd.control('DataTable3').value[3].Column4))).toFixed(2);
//column4 Sales Revenue
fd.control('DataTable2').value[2].Column4=parseFloat(getNum(parseFloat(fd.control('DataTable5').value[0].Column4))+getNum(parseFloat(fd.control('DataTable5').value[1].Column4))).toFixed(2);
//column4 Total Cash In
fd.control('DataTable2').value[3].Column4=parseFloat(getNum(parseFloat(fd.control('DataTable2').value[0].Column4))+getNum(parseFloat(fd.control('DataTable2').value[1].Column4))+getNum(parseFloat(fd.control('DataTable2').value[2].Column4))).toFixed(2);
//column4 Cash Out
if (fd.control('DataTable2').value[4].Column4!=''){
fd.control('DataTable2').value[4].Column4=parseFloat(fd.control('DataTable2').value[4].Column4).toFixed(2);
}
//column4 Net Balance
fd.control('DataTable2').value[5].Column4=parseFloat(getNum(parseFloat(fd.control('DataTable2').value[3].Column4))-getNum(parseFloat(fd.control('DataTable2').value[4].Column4))).toFixed(2);

//column5 Opening Cash
fd.control('DataTable2').value[0].Column5=parseFloat(getNum(parseFloat(fd.control('DataTable2').value[5].Column4))).toFixed(2);
//column5 Additional Funding
fd.control('DataTable2').value[1].Column5=parseFloat(getNum(parseFloat(fd.control('DataTable3').value[0].Column5))+getNum(parseFloat(fd.control('DataTable3').value[1].Column5))+getNum(parseFloat(fd.control('DataTable3').value[2].Column5))+getNum(parseFloat(fd.control('DataTable3').value[3].Column5))).toFixed(2);
//column5 Sales Revenue
fd.control('DataTable2').value[2].Column5=parseFloat(getNum(parseFloat(fd.control('DataTable5').value[0].Column5))+getNum(parseFloat(fd.control('DataTable5').value[1].Column5))).toFixed(2);
//Column5 Total Cash In
fd.control('DataTable2').value[3].Column5=parseFloat(getNum(parseFloat(fd.control('DataTable2').value[0].Column5))+getNum(parseFloat(fd.control('DataTable2').value[1].Column5))+getNum(parseFloat(fd.control('DataTable2').value[2].Column5))).toFixed(2);
//column5 Cash Out
if (fd.control('DataTable2').value[4].Column5!=''){
fd.control('DataTable2').value[4].Column5=parseFloat(fd.control('DataTable2').value[4].Column5).toFixed(2);
}
//Column5 Net Balance
fd.control('DataTable2').value[5].Column5=parseFloat(getNum(parseFloat(fd.control('DataTable2').value[3].Column5))-getNum(parseFloat(fd.control('DataTable2').value[4].Column5))).toFixed(2);

//column6 Opening Cash
fd.control('DataTable2').value[0].Column6=parseFloat(getNum(parseFloat(fd.control('DataTable2').value[5].Column5))).toFixed(2);
//Column6 Additional Funding
fd.control('DataTable2').value[1].Column6=parseFloat(getNum(parseFloat(fd.control('DataTable3').value[0].Column6))+getNum(parseFloat(fd.control('DataTable3').value[1].Column6))+getNum(parseFloat(fd.control('DataTable3').value[2].Column6))+getNum(parseFloat(fd.control('DataTable3').value[3].Column6))).toFixed(2);
//Column6 Sales Revenue
fd.control('DataTable2').value[2].Column6=parseFloat(getNum(parseFloat(fd.control('DataTable5').value[0].Column6))+getNum(parseFloat(fd.control('DataTable5').value[1].Column6))).toFixed(2);
//Column6 Total Cash In
fd.control('DataTable2').value[3].Column6=parseFloat(getNum(parseFloat(fd.control('DataTable2').value[0].Column6))+getNum(parseFloat(fd.control('DataTable2').value[1].Column6))+getNum(parseFloat(fd.control('DataTable2').value[2].Column6))).toFixed(2);
//column6 Cash Out
if (fd.control('DataTable2').value[4].Column6!=''){
fd.control('DataTable2').value[4].Column6=parseFloat(fd.control('DataTable2').value[4].Column6).toFixed(2);
}
//Column6 Net Balance
fd.control('DataTable2').value[5].Column6=parseFloat(getNum(parseFloat(fd.control('DataTable2').value[3].Column6))-getNum(parseFloat(fd.control('DataTable2').value[4].Column6))).toFixed(2);

//column7 Opening Cash
fd.control('DataTable2').value[0].Column7=parseFloat(getNum(parseFloat(fd.control('DataTable2').value[5].Column6))).toFixed(2);
//Column7 Additional Funding
fd.control('DataTable2').value[1].Column7=parseFloat(getNum(parseFloat(fd.control('DataTable3').value[0].Column7))+getNum(parseFloat(fd.control('DataTable3').value[1].Column7))+getNum(parseFloat(fd.control('DataTable3').value[2].Column7))+getNum(parseFloat(fd.control('DataTable3').value[3].Column7))).toFixed(2);
//Column7 Sales Revenue
fd.control('DataTable2').value[2].Column7=parseFloat(getNum(parseFloat(fd.control('DataTable5').value[0].Column7))+getNum(parseFloat(fd.control('DataTable5').value[1].Column7))).toFixed(2);
//Column7 Total Cash In
fd.control('DataTable2').value[3].Column7=parseFloat(getNum(parseFloat(fd.control('DataTable2').value[0].Column7))+getNum(parseFloat(fd.control('DataTable2').value[1].Column7))+getNum(parseFloat(fd.control('DataTable2').value[2].Column7))).toFixed(2);
//column7 Cash Out
if (fd.control('DataTable2').value[4].Column7!=''){
fd.control('DataTable2').value[4].Column7=parseFloat(fd.control('DataTable2').value[4].Column7).toFixed(2);
}
//Column7 Net Balance
fd.control('DataTable2').value[5].Column7=parseFloat(getNum(parseFloat(fd.control('DataTable2').value[3].Column7))-getNum(parseFloat(fd.control('DataTable2').value[4].Column7))).toFixed(2);
fd.control('DataTable2').widget.refresh();
}

Please have a look

Nikita_Kurguzov · August 8, 2022, 1:22pm

Dear @itapps,
No idea why it could be like this - there is way too much code for us to possibly test. Can you check the value before save? For example, you can copy value for the whole DataTable to some other Multiline text field on the form with JavaScript, this will allow you to monitor the value in real time and after save.

itapps · August 16, 2022, 8:04am

Dear @Nikita_Kurguzov
I just tried that

console.log(fd.control('DataTable2').value);
console.log(fd.control('DataTable2').value[0].Column1 + '; ' + fd.control('DataTable2').value[0].Column2 + '; ' + fd.control('DataTable2').value[0].Column3 + '; ' + fd.control('DataTable2').value[0].Column4 + '; ' + fd.control('DataTable2').value[0].Column5 + '; ' + fd.control('DataTable2').value[0].Column6 + '; ' + fd.control('DataTable2').value[0].Column7 + '; ');

The backend value is really different with the display value

Please check the bottom console

Do you know why it can be different?

The 0th row - Colum 3 - fd.control('DataTable2').value is different with fd.control('DataTable2').value[0].Column3.

itapps · August 16, 2022, 8:06am

@Nikita_Kurguzov it seems that it will get the previous value before change.
How can I fix it?

Nikita_Kurguzov · August 16, 2022, 9:39am

Dear @itapps,
When exactly are you accessing this value? You can check our article here with an example on calculations to know when to trigger them -DataTable — SharePoint forms

itapps · August 17, 2022, 8:30am

I am trying somethings like this

I tried the code like this.

However, it keeps looping.

How can I prevent the loop here? @Nikita_Kurguzov
Please help. Thank you

itapps · August 19, 2022, 2:38am

I just fixed it by saving value = value before save..
Thank you for your response.