7 Replies Latest reply on May 3, 2013 10:15 AM by bb1519

    Sumif function in javascript?

    bb1519

      Hi all,

      I have a very simple form that is to be used for people taking payments and I would like to have subtotals of each payment method on the form.

       

      Example:

      Cash     50.00

      Visa     100.00

      Cash     75.00

      Visa     200.00    

       

      Total Cash:  125.00

      Total Visa:  300.00

      Total Payments:  425.00

       

      There could be up to a couple of hundred entries and management wants to use a pdf fillable rather than excel.  Can I use a script similar to the sumif function in Excel? 

       

      Thanks for any help.  And I did read the answer to a similar question in the Livecycle forums but I'm afraid they kind of lost me with the answer.

       

      Thanks again.

      Brenda

        • 1. Re: Sumif function in javascript?
          gkaiseril MVP & Adobe Community Professional

          With Acrobat JavaScript you will have to write your own function for this task. How exactly this is programmed will depend upon how you name the fields.With multiple fields and multiple payment options there are multiple ways to solve this problem and possibly one that is very dynamic and will automatically adjust for the addition of new fields or the removal of fields and automatically adjust for additional payment methods.

          1 person found this helpful
          • 2. Re: Sumif function in javascript?
            gkaiseril MVP & Adobe Community Professional

            A sum if equal function using hierarchal field names:

             

            function SumIfEq(cIndex, cRange, cValue) {
            // function to sum cRange for item in cIndex that equals  cValue;
            // using hierarchical fields;
            var nSum = 0; // variable to sum payment type;
            // using hierarchical field names;
            // get the payment type and amount high level field objects;
            var oPayType = this.getField(cIndex);
            var oAmount = this.getField(cRange);
            // convert the highlevel field objects to arrays;
            var aPayType = oPayType.getArray();
            var aAmount = oAmount.getArray();
            // verify that there are an equal number of fields;
            if(aPayType.length != aAmount.length) {
            app.alert("The number of payment type fields and amount fields must be equal!", 0, 0);
            } else {
            // loop through the fields
            for(i = 0; i < aPayType.length; i++) {
            console.println(aPayType[i].value);
            if(aPayType[i].value == cValue) {
            // add the payment to the sum;
            nSum += Number(aAmount[i].value);
            } // end match payment type;
            } // end loop through the fields;
            } // end field length;
            return nSum; // return computed sum;
            } // end SumIfEq function

            // custom calculation script using the SumIfEq function;
            // call sum if equal for payment "Type", for "Amount", equal to "Visa";
            event.value = SumIfEq("Type", "Amount", "Visa");
            // end custom calculation script;

             

             

            The Sum If Equal function using an array of field names:

             

            function SumIfEq(aIndex, aRange, cValue) {
            // function to sum cRange for item in cIndex that equals  cValue;
            // do not change code below;
            var nSum = 0; // variable to sum payment type;
            // verify that there are an equal number of fields;
            if(aIndex.length != aRange.length) {
               app.alert("The number of payment type fields and amount fields must be equal!", 0, 0);
               } else {
               // loop through the fields
               for(i = 0; i < aIndex.length; i++) {
               if(this.getField(aIndex[i]).value == cValue) {
                  // add the payment to the sum;
                  nSum += Number(this.getField(aRange[i]).value);
                  } // end match payment type;
               } // end loop through the fields;
            } // end field length;
            return nSum; // return computed sum;
            } // end SumIfEq function

            // custom calculation script using the SumIfEq function;
            // using arrays of field names;
            var aIndex = new Array("Type.0", "Type.1", "Type.2", "Type.3", "Type.4", "Type.5", "Type.6", "Type.7", "Type.8", "Type.9");
            var aRange = new Array("Amount.0", "Amount.1", "Amount.2", "Amount.3", "Amount.4", "Amount.5", "Amount.6", "Amount.7", "Amount.8", "Amount.9"); 
            // call sum if equal for payment "Type", for "Amount", equal to "Visa";
            event.value = SumIfEq(aIndex, aRange, "Visa");
            // end custom calculation script;

            • 3. Re: Sumif function in javascript?
              bb1519 Level 1

              Excellent .  I used the second script and it worked brilliantly.  Honestly although I have picked up scripts online and changed them to suit my needs, I haven't any formal training in javascript so I picked the one that I could understand what it was doing .   If you have time to elaborate on exactly what each script is doing and if one way is better than the other, I am always looking to learn!  If not, no problem.  Thanks very much for the help.  Regards, Brenda

              • 4. Re: Sumif function in javascript?
                gkaiseril MVP & Adobe Community Professional

                The script has comments describing for what it is being done.

                • 5. Re: Sumif function in javascript?
                  bb1519 Level 1

                  Yes I did see the comments and I will walk through it a step at a time and figure it out.  Thanks for the script.  Regards, Brenda

                  • 6. Re: Sumif function in javascript?
                    gkaiseril MVP & Adobe Community Professional

                    I use 2 arrays of field names, one for the index column and the second for the selection of the values. The listing of the file names must be in the same order so the first name in the index column is matched to the first amount for that row.

                     

                    I can determine the number of items to compare by accessing the length property of the 2 arrays.

                     

                    I can then loop through the array of index field name and get each items value. I then compare the value for an index row to the criteria to match. If there is a match of values, then the amount for that row is added to the sum. After each row has been processed the sum hold the total for the criteria item.

                     

                    With a function, just like in Algebra, there is or are inputs for processing and a result is returned. So if one passes an array of field names for the index, an array of field names of values in the same order as the index, and a criteria for selection the function processes each index field and totals the amounts for the specified criteria.

                     

                    The first example uses the hierarchical fields top level and converts that field object into an array of it's subfields and then performs the processing.

                    • 7. Re: Sumif function in javascript?
                      bb1519 Level 1

                      That's helpful...thx.  So I tried the first script and it works as well.  Am I understanding it correctly in that for the second example I would have to revise the script if I added more fields, whereas in the first example I wouldn't?

                      Also......to throw another question at you, if I wanted to add another column to the form and have yet another condition on the subtotals is that possible?

                      Example:

                       

                      Visa     A/R          250.00

                      Visa     Non A/R   50.00

                      Cash     Non A/R     50.00    

                      Cash     A/R          100.00

                      Visa     A/R          125.00

                       

                      Total A/R Sales:

                      Visa     $375.00

                      Cash     $100.00

                       

                      Total Non A/R Sales:

                      Visa     $50.00

                      Cash     $50.00

                       

                      Sorry.....that curveball was just thrown at me after getting the form all done using your script!    thx Brenda