Transactional Transactions and a Transactional Running Balance

[Josh Ormond]

Besides asking the vaguest of questions, I was hoping to get some thoughts on the pros and cons of 2 approaches toward handling a Running Balance.

 

Use-Case: In it's simplest form, a checkbook register. The key for this use-case is the sort order won't really change. Though there is the possibility of adding records into the middle of the sorted found set. Think, got my bank statement, and there is a transaction I didn't expect, or forgot to record ( or had to correct the transaction amount ).

 

Approach 1: Summary Field

I know this one has been talked about. The couple of things I know about this approach in the use-case I'm looking at is that the summary is based on the found set, which over time and enough records, can be problematic for performance.

 

Approach 2: Stored Value, Transactional Updating.

Idea being you calculate the running balance on each record when it entered. Obviously, would need to manage the balance if you insert a record into the middle of the transaction set.

 

Other thoughts: 

I was leaning more toward approach 2. Because once a month is closed and reconciled, no transactions will be entered. So even if I add in a transaction after the fact, it would only ever be for the current month, or at the most the previous month.

 

General thoughts are welcome. Pros vs Cons of each welcome. Preferences, general musings, all welcome. Things I haven't thought of....you get the idea.

[LaRetta]

Hi Josh,

 

I use both.  Since you close month-end, that data can be written to summary table after posting (your Approach 2).  Then I use aggregate (Approach 1) on the current month only (relationally filtered so it is small and fast) and simply add the two together.  This allows speed plus dynamic up-to-date totals; the best of both worlds.  

 

Posting month-end (freezing records) must be bullet-proof of course, flagging records as posted and your data-entry will probably involve transactional handling but you are up on all of that.  Security will enforce the freeze so once posted, you are free to write the posted records to your summary table without record-lock or transactional issues.  Freezing month-end will be the important one. :-)

Oh, by the way, if you DO need to add a record to a posted month (it's been known to happen), you simply write the new summary through at that time.  You won't have record-lock on Summary table nor Inventory table (where I use this same process).

 

This is a great discussion thread to open!

[Josh Ormond]

Thanks LaRetta. Great input.

 

Have you, or anyone, had a scenario where you needed to pull reports from previous months that requires the running balance there also? For example, if I need to generate a report that covers the last year. 

 

Have you run into pros/cons either storing the running balance in the transaction records? Or with a temp table or virtual list that basically recreates the running total just for the report.

[LaRetta]

I would fight like crazy NOT to have running balance in Transactions.  I would instead place a running balance in your Summaries table where you find the current years' records and displaying 12 records with running balance instead.  

 

It would of course depend upon the report; a running balance field carries little weight unless it is displayed but if you accidentally display it when all records are showing, you will regret it, as you know.  If you perform a find for the year on a Transactions layout without any references (or fields) and then switch to the report with the running balance summary, it might be doable.  There would be no way I would consider this unless LAN.  I would tend to use Summary table or virtual to display/gather what is needed for the report.  

 

It might be beneficial here to gather the values using ExecuteSQL() but then, if that were the case, you wouldn't be asking for running balance (which is a display tool).  One way or the other, you are going to have to pay the price of aggregating the data; much better to pay that price behind the scenes than when the User is requesting it.

[LaRetta]

BTW, I am sure others will have suggestions also.  I've placed running balances on LineItems before and it was too slow.  A running balance on only a year's worth of records is inaccurate anyway!

 

If your client still wants it after you warn them about potential speed issues then go ahead and then THEY can request it be removed and maybe next time they'll listen to your advice. 

[Josh Ormond]

Since every transaction that is shown in a checkbook register needs to also show the running balance, I don't think a running total in a summary table would be the approach I would go with...though I may not be visualizing the approach correctly.

 

This is where I was thinking about keeping a straight number field in each transaction that I write to only when the month closes. Once closed any additional transactions would be a rare exception. So I would only need to write data and update that running total field if I enter a transaction into a closed month. I would then need to run through the rest of the transactions after that entry and update the Running Total. And that would "never" happen outside of the current month and previous month. Any legacy data beyond that wouldn't change.

 

The "Summary" running balance would only need to be used, if even then, for the current month. I may not even need that if I use a transactional approach to update that value.

 

It may be easier to explain in a sample file. I'll try to throw one together tonight.