Calculate multiple fields in columns

[David Borrink]

I designed pages with ten columns of 31 fields, and I need each column to be summed in totals at the bottom. If I go into a "total" field's properties to choose "calculate" I have the option to choose the fields above but I have to pick the 31 fields very particularly to get them. For example, if I know I need to select "Text Field 30173" through "Text Field 30203", there is a "Text Field 3018" from another page in the list order after 30180 that I know I won't want. I would hope that I'd be able to select a column's worth of Text Fields to say "add these up and put the sum in this field". But there are going to be certain fields popping up that aren't going to be wanted.

Unless I'm missing a tip or trick, is this hopelessly involved? Did I go and create nicely designed forms in InDesign with the intent to have them calculate, only to find out that it's too tedious to do in Acrobat? If I have to go through the Properties dialog and checkbox all the 31 desired fields from a long list for each sum group and be careful to not pick the ones that are seemingly out of the field name order, this could be very time-consuming. Then I'd want to calculate the row of totals to compile them into a grand total on the far right. And those field names would definitely not be in some name order.

Is there an easier way to do this? Am I missing a tool or feature?

[Thom Parker]

I don't know why you would think selecting fields from a list is more difficult than InDesign, but yes, there is an easier way, sort of, which is to name the fields so they can be treated as a group. For example if all the fields in Column 1 were named "Column1.Row1",    "Column1.Row2" etc. Then it would be easy to select them on the list.

It would also be easy to write a script to perform the sum.  This sort of issue is all the name. 

[David Borrink]

