How to subtract one field from another and sum a range of fields

[David22141583eykw]

I created a form in Word that I converted to a fillable pdf form using the prepare form feature for a template for a mileage log. I would like to create a formula that calculates miles driven for each row where it takes the odometer reading at destination point and subtracts the odometer reading at starting point. I know how to create the formula for the first row, however my table has 60 rows and I would like to copy the formula in the first cell to the cells in the other rows. Is there anyway to do this or do I have to create the formula for the cells in the other rows one by one? Also, I would like to create a cell that totals the miles driven for all of the rows in the miles driven column that will be labeled total mileage. Is there a formula that would allow me to do that? I have attached a template of the form I created to this post. If I can have assistance on this it would be much appreciated.

[JR Boulay]

-----------

[Nesa Nurani · Accepted solution]

Here is your file with scripts included https://drive.google.com/uc?export=download&id=1Q-pZ7JGqdMZpC6tRHTtY52BilQ9Rq_C2 

For miles driven, script is in "Miles DrivenRow1" field under calculate tab -> custom calculation script.

For total mileage, script is in "Total Mileage" field under calculate tab -> custom calculation script.

Since you only have 22 fields in your sample, I set that number in scripts so you can test it, and since you said you have total of 60 rows, in both scripts change 22 to 60 for your original file.

[David22141583eykw]

Thanks!

How were you able to copy and paste the formula for miles driven in the
first row to all the other rows? Also what was the formula you used for
total miles?

[Nesa Nurani]

It's not pasted in all rows, it's only in one field but it works for other fields aswell (the 22 ones you have in sample file, to make it work for all your fields, read my post I explained how to do it).

Script for miles is in total miles field, it sums all "Miles DrivenRow" fields, do you need it to do something else?

[David22141583eykw]

I am following up on this reply about creating a mileage log that calculates how many miles driven. To follow up on this, what formula would I use that will add up the total miles driven in the miles driven column on both pages that would be in the cell labeled "total mileage" at the bottom of page 2? If I decide to add a third page or add more rows, how would I modify the formula so it will provide the total for the three pages or the additonal rows? I have attached the pdf of my mileage log to this post. If you could provide input it would much appreciated.

[Nesa Nurani]

When creating such large file you should stick to sequenced naming your field like you did on page 1, that way it would be much easier to create script, like adding  _2 to last fields on page2 (fields 23-30).

Or you can just use 'Value is the' and pick your fields.

[COAC]

Hello, I have the same issue as the original poster and would love to know what post you are referring to above in order to make the formula for the miles driven row in your example work on all 60 rows as the original poster wanted. Could anyone please help with this? I can't access the files attached in the original answer and need to know how to make a formula work without manually calculating 30+ rows in a document I have. Thank you!

[Nesa Nurani]

For all 60 fields you need to use loop. If you post field names and what calculation you need we can help. 

[COAC]

Thank you for the response! I'm very new to JavaScript and don't know how to use a loop. I have attached the form which doesn't have any calculations in it yet, it is simply the auto field detection acrobat used to get the fields in. I would need to find the product of all prices and quantities and have each total for the separate items listed in each "TOTAL" space, to eventually be able to sum them all for one grand order total at the bottom. Please let me know the steps I would need to take to make this work as I will need to make many other forms with these same calculations. I very much appreciate your help!

[COAC]

This file will work better because I realize the one attached above didn't correctly name/separate the PRICE and TOTAL.

[Nesa Nurani]

I'm sorry, I'm busy at the moment, if you don't get answer I will look at it tommorrow. 

[COAC]

Ok no problem, yes please let me know if you have a solution for this when you have a chance! Thank you so much!

[Nesa Nurani]

Once you add fields, use this in "SUB TOTAL" field as 'Custom calculation script':

var total = 0;
for(var i=1; i<=30; i++){
if(this.getField("QTY"+i).valueAsString != "" && this.getField("PRICE"+i).valueAsString != "")
this.getField("TOTAL"+i).value = Number(this.getField("QTY"+i).valueAsString)*Number(this.getField("PRICE"+i).valueAsString);
else
this.getField("TOTAL"+i).value = "";
if(this.getField("TOTAL"+i).valueAsString != "")
total += Number(this.getField("TOTAL"+i).valueAsString);}
event.value = total;

[COAC]

Wow thank you so much!! That worked, I appreciate your help! Side question, are there any resources/yt channels/courses you would recommend for learning Javascript for scripting to help with problems like this? I am very new to it but want to learn more of an understanding of the language in order to so more like this, if there is anything specific you think is a great resource. Thanks so much again!