12 Replies Latest reply on May 7, 2014 4:45 PM by gkaiseril

    Javascript

    jn2xneri

      How do I convert this excel formula =I16*(N16+P16) to javascript

        • 1. Re: Javascript
          gkaiseril MVP & Adobe Community Professional

          You can use the simplified field notaion:

           

          I16 * (N16 + P16)

           

          As long as your field names the same as the cell names.

           

          You can also use the Custom JavaScript:

           

          event.value = this.getField("I16").value  * this.getField('N16").value + this.getField("I16").value  * this.getField('P16").value;

          • 2. Re: Javascript
            George_Johnson MVP & Adobe Community Professional

            You can use the simplified field notation option with:

             

            I16 * (N16 + P16)

             

            The equivalent JavaScript could be:

             

            // Custom calculatio script

            (function () {

             

                // Get the field values, as numbers

                 var v1 = +getField("I16").value;

                var v2 = +getField("N16").value;

                var v3 = +getField("P16").value;

             

                // Set this field's value

                 event.value = v1 * (v2 + v3);

             

            })();

             

            Change the field names used above to match the actual names of your fields.

            • 3. Re: Javascript
              jn2xneri Level 1

              Thank you so much!

              • 4. Re: Javascript
                jn2xneri Level 1

                Hi,

                 

                I tried the first but didn't get a result in my field.. also I did switch to the names of the field with no spaces..

                • 5. Re: Javascript
                  Test Screen Name Most Valuable Participant

                  Note that the actual field names have to have no spaces because your script must match EXACTLY, including upper/lower case.

                   

                  Field 1

                  field 1

                  Field1

                   

                  are all entirely different.

                  • 6. Re: Javascript
                    gkaiseril MVP & Adobe Community Professional

                    Are your field names named the same as the cells in Excel?

                     

                    >> As long as your field names the same as the cell names.

                    • 7. Re: Javascript
                      jn2xneri Level 1

                      Hi Again,

                       

                      Great that worked!... If its possible... I have another question... this formula is a bit more complicated...

                       

                      =IF(I17>0,SUM(K17-R17)/K17,"")

                       

                      Thanks again for your help... promise these will be the last question lol..

                       

                      Jon

                      • 8. Re: Javascript
                        gkaiseril MVP & Adobe Community Professional

                        Acrobat forms work on field names and not cell row and column names.

                        Unlike Excel, there are very few application level functions so the user needs to write their own functions or use the built-in features of Acrobat.

                        On the form field's "Properties" "Calculate" tab the first calculation option allows one to select an action to take, "Sum", "Multiply" or "Average", and select the fields to use for the action.

                        Or you could use the Simplified Field notation if you use the JavaScript escape character to include the space character.

                        Or write a custom JavaScript calculation. You can even write your own function to Sum the values for an array field names passed as a parameter.

                         

                        I am not sure what you accomplish using the Excel Sum function to sum the result of the calculation (R17 - K17)". It only sums one value.

                         

                        function GetField(cName) {
                        // function to return the field object for a field name or report failure and return a null;
                        var oField = this.getField(cName);
                        if(oField == null) app.alert("Error accessing field " + cName, 0, 0);
                        return oField;
                        } // end GetField function

                         

                        // IF(I17>0,SUM(K17-R17)/K17,"")

                        var I17 = GetField("Field Name for I17"); // change field name as needed;

                        var K17 = GetField("Field Name for K17"); // change field name as needed;

                        var R17 = GetField("Field Name for R17"); // change field name as needed;

                        event.value = "";

                        if(Number(I17.value > 0 && Number (K17) != 0)  {

                        event.value = (K17.value - R17.value) / K17.value;

                        }

                         

                        What calculation do you want to do if the divisor, K17,  is  zero?

                        What calculation do you want to do if I17 is less than zero?

                        • 9. Re: Javascript
                          jn2xneri Level 1

                          Here's the file I've been working on.. converted it from Excel.. I'm still having a lot of issues with getting it to work

                          correctly.. for example the sales tax and the cash of lost discount buttons were supposed to turn off and on those features.. also getting the (format doesn't match...field) everytime

                          I'm putting in numbers in another field.. I think I'm almost done, but still can't seem to figure out how to get it to calculate smoothly.  Lmk if you can help. Thanks!

                           

                          Jon

                          • 10. Re: Javascript
                            try67 MVP & Adobe Community Professional

                            You can't attach files to posts here. You should upload the file to a file-sharing website (dropbox/acrobat.com/google drive/etc.) and post the link to it here.

                            • 11. Re: Javascript
                              jn2xneri Level 1

                              Hi again,

                               

                              ok.. here's the link..

                               

                              https://dl.dropboxusercontent.com/u/15950141/Lee%20Malone.pdf

                               

                              lmk if this works.  Thanks again for all your help!

                               

                              Jon

                              • 12. Re: Javascript
                                gkaiseril MVP & Adobe Community Professional

                                Have you tried to make the fields that have the formatting error to a "Format" of "None"?

                                 

                                Observe the result.

                                Examine the formula.

                                What is the result when one divides any number by zero?

                                 

                                If you want check boxes or radio buttons to be exclusionary, that is only allow one or none of the buttons to be selected at a time, one needs to make an exclusionary group, give the fields the same name but a different value when checked.

                                 

                                Once a radio button has been selected it cannot be unselected by clicking on in. The field needs to be reset or another radio button in the same group needs to be selected.

                                 

                                Using document level functions, you could simplify your coding.

                                 

                                The line that reads:

                                 

                                if(Number(I17.value > 0 && Number (K17) != 0)  {

                                 

                                Should read:

                                 

                                if(Number(I17.value > 0 && Number(K17.number) != 0)  {

                                 

                                One of your results is generating a very small value and that value is being displayed in Scientific notation and while this result is a number for the number format it is string and not a number.