• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
0

Needs average of four columns of fields but ignore zeros

New Here ,
Oct 09, 2020 Oct 09, 2020

Copy link to clipboard

Copied

I have a form with four individual columns that need an average calculated, but with the zeros ignored.  Below is what it looks like.  The fields in each column are either E.  R2.  R3.  R4., wish sequential numbers after each dot. The averages need to calculate in the DLA  row. (Keep in mind this page two, there are numbers on the other, bringing up the sum at the bottom). 

 

Screenshot 2020-10-09 070232.jpg

 

I've come across different codes but I am understanding they have to be a document level. How do I do that when I need four different averages? 

 

This is what I have found. Document-level script: 

 

function calcAverage(aFields, bIgnoreBlanks, bIgnoreZeros) {

    var total = 0;

    var n = 0;

    for (var i in aFields) {

        var f = this.getField(aFields);

        if (f==null) {

            console.println("Error! Can't locate a field called: " + aFields);

            continue;

        }

        if (f.valueAsString=="" && bIgnoreBlank) continue;

        var v = Number(f.valueAsString);

        if (isNaN(v)) continue;

        if (v==0 && bIgnoreZeros) continue;

        total+=v;

        n++;

    }

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

    else event.value = total/n;

}

 

Then for the DLA boxes, I am guessing I am supposed to run this script, but I am not sure how to write my form field names. For the first column, would it only "R1"?

calcAverage(["Knowledge1", "Knowledge2", "Knowledge3", "Knowledge4", "Knowledge5", "Knowledge6"], true, true);

 

So, questions - are these the correct scripts I need to be using and how do I make it work for my document? I have basic knowledge of JS. I really just need someone to tell me what to put and where to put it, and what I need to replace with my field names. Please and thank you so much! 

TOPICS
Acrobat SDK and JavaScript

Views

828

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines

correct answers 1 Correct answer

Community Expert , Oct 10, 2020 Oct 10, 2020

On second thought,

 

If you're still looking to use document-level script this is how I am using it in my PDFs

 

 

// USE THIS FUNCTION AS  DOCUMENT LEVEL SCRIPT TO GET THE AVERAGE USING THE  "getArray()"  METHOD

function calcAverage(aNumFields) {
sum = 0;
for (j =0; j < aNumFields.length; j++) sum += aNumFields[j].value;
event.value = util.printf("%0.2f",  sum/aNumFields.length);
}


// CALL THE FUNCTION FROM THE TOTAL AVERAGE DLA FIELDS THAT CORRESPOND TO EACH COLUMN

//This will get the aver
...

Votes

Translate

Translate
Community Expert ,
Oct 09, 2020 Oct 09, 2020

Copy link to clipboard

Copied

Yes, this is the correct script to use (which I wrote, by the way!)...

 

You need to place the function part in a doc-level script (Tools - JavaScript - Document Scripts), and the call to it as the custom Calculation script of your Average field. If your field names are R1, R2, etc. then change that call to:

calcAverage(["R1", "R2", "R3", "R4"], true, true);

That's all there is to it.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Oct 09, 2020 Oct 09, 2020

Copy link to clipboard

Copied

Thank you for responding so quickly! You are always so helpful!

 

So, I input the document script above as it is written at the document level. Then I only need the average of each column not a total of all of four columns, so I tried that calculation script in one of the DLA text fields, but only used "R1" and it didn't work. Then I created a separate field with a calculation script as you have it written above, and nothing happened. I must be doing something wrong but I can't figure it out. Do I need to change out any of the names in the document level script with my fields? aFields means all fields, yes? 

 

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Oct 09, 2020 Oct 09, 2020

Copy link to clipboard

Copied

You would call this script from the custom calculation script of your respective DLA field. Let's say you are working on E.DLA (we don't know what your naming convention for the DLA fields are, so I just assume that's the average field for the E column). 

You would call it with all field names that you want to average in a field array as you've shown in the example. I don't know how many rows you have, so let's assume it's 30, you would need to create an array with these 30 field names, and then call the script:

var fieldArray = [];
for (var i=0; i<30; i++) {
    fieldArray.push("E." + i);
}
calcAverage(fieldArray, true, true);

 

If you have a different number of rows, you need to edit the for expression. 

 

You would add something corresponding to all four DLA fields. 

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Oct 09, 2020 Oct 09, 2020

Copy link to clipboard

Copied

There are 20 rows. So, in each of the DLA fields it would look something like this? This is for the first eval column: 

 

var fieldArray = [E];

