Jump to content
  • 0

Running Balance


Accountability
 Share

Question

Does anyone have an SQL or JavaScript for a calculated field to accomplish the following:

 

Want to be able to have record level running total like you would have in bank statement. For example...

 

Starting Balance $200.00

__________________________________________

Date-------------Debit/Credit---------Amount------Balance

__________________________________________

10/27/2014----Check # 101--------$20.00-----$180.00

10/30/2014----Check # 102--------$15.00-----$165.00

11/03/2014----Deposit-------------($100.00)---$265.00

11/04/2014----Check #103---------$65.00-----$200.00

ETC....

 

Would like to have a running balance in a report so the customer can know what their balance was on a given day. I wrote a full accounting application for county government back in 1998 and it is still in use today. I would love to completely recreate the program with Caspio as the back-end. I only have two obstacles preventing me from moving forward with this large task. Obstacle one is the running balance which I am inquiring about in this post and obstacle two is the ability to have custom print output. In my current (OLD APP) I can print checks and do bank reconciliation. 

 

Any suggestions would be very much appreciated.... Thanks

Link to comment
Share on other sites

10 answers to this question

Recommended Posts

  • 0

Hello Accountability,

 

I think, you can try the following steps:

- Add a Calculated field with a formula (if it is required);

- Add a Header&Footer element;

- In the Footer element, click the "Source" button and insert the following script:

<script language="javascript" type="text/javascript">
var column_order = 4;
var tbl = document.getElementsByTagName("table")[0];
var rows = tbl.getElementsByTagName('tr');

var balance = parseFloat(rows[1].getElementsByTagName('td')[column_order].innerHTML);
for (var row=2; row<rows.length;row++)
{     
   var cels = rows[row].getElementsByTagName('td');
   balance = parseFloat(balance) + parseFloat(cels[column_order].innerHTML);
   cels[column_order].innerHTML= balance;
}
</script>

Please, insert the number of your column instead of "4" in the line "var column_order = 4;" and please remember, that the first column is "0", the second is "1" and so on.

 

Script changes values of the Calculated field column as follows:

First row = the value of the first row;

Second row =  the value of the first row + the value of the second row;

Third row =  the value of the first row + the value of the second row + the value of the third row

And so on.

 

I hope, it helps.

Link to comment
Share on other sites

  • 0

Hello Caspio Rockstar. I've tested the above JS for calculating a running balance and it works great! However, when adding "Formating" to the Calculated Field (Currency for example) the Calculated Field no longer performs the Calculation and the following letters fill each Calculated Field (NaN). Is there a modification to the above JS to include a "currency" masking for the Calculated Field?

Link to comment
Share on other sites

  • 0

Hi, 

Is there a newer version of this script? Can it be used to calculate cumulative time difference? For example, I have timestamps for records entered into my table and need to have the difference between the new stamp and the previous timestamp for logging and auditing purposes for each unique ID.  All stamps are entered into the same column. I'm already calculating the difference between start and end times which is working perfectly using the date diff function. However, the difference that I'd like to capture name all the data is in the same column. 

(Column) Time_Stamp

Unique ID: 25

Row 1) 01/28/2018 18:06:32

Row  2) 01/29/2018 18:06: 32

Row 3) 01/29/2018 19:06:32

The Time difference would a follows: 

Row 1) Null (No previous time_stamps for Unique ID 25)

Row 2) 24 Hrs. (Row 2 - Row 1)

Row 3) 1 Hr.  (Row 3-Row2) 

and so on ....

 I'd appreciate any feedback that can be provided.

 

Thanks,

Bre  

Link to comment
Share on other sites

  • 0

In an aggregation you can try to get the value from the table for instance:

SELECT SUM (Balance) FROM Table Balance_Table WHERE Customer_ID = target.[@field:Customer_ID] AND Date = [@date]

[@field:Customer_ID] needs to be selected from picker and [@date] is being passed from search

Link to comment
Share on other sites

  • 0

Hi All!

I would like to share a solution to get the running balance without using JavaScript or Triggered Actions. You can just use a Calculated field.

(SELECT SUM(ISNULL(CASE WHEN [@field:Debit_Credit] = 'Deposit' Then [@field:Balance] ELSE 0-[@field:Balance] END,0) - ISNULL([@field:Decrease],0)) FROM Running_Balance WHERE Code= target.[@field:Code] AND ID <= target.[@field:ID])

The formula is based on the initial inquiry in this forum thread.

 

Hope this helps!

Link to comment
Share on other sites

  • 0

Hello! I just wanted to add this to the answers above. If any case that you would like to get the running balance using two fields (debit and credit amount), you may use this formula below. Just an additional note, If you are sorting the records by Transaction Date, you may use a Timestamp as the Data Type rather than a Date/Time field. To calculate the running balance, each record should have a difference and should be in order. Otherwise, you can add a field and set it to Autonumber. It is important to know how the records are arranged on your table. 

(SELECT SUM(IsNull([@field:Credit_Amount],0) - IsNull([@field:Debit_Amount],0)) FROM Table WHERE ID= target.[@field:ID] AND Autonumber <= target.[@field:Autonumber])

 

Cheers!

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
 Share

×
×
  • Create New...