Okay, so then I could write a script in the doing the names in the "Custom Calculation Script" area rather than the "Value is the [sum] of the following fields" area (that's where I saw the list of field names).

[David Borrink]

Oh wait, I misunderstood you. So renaming the fields would be your idea. Yes, that could do it. Much easier to grab from the list when they're truly together.

[Thom Parker]

I meant both. Naming things with a consisent meaningful methodology provides many benefits. For one it makes it easy to recognize what field does what. To remember when you are scripting or selecting names off of a list. It also allows for easier and more robust scripting. 

For example, using the naming convention in my previous post, this single line of code does a sum when placed in the custom calculation script.

this.getField("Column1").getArray().forEach(function(a,i){event.value=(i?event.value:0) + a;});

In this case the "getField" function returns the entire group of fields with the "Column1" prefix.  It doesn't matter how many fields are in the group, it sums them all, which makes this technique more generic and more robust than summing a list of specific fields. It could be made event more generic by using the prefix of the current fields. So if the column total field was named "Column1.Total", then this small adjustment makes the same script work for any column.

this.getField(event.targetName.split(".").shift()).getArray().forEach(function(a,i){event.value=(i?event.value:0) + a;});

[David Borrink]

Wow. I get it. So the key is to name the fields a certain way in InDesign and use the Object > Transform > Move plus Transform Sequence Again to get a pattern going, and set up the naming there. Okay. 

[David Borrink]

I'm looking more at your last code example. So that code would work in any situation because it looks for the prefix of the field and then looks for fields based on that prefix? So that becomes a "one size fits all" universal script for tabulating columns? I assume the split(".") part means it grabs only the item before the period in the name? I'm aiming to get more understanding of Javascript but if I'm starting to percieve this line of code it's saying this:

For this field, get the first part of the name, get the array based on that name, then for each array item do the function of grabbing the value of each one as "a" and add it to "i" to get a total and put the sum of it all here in this field.

Did I interpret that right? I like to understand the code so I know what it's doing

[Thom Parker]

You've got it, sort of. The "i" argument is the index of the array being iterated over. It's used to identify the first element, which is assigned directly to the sum. All the other elements are added onto the existing sum. These elements, i.e. "a", are the field objects in the group. In the code I made the mistake of not referencing the fields object's value, so that code won't work as it. It's fixed below. 

But there is one mistake in the last script. The sum needs to exclude the total field since it is now included in the grouping. This could also have been done by adding another level of group naming.  Naming is a powerful tool for form scripting.

this.getField(event.targetName.split(".").shift()).getArray().forEach(function(a,i){if(a != event.target) event.value=(i?event.value:0) + a.value;});

[David Borrink]

Okay, that makes sense. The if(a !=event.target) excludes the total from the calculation.

Now, what if I wanted to tabulate them horizontally as well so the far right column is a total of each row? Is that possible or is it too complicated if you're setting up columns? My thought was wondering if row1 was made up of fields named column1.row1, column2.row1, column3.row1, etc., would that "split" part of the code be worked to grab the suffix part of the field name instead of the prefix? Then I'd be able to grab a horizontal group based on the row names and tabulate them on the far right column? 

If so, then the far right column could have a grand total at the bottom for the entire page with it's own script, but if that's possible, what would I use to do that tabulation? I would be using prefixes for columns, suffixes for rows... I guess I'd need to do a manual tabulation of the last column using the "select from field names" option for that one.

[Thom Parker]

You are close, but group naming is hierarchical, you only get easy access to grouped fields for the prefix. However, having a consisent  suffix means that the field names can be easily generated. 

So if all the row totals were named "total.row1" etc. the script can grab the suffix and generate field names likes this:

var cRowName = event.targetName.split(".").pop();

var nSum = 0;

for(var i=1;i<4;i++)

     nSum += this.getField("Column" + i + "." + cRowName).value;

event.value = nSum;

[David Borrink]

Yes, I thought of how getting the first row's names made in InDesign should lead to consistent field name generation! Glad to know I'm starting to get this. I'm beginning to think I can do this.

What does the shift and the pop do in both of your examples? Is that an indicator for what goes before or after the "." in the field name? I assume the i refers to the column number. What does the ++ do?

[Thom Parker]

Push and pop are Array functions in Core JavaScript. 

https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Array

So it seems like you need a primer in programming. JavaScript is a very simple language to learn, and it contains all  of the major programming concepts. Plus it's really well adopted. 

So study this reference. 

https://developer.mozilla.org/en-US/docs/Web/JavaScript

[David Borrink]

Thank you, Thom. I've been doing web development for several years and JavaScript is my weakest skill; something I've need to know more about. I'm not intimidated by it, I just haven't been able to take the time to learn it well enough. So thank you for those reference links. If I'm doing coding, I'm always wanting to understand why it works, and not say, “okay, I’ll trust you even though I don't get it”.

I’ll take a look finally get my brain wrapped around JS. It’ll be good for both Acrobat and web stuff.

[Thom Parker]

Good Luck,

  I always spend time to try and do the same thing as many ways as possible. 

[David Borrink]

That reference helped. Now I understand shift and pop better. It's grabbing certain parts by eliminating the other.

[David Borrink]

Thom, the good news is that I successfully set up new columns and put your code in the totals row. It works! Numbers are going into the totals row below. But...

The bad news is that the numbers aren't adding to a total. They're adding in sequence. If I put the numbers "1", "2", "3", "4", and "5" in the first five rows, I don't get a total of "15". I get "12345"! I'm not understanding yet how the equation works on the end of your code:

this.getField(event.targetName.split(".").shift()).getArray().forEach(function(a,i){if(a != event.target) event.value=(i?event.value:0) + a.value;});

Here is a link to the PDF you can download and see. I have several other columns started with some numbers. https://davidborrink.com/wp-content/uploads/2020/03/Household-Orig.pdf  Could you advise what would total the numbers instead? We're one step away from complete success.

[Thom Parker]

Test

[David Borrink]

<meta http-equiv="content-type" content="text/html; charset=UTF-8" />


Hi Thom, for some reason, Adobe isn't showing the thread past
response #15, but email responses seem to be working fine. I did see
your last response in my email via the subscription thread.

style="max-width: 470px;margin: 8px 8px 8px 0;">

[Thom Parker]

Just another helpful note. The "+" operator is for both adding and string concatonation. There are times when numbers are interpreted as strings. Just to be sure, it's a good idea at time to explicitly convert variables to numbers. 

For example:

var cRowName = event.targetName.split(".").pop();

var nSum = 0;

for(var i=1;i<4;i++)

     nSum += Number(this.getField("Column" + i + "." + cRowName).value);

event.value = nSum;

[David Borrink]

<meta http-equiv="content-type" content="text/html; charset=UTF-8" />


Thom,



The challenge I'm having is with the first set of code you shared,



this.getField(event.targetName.split(".").shift()).getArray().forEach(function(a,i){if(a
!= event.target) event.value=(i?event.value:0) + a.value;});

Your latest answer was referring to the other code for doing
horizontal totals, which is not being used on the PDF I shared in
my last note. I'm only working on vertical columns. Did you see my
PDF?

style="max-width: 470px;margin: 8px 8px 8px 0;">

[George_Johnson]

Using a hierarcical naming scheme (i.e., using periods in the field names) in for form fields created in InDesign is very buggy when the document is exported as an interactive PDF, though I haven't tested the most recent versions. The field names get mangled. Just a warning.

[David Borrink]

I wondered if my field name generation was a concern in this because
Thom's idea of naming them column1.row1 actually turned into
"column1.row 1" with a space after "row". I'm not thinking that was a
problem because numbers were compiling into the total field below, but
as sequential numbers, not a sum. So the collecting of numbers is
working, but the result value in the total field is not a sum, like I
want it to be.