11 Replies Latest reply on May 3, 2012 7:17 AM by gkaiseril

    3 Step Formula

    a.n.bentz

      Hello,

       

      When using Adobe X Standard to write formulas in the Custom Calculation Script field, can you create a 3 step formula? Basically I want for it to be v1+v2-v3=v4.

       

      Thank you for your help!

        • 1. Re: 3 Step Formula
          try67 MVP & Adobe Community Professional

          You can have as many steps as you'd like. If those are your fields' names,

          just place this formula under the Simple Field Notation option of the

          Calculate tab of v4:

          v1 + v2 - v3

          • 2. Re: 3 Step Formula
            a.n.bentz Level 1

            Thank you!!

             

            I was trying to make it more difficult than I needed it to be.

            • 3. Re: 3 Step Formula
              gkaiseril MVP & Adobe Community Professional

              As noted the simplified field notation is easy if the names comply with the naming requirements for this calculation.

               

              You can even use the option for the "Field is the ____ of the following fields" by selecting the operation and fields to be processed.

               

              If you want to use the Custom JavaScript Calculation then you need to write the calculation using JavaScript. This is approach is needed to use execution flow control statements like the "if...then...", use the methods of objects or call predefined functions.

               

              To add 3 fields and prevent concatenation due to a blank field:

               

              event.value = Number(this.getField("v1").value) + Number(this.getField("v2").value) + Number(this.getField("v3").value);

              1 person found this helpful
              • 4. Re: 3 Step Formula
                a.n.bentz Level 1

                Thanks everyone for their feedback, it's been wonderful. I do have just one more question. The sheet I am working on is dealing with money calculations. I want the calculations to round down. So if the numbers are 10.32+10.52-3.82=17.02 I want the answer to read 17. How would I write that?

                • 5. Re: 3 Step Formula
                  gkaiseril MVP & Adobe Community Professional

                  You can specify the number of decimals for the "Number" format. Setting the "Decimals" to 2 will round up at 0.50 or greater and truncate for values below.

                   

                  If you want the value of the field to be rounded, then you need to use the custom JavaScript calculation and use the 'util.printf" method or write a function to perform the task. JavaScript's "Math.round" method has a known issue.

                   

                  To get both the rounding and the decimal alignment:

                   

                  var v1 = Number(this.getField("v1").value);

                  var v2 = Number(this.getField("v2").value);

                  var v3 = Number(this.getField("v3").value);

                  var nResult = v1 + v2 - v3;

                  console.show();

                  console.clear();

                  console.println("nResult = " + nResult);

                  var sResult = util.printf("%,0 .0f", result)

                  console.println("sResult = " + sResult);

                  console.println("event.value = " + Number(sResult))

                  event.value = Number(sResult);

                  1 person found this helpful
                  • 6. Re: 3 Step Formula
                    a.n.bentz Level 1

                    Thank you!

                     

                    I feel bad to keep asking so many questions, but when I use the above formula a "JavaScript Debugger" appears and the text written says:

                    nResult = 174.45

                    ReferenceError: result is not defined

                    8 : Field : Calculate

                     

                    Does this mean anything to you? Or is this the known issue you were talking about? The nResult number is correct for the test numbers I ran, I would just want it to say 174 in the v4 field. And this form, once complete, will be used by a bunch of different employees in the company I work for. So would the JavaScript debugger always appear when that particular formula is being used? Or is there a way to just have the number populate into it's field without having to use the Debugger to get the answer?

                    • 7. Re: 3 Step Formula
                      gkaiseril MVP & Adobe Community Professional

                      The line to create the sResult variable should read:

                       

                      var sResult = util.printf("%,0 .0f", nResult)

                       

                      an not:

                       

                      var sResult = util.printf("%,0 .0f", result)

                       

                      The message references line 8 in the script and that there is no defined variable "reslut".

                      1 person found this helpful
                      • 8. Re: 3 Step Formula
                        a.n.bentz Level 1

                        Thanks for all the information!

                         

                        I work in a mortgage company and the numbers we'll  be using are run in the hundred thousands, and when I used a real life scenario to test the process, the nResult =452,403.19, sResult=452,403, but the event.value = NaN.

                         

                        Lol, I think I might just have the employees do the math by hand. :-)

                        • 9. Re: 3 Step Formula
                          gkaiseril MVP & Adobe Community Professional

                          Sorry,

                           

                          the line should read:

                           

                          var sResult = util.printf("%,3 .0f", nResult);

                           

                          This will create a number string with only the decimal point.

                          1 person found this helpful
                          • 10. Re: 3 Step Formula
                            a.n.bentz Level 1

                            Thank you again. Everything worked great!!

                             

                            Now, I have just one last question (hopefully). I want the mathematical equation to always round down. Even if the number is 999.99. Instead of the number being 1,000, I want it to read 999. Is that possible? The current line I'm using rounds down if it's below the cents is below 50 and up if it's above 50.

                             

                            Thank you!!

                            • 11. Re: 3 Step Formula
                              gkaiseril MVP & Adobe Community Professional

                              You can use the Math.floor() method:

                               

                              eventn.vlaue = Math.floor(nResult);

                               

                              or parseInt() global object:

                               

                              event.value = parseInt(nResult, 10);