Running Balance Totals for PDF fillable form

[SG75]

Hi, I’m hoping someone could help me, I’m very new to JavaScript as you’ll be able to tell. I have converted an Excel Spreadsheet to a PDF fillable form that contains seven columns and twenty-two rows. I’m attempting to create a custom calculation script that will show a running balance of additions and deductions. I have researched and read for hours and have not been able to come up with a script that works for a running balance. Any help would be greatly appreciated.  

[try67]

How is a "running balance" different from just a regular sum?

[SG75]

Sorry if I didn’t explain well enough. I trying to show all the reconciled transactions of each row and show the current balance each time.

[try67]

So it's the sum of the current row and all previous ones?

[SG75]

Thats Correct.

[try67]

OK. What are the names of those fields?

[SG75]

1) Advances and Transfers Received, which will always be added, and subtract from 2) Items Purchased, 3) Persons Paid, 4) Vehicle Expense, 5) MISC. Expense, 6) Transfers Out7) Balance for each row.

[try67]

Can you share the actual file with us?

You can attach it to the original message using the tiny paperclip icon at the bottom when you edit it, or upload it to a file-sharing website (like Dropbox, Google Drive, Adobe Cloud, etc.), generate a share link and then post it here.

[SG75]

Here is a copy of the form. 

[SG75]

I attempted to use the below script, but I can't get it to work. I'm sure some of the code is incorrect. 

function calcBalance(){
var rowNumber
=Number(event.target.name.replace("BALANCE",""));
var BALANCE
=Number(this.getField("BALANCE+rowNumber).valueAsString);
if(BALANCE==0)event.value="";
else{
var prevBalance=0;
if(rowNumber!=1)prevBalance=Number(this.getField("BALANCE"+(rowNumber-1)).valueAsString;
event.value=BALANCE+prevBalance;
}
}

[SG75]

No, I'm still having the same issues. 

[Thom Parker]

You do not need a script for this. Use the "Sum" function on the calculation tab. 

 For each field that needs to show a calculation result, add a sum of the previous result plus the new input(s).

[SG75]

I have attempted to do that but it will not correctly show the balance of each row. And since there are also deductions as well I have attempted using simplified field notations with the same result. 

[Thom Parker]

Simplified notation seems like the perfect way to go with this. But since you have multiple calculations that depend on each other, it is very important that they are executed in the correct order. I would suggest that you check the calculation order. Also, to use Simplified Notation your field names should all be a single word, no spaces or punctuation. This is required for Acrobat to parse out the fields named from the math operations.  

In order to get the best results when you post a question, you need to provide the most complete information. Saying that the calculation doesn't work, doesn't provide much useful information. Stating that a number is being calculated, but is not the correct value. That's better. 

BTW: the script you posted is on the right track, i.e., using field naming to generalize the calcualtion. However, this is not a simple script, and unless you have some scripting proficiency, it's better to stick with a more direct solution where available. 

[SG75]

I apologize for not providing sufficient information, and not explaining myself very well. I have double checked all of my field names to make sure there were no spaces or punctuation, and I have checked the calculation order and found it to be correct as well. The calculations on each row are working and showing the correct value, but I 'm not able to produce the outcome I want on each balance row. So, looking at the form I uploaded, if the balance on row 1 is $2000.00, and I move down to row 2 and enter $500.00 into the Advances and Transfers Received text box, I'm wanting the balance on row 2 to show $2500.00. ($2000.00 from previous balance on Row 1) And if I move down to row 3 and enter a $300.00 deduction in the Vehicle Expenses text box, I want the balance text box on row 3 to show $2200.00. This is a monthly report, so I'm attempting to show a running balance of each row down the entire table. 

BTW:The script I posted was actually from the community forum that Try67 came up with in helping another member. 

Thanks, 

[Thom Parker]

So you've said that the calculations on each line are correct, but then that the balance values are not correct? or you don't have any balance calculations? If you do have balance calcs, what are the current results?

Also, you have not posted the form, it's just an image of the form. Please add the balance calcs if they are not there and then posts the form by editing the first post. That way we can see what's going wrong. 

[SG75]

Yes, all calculations on each row are correct. The problem I'm having is bringing down the previous balance of each row to show a continuous (Running) balance on the entire sheet. I have also uploaded the form on the first post. 

Thanks, 

[Thom Parker]

Well, there is a very good reason the previous balance is not showing up in the balance calculation. It's because it's not included in the calculation. Simply include the previous balance field. 

For example, on line 20:

BALANCE19+ADVANCESTRANSFERSRECEIVED20-EVIDENCEPURCHASED20-INFORMANTSPAID20-VEHICLEEXPENSE20- MISCEXPENSE20-TRANSFERSOUT20

[SG75]

I've previously attempted that as well, and once you enter a value into a text field the entire balance row reflects that same value, making the ending balance not correct. Any suggestions? 

[Thom Parker]

What are you talking about? This is exactly what you asked for.  Besides, there is no ending balance calculation.

Please provide clear explainations of exactly what is happening and exactly the results you are seeing. It does no good to say something is not correct without explaining how it is not correct.