SQL-Ledger Homepage

SQL-Ledger User Forum

Forum index page

Log in | Register

Back to the board
Thread view  Mix view  Order
Gregory West(R)

E-mail

Manitoba Canada,
12.08.2016, 02:38
 

Special Report (General)

I am trying to write a report, that when printed will look very much like our bank statement. Right now I have the bank rec function, but this is not overly helpful when there is an issue with balancing. For example, on a given day there would be one bank deposit of say $123,456.78. When I look at SQL-Ledger there is no such transaction. Instead I have several 100 transactions for the monthly payments from clients on outstanding invoices. This makes doing the bank rec very hard. To compound the issue, if a debit memo is applied at the time several invoices are being paid, everything works great until the bank rec, where SQL-Ledger has put in a deposit to the chequing account for the amount of the debit memo. Unfortunately, there is no corresponding deposit into the bank. And again if two debit memos are closed off in paying several invoices the issue gets even more complex.

So what I want to do is get all the AP / AR / GL transactions bundled together with the dollar mounts, debit or credit, any source information, and of course the date.

Question is, what tables would I best start looking at, and has anyone ever done anything like this before?

---
Gregory A. West
DB&W Systems Limited

DavidJ(R)

16.08.2016, 08:03

@ Gregory West

Special Report

Hi Gregory
We overcame most of these issues many years ago by learning to use SQL-L better.
Eg SQL-L bank reconciliation will "gather up" all transactions on same date with identical reference field, including identical nulls. Its a feature, not a bug. Makes it possible to pay multiple invoices for multiple customers all tallied up into a single deposit.
Most banks do that if you deposit a bundle of cheques in a single batch.
Same for payments to suppliers.
So use the reference field cleverly to distinguish your different transactions.
We have very lightly modified the code to make it look like a "Bank Statement" in our region, eg changed headings for "Withdrawal" and "Deposit".
Just cosmetic, not logic changes.
It works for us for many years.
HTH

Gregory West(R)

E-mail

Manitoba Canada,
16.08.2016, 11:07

@ DavidJ

Special Report

Thanks for the reply. Yes I can see that, and the folly of our past methods. Some fields we did not think were overly important we ignored, to our detriment. I am into the first full year end, after a rather bumpy start. Everything that could go wrong did go wrong.

Thanks for your input, we will make this coming fiscal year for better than last.

---
Gregory A. West
DB&W Systems Limited

Back to the board
Thread view  Mix view  Order
959 Postings in 314 Threads, 320 registered users, 103 users online (0 registered, 103 guests)
SQL-Ledger User Forum | Admin contact
RSS-Feed
powered by my little forum