for (var i=0; i<20; i++) {

         fieldArray.push(E + i);

}

calcAverage(E, true, true);

 

 

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Oct 09, 2020 Oct 09, 2020

Copy link to clipboard

Copied

Your script is different from what I posted. Use my script. You don't have to make any changes for the "E" column, just for the ohter three columns. Are your fields named E.0 ... E.19 or E.1 ... E.20? My script assumes the first case. If it's the second, the for loop need to be changed:

 

for (var i=1; i<=20; i++) {

 

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Oct 12, 2020 Oct 12, 2020

Copy link to clipboard

Copied

Hi, I entered the code and only changed the variables as you said, but nothing is happening. I tried it with and without the document script that Try67 wrote thinking that might be the problem, but nothing is calculating.  I even tried using different fields in a different form. The only thing that happened was at one point is some code appeared in the box  - something like R.1# or something, but it only ever averaged one even though I entered something into every preceding field. I must have something wrong or doing something wrong. I've checked the calculation order. I am truly at a loss. 

 

var fieldArray = [];

for (var i=1; i<20; i++) {

    fieldArray.push("E." + i);

}

calcAverage(fieldArray, true, true);

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Oct 12, 2020 Oct 12, 2020

Copy link to clipboard

Copied

Can you share the form?

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Oct 13, 2020 Oct 13, 2020

Copy link to clipboard

Copied

As Bernd said, having access to your form would help. In general however, whenever your form is not doing what it's supposed to be doing, check the JavaScript console for errors. You can bring it up with CTRL-J or Cmd-J on a Mac.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Oct 15, 2020 Oct 15, 2020

Copy link to clipboard

Copied

@Bernd Alheit @Karl Heinz Kremer 

Sure!

https://www.dropbox.com/s/3q5blmwy0dubggo/1C2i-3%20-%20DLA-20%20-%20DD-IDD%20-%20copy.pdf?dl=0

 

I did run the JS debugger and it says Reference Error: calcAverage is not defined. But I do not understand what I need to change from the script that was provided. I only have the code in field Text6 at the bottom of the first column on the second page. I've tried so many things, I didn't want to enter the code in each box of the DLA row if it wasn't working. Any help is appreciated! Thanks!

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Oct 15, 2020 Oct 15, 2020

Copy link to clipboard

Copied

I just realized you probably won't be able to do anything with that link other than view the file, which doesn't help. 

https://drive.google.com/file/d/1SZ_e_njzZvnZhnKGTIidpi9R6I4lcaM7/view?usp=sharing

 

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Oct 15, 2020 Oct 15, 2020

Copy link to clipboard

Copied

I only have access to my phone right now, so cannot take a look at your file. The error message suggests that you did not define the function calcAverage in a document level script.  

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Oct 15, 2020 Oct 15, 2020

Copy link to clipboard

Copied

That's okay! Thank you for your help! I'm a person who can't quit until I find the solution. I wanted to know to learn the "why". But I ended up diving more into the manual @ls_rbls referenced below and then I found the issue. It's now working with two possible options for the outcome I need. I just needed to learn how to customize the script to my needs. 

 

Solution 1

Document-level script

function myAverageFunction(aNumFields)

{

// n = number of fields that have a numerical value

var n=0, sum = 0;

 for ( var i=0; i<aNumFields.length; i++) {

var v = this.getField(aNumFields[i]).value;

 if ( v != "" ) {

 n++;

 sum += v;

 }

 }

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

 else event.value = sum/n;

}

 

custom calc script: 

var aNumFields = new Array(“R2.0", “R2.1", “R2.2", “R2.3", “R2.4", “R2.5", “R2.6", “R2.7", “R2.8", “R2.9", “R2.10", “R2.11", “R2.12", “R2.13", “R2.14", “R2.15", “R2.16", “R2.17", “R2.18", “R2.19");

myAverageFunction(aNumFields);

 

That was the most tedious process until I realized the next option was much simpler for what I needed to do. 

 

Solution 2

I can't remember who wrote this code or where I copied it from, but it's not mine and I apologize I can't give credit to whoever wrote it. 

 

custom calc script: 

// Initialize variables

var i, v, num = 0, sum = 0;

// Loop through the input fields

for (i = 0; i <= 19; i++) {

  v = +getField("R2.” + i).value;

  if (v !== 0) {

  // increment the non-blank/zero field counter

  num++;

  // add the field value to the running total

  sum += v;

  }

}

// Calculate the average

if (num) {

  event.value = sum / num;

} else {

  // All fields are empty, so set to blank

  event.value = "";

}

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Oct 09, 2020 Oct 09, 2020

Copy link to clipboard

Copied

You don't necessarily need a document-level script for this to work since you can run a custom calculation script on each of the DLA averaging fields.

 

The script that you found since to be a modification of a script example that is provided  in the Adobe Acrobat DC SDK Using JavaScript in Forms,  Developing Acrobat® Applications Using JavaScript™ , "Task-based topics", on page 85

 

The most important detail is that you already created  parent field name and broke it down hierarchically with its children fields.

 

For example, "Eval1" is the parent name, and the children names are pre-fixed by the parent name and indexed in a hierarchy like "Eval1.0", "Eval1.1", "Eval1.2", etc. This makes it easier to run a script as defined in the link I provided above where you get an array of fields to perform a custom calculation based on the values of each field through an array.

 

This is explained the getArray() section with the first example on Page 146 of the Adobe Acrobat SDK JavaScript, API JavaScript™ for Acrobat® API Reference, "Field methods"

 

So for the DLA total of the Eval1 average field you can use something like this (which worked good on my end):

 

 

var f = this.getField("Eval1");
var aNumFields = f.getArray();

sum = 0.0;
for (j =0; j < aNumFields.length; j++) sum += aNumFields[j].value;
// sum contains the sum of all the children fields prefixed as "Eval1"

var n =  sum/aNumFields.length;
// var n calculates the average


event.value = util.printf("%0.2f", n);
//util.printf formats the total in this field to 2 decimals to the right of the period and rounds off the total number

 

 

 

 

 

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Oct 09, 2020 Oct 09, 2020

Copy link to clipboard

Copied

Sorry guys.

 

Seems like I was typing my long verbage answer at the same time as Try67 and Karl.

 

Please disregard. 

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Oct 10, 2020 Oct 10, 2020

Copy link to clipboard

Copied

On second thought,

 

If you're still looking to use document-level script this is how I am using it in my PDFs

 

 

// USE THIS FUNCTION AS  DOCUMENT LEVEL SCRIPT TO GET THE AVERAGE USING THE  "getArray()"  METHOD

function calcAverage(aNumFields) {
sum = 0;
for (j =0; j < aNumFields.length; j++) sum += aNumFields[j].value;
event.value = util.printf("%0.2f",  sum/aNumFields.length);
}


// CALL THE FUNCTION FROM THE TOTAL AVERAGE DLA FIELDS THAT CORRESPOND TO EACH COLUMN

//This will get the average of all Eval1 children fileds
var f = this.getField("E");
calcAverage(aNumFields);

//This will get the average of all R2 children fileds 
var f = this.getField("R2");
calcAverage(aNumFields);

//This will get the average of all R3 children fileds
var f = this.getField("R3");
calcAverage(aNumFields);

//This will get the average of all R4 children fileds 
var f = this.getField("R4");
calcAverage(aNumFields);




//You can also rename the same  function and slighlty modify it to totalize the SUM of all the values in the "SUM (max. 140)" fields

function calcSUM(aNumFields) {
sum = 0;
for (j =0; j < aNumFields.length; j++) sum += aNumFields[j].value;
event.value = sum;
}

// THEN CALL THE FUNCTION FROM THE TOTAL  "SUM (max. 140)"  FIELDS THAT CORRESPOND TO EACH COLUMN

//This will get the SUM of all Eval1 children fileds
var f = this.getField("E");
calcSUM(aNumFields);

//This will get the SUM of all R2 children fileds
var f = this.getField("R2");
calcSUM(aNumFields);

//This will get the SUM of all R3 children fileds
var f = this.getField("R3");
calcSUM(aNumFields);

//This will get the SUM of all R4 children fileds
var f = this.getField("R4");
calcSUM(aNumFields);

 

 

Is very simple.

 

I am also learning Acrobat JavaScript so I always approach the problem as simple as possible. And like I mentioned before, since you already have the children  field names organized with an indexed hierarchy,  the getArray()  method works perfectly in this particular case. It also minimize the use of unnecessary conditional statements.

 

To recap, my example above assumes that  all "E" fields fall in the same column for "Eval1" , for example. And the same assumption for the R2, R3, R4 children fields under those columns.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Dec 03, 2021 Dec 03, 2021

Copy link to clipboard

Copied

LATEST

To anyone who finds this code, there's a small mistake in it.

Change this line:

if (f.valueAsString=="" && bIgnoreBlank) continue;

To:

if (f.valueAsString=="" && bIgnoreBlanks) continue;

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines