9 Replies Latest reply on Jul 11, 2016 9:21 AM by gkaiseril

    Negative numbers not working in calculation

    Ms Tela

      I am new to form field calculations and have run into an issue I can't resolve. I have a form with calculations. There are four columns, (beginning balance, receipts, expenditures and ending balance) the problem happens when a negative number is entered for the beginning balance. My calculation should be beginning balance + receipts - expenditures = ending balance but if a negative beginning balance is ed and you tab to receipts, enter receipts, tab to expenditures, enter expenditures, tab to ending balance I get the error "The value entered does not match the format of the field'. If beginning balance is a positive number there isn't a problem. I can send someone a copy of the form if that will help resolve this. Any help or suggestions would be greatly appreciated.

      I am using Acrobat 9 Pro

      Here is a sample of my calculation for ending balance:

      // Get first field value
      var v1 = getField("Beginning Balance.7").value;

      // Get second field value
      var v2 = getField("Receipts7").value;

      // Get first field value
      var v3 = getField("Expenditures7").value;

      // Set this field value equal to the difference
      event.value = v1 + v2 - v3;

        • 1. Re: Negative numbers not working in calculation
          gkaiseril MVP & Adobe Community Professional

          Are any of the input fields to your calculation empty?

           

          A null field in some processed is treated like zero while in others it is treated like a string or non-numeric value. The "+" operator performs additions if the values being processed are clearly numeric values, but concatenates or joins the values if any value is not a numeric value.

           

          Your script needs to make sure all values are numeric values. You can use the "Number" constrictor to convert a null string to zero.

           

          // Get first field value
          var v1 = getField("Beginning Balance.7").value;

          // Get second field value
          var v2 = getField("Receipts7").value;

          // Get first field value
          var v3 = getField("Expenditures7").value;

          // Set this field value equal to the difference
          event.value = Nmber(v1) + Number(v2) - v3;

           

          Note: only when using the "+" operator does one need to force null strings to a zero value. All other arithmetic operations only process numbers and automatically force null stings to the numeric zero.

           

          There are other ways to force a null string to zero that include multiplying by  the Multiplicative Identity, 1.

          • 2. Re: Negative numbers not working in calculation
            Ms Tela Level 1

            Thank you for responding. No, all the fields have at least a zero in them. I have attached the form. If you look at line 8 for kitchen, the beginning balance is -7,236.13, receipts is 21,190.85. If I enter the expenditures of 13,954.72 I get the error "The value entered does not match the format of the field (EndingBalance7).

             

            Hopefully I am not making this worse.

             

             

             

            Tela Harbold, Dept. Guard

            VFW Auxiliary Dept. of Florida

            ★ Honesty ★ Integrity ★ Commitment

            • 3. Re: Negative numbers not working in calculation
              try67 MVP & Adobe Community Professional

              You can't attach files to a message in these forums directly. Upload it to something like Dropbox or Google Drive and then post the link to it here.

              • 4. Re: Negative numbers not working in calculation
                Ms Tela Level 1

                Thank you. Here is the Google Drive link: https://drive.google.com/file/d/0B48sUQNL979TV0dyaWJZTkJZZzA/view?usp=sharing

                 

                Any help would be appreciated.

                 

                 

                 

                Tela Harbold, Dept. Guard

                VFW Auxiliary Dept. of Florida

                ★ Honesty ★ Integrity ★ Commitment

                • 5. Re: Negative numbers not working in calculation
                  try67 MVP & Adobe Community Professional

                  Works fine for me, even when the result is negative.

                  • 6. Re: Negative numbers not working in calculation
                    gkaiseril MVP & Adobe Community Professional

                    Computers do not perform calculations using decimal values but convert the decimal values to binary values, perform the computation in the binary base, and then convert back to the decimal values. Since for many decimal value there is not an exact conversion value a small error gets introduced into the values and calculation. Changing the format to "None" and expanding the field for the result, one sees a value of -1.8189894035458565e-12  or the value of -"0." followed by 12 zeros and then the value 1.8189894035458565. A very small value. If one rounds the value of the calculation (not the displayed format of the Format option) one can get the answer of $0.00. One can verify this result by changing the "Format" option to "None" and expanding the field to accommodate the longer value.

                     

                    I would use the util.printf method to perform the rounding for the result of the calculation.

                     

                    Your calculation script could be:

                     

                    // Get first field value
                    var v1 = getField("Beginning Balance.7").value;

                    // Get second field value
                    var v2 = getField("Receipts7").value;

                    // Get first field value
                    var v3 = getField("Expenditures7").value;

                    // Set this field value equal to the difference
                    event.value = util.printf("%,101.10f", Number(v1) + Number(v2) - Number(v3));

                     

                    This script rounds to 10 decimal paces assuring the result will be within the value that can be displayed using the Number format.

                    • 7. Re: Negative numbers not working in calculation
                      Ms Tela Level 1

                      I can get it to work with a negative result but not if the beginning balance is a negative. Any suggestions?

                       

                      Sent from my iPad

                      • 8. Re: Negative numbers not working in calculation
                        Ms Tela Level 1

                        That did it! Thank you!

                         

                         

                        var nResult = Number(this.getField("Beginning Balance.7").value) + Number(this.getField("Receipts7").value) - Number(this.getField("Expenditures7").value);

                        event.value = util.printf("%,101.10f", nResult);;

                         

                         

                         

                         

                        Tela Harbold, Dept. Guard

                        VFW Auxiliary Dept. of Florida

                        ★ Honesty ★ Integrity ★ Commitment

                        • 9. Re: Negative numbers not working in calculation
                          gkaiseril MVP & Adobe Community Professional

                          You should see that you have the exact same calculation 17 times with only the name of 3 fields changing. Since the field names are systemically named, having a standard field name for each column and the last characters of the field name being the row number, allows one to write a common block of code that only needs to only know the row number to compute the ending balance for each row and using the column name and row number. Once can write a document level function that could be used for each ending balance field.

                           

                          A possible function is:

                           

                          function ComputeEndigBalance(cBeginning, cReceipts, cExpenditures)
                          {
                          // Get beginning balance
                          var v1 = this.getField(cBeginning).value;

                          // Get receipts value
                          var v2 = this.getField(cReceipts).value;

                          // Get ependitures value
                          var v3 = getField(cExpenditures).value;

                          // return rounded value equal to the difference
                          return util.printf("%,101.2f", Number(v1) + Number(v2) - Number(v3));
                          } // end ComputeEndingBalance function;

                           

                          One now only needs to call this function with a field name for each of columns to use in the calculation. The parameters are positional so the first parameter is the beginning balance, then the receipts, and finally the expenditures. The function will return the computed ending balance.

                           

                          To use this function in a row's ending balance field, one can use the following custom JavaScript calculation:

                           

                          var nRow = 0;
                          event.value = ComputeEndigBalance("Beginning Balance." + nRow, "Receipts" + nRow, "Expenditures" + nRow);

                           

                          This code uses the "base" or common name part for each column and then add the tow number to compute the name for each parameter by row.

                           

                          This reduces the amount of coding to be done for each row,  places all of the common code in one location so if there is a need to edit or change the code it is done in one place instead of 17.

                           

                          The calculation for each column could be reduced even further to the point where the row number is computed or extracted from the ending balance field name. This mean that once one has the first row script worked out, the make multiple copies of the 1st row's fields can be created and the computation will automatically adjust for each row.