Skip to main content
A
Anonymous
February 18, 2020
Question

Calculations adding digits instead of sum total

  • February 18, 2020
  • 3 replies
  • 799 views

I have multiple, simple calculation scripts in my template that sum row fields horizontally and column fields vertically. The same fields are used to calculate both horizontal and vertical totals for the columns and rows. The problem I have is some of the totals add the fields as digits. For example 8+8+8 should equal 24 but in some totals 8+8+8 = 888. I have tried adjusting the calculation order but the problem seems to remain. If I physically enter a zero in the blank fields the calculation problem is fixed? I setting all the source fields default value to zero and hidden if zero, but if a row entry is missed the calculation reverts back to my poblem.

Is there a way to ignore blank values in the calculations for example below?

 

var prefix = "";
var nameParts = event.target.name.split(".");
if (nameParts.length > 2) {
prefix = nameParts[0] + "." + nameParts[1] + ".";
}

event.value = this.getField(prefix + "HoursRow1").value
+ this.getField(prefix + "HoursRow2").value
+ this.getField(prefix + "HoursRow3").value
+ this.getField(prefix + "HoursRow4").value
+ this.getField(prefix + "HoursRow5").value
+ this.getField(prefix + "HoursRow6").value
+ this.getField(prefix + "HoursRow7").value
+ this.getField(prefix + "HoursRow8").value
+ this.getField(prefix + "HoursRow9").value
+ this.getField(prefix + "HoursRow10").value;


if(event.value==0)
event.value="";

This topic has been closed for replies.

3 replies

try67
Community Expert
try67Community Expert
Community Expert
February 18, 2020

If the value of any of the fields is a String then JS will treat all of them as Strings and just concatenate them, instead of adding them up. That's why it's important to explicitly convert the values to Numbers.

Bernd Alheit
Community Expert
Bernd AlheitCommunity Expert
Community Expert
February 18, 2020

Use something like this:

var res = 0;
for (var i = 1; i <= 10; i++)
  res += Number(this.getField(prefix + "HoursRow" + i).valueAsString);
if (res == 0) event.value = "";
else event.value = res;
    A
    Anonymous
    February 18, 2020

    Thanks Bernd,

     

    thats the fix i found. Plus some field renumbering.

    ls_rbls
    Community Expert
    ls_rblsCommunity Expert
    Community Expert
    February 18, 2020

    Interesting!

     

    The form is working on my end without the fix though.

     

    I enter zeros or leave some fields blank and the calculations are working fine.

     

     

    ls_rbls
    Community Expert
    ls_rblsCommunity Expert
    Community Expert
    February 18, 2020

    Hi,

     

    It looks like you created this document directly from MS Excel.

     

    The code is fine, I played around rearraging the tabbin order again and that didn't do anything.

     

    I was able to resolve the issue by coping the custom calculated script from a known-good working cell in the HoursTotal block  down below at the bottom; specifically I copied from  Saturday Row Hours Total which seems like you worked before on it and had no issues.

     

    So I  deleted the script that was HorsTotal foer Monday  and pasted the working script from HoursTotal Saturday row in the custom calc script of Monday Row HoursTotal; then manually changed this part "1_6" for example,  to "HoursRow1".

     

    I did this change for each and  on each line.

     

    The first three rows from Monday through Wednesday were prolematic. Once I did the same procedure on each of the HoursTotals fields for those three days I started deleting zeroes and typing random numbers in the form; everything started falling into place on its own.

     

    In my opinion, it seems like if you've exported to PDF using the Acrobat PDF Maker add-on or the Save As PDF method  from MS Excel; by doing so, maybe some Excel VB script or similar code from a Macro (or any other Add-In) was retained somehow and conflicting with your PDF.

     

    If this happens again working with Excel spreadsheets use a different method; instead of exporting from Excel, try instead printing to PDF to generate a posctripted file. Then add your form fields using Acrobat, OR, see if importing an Excel document directly from Acrobat behaves differently when you cretae your custom calculated fields.

     

    Here is a link to the file you sent with some of the modifications I did.

    https://documentcloud.adobe.com/link/track?uri=urn%3Aaaid%3Ascds%3AUS%3Af21ffe9a-0725-4a40-808f-72b6adf22f51 

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices