Customer Running Ledger Report

[khawar]

Invoices are being stored in one table and customer receipts are being stored in customerReceipts table

i want to creat a report of customer ledger

including following fields
Date ---------- Date of transaction
Narration ---------- Invoice # if its a invoice and receipt # if its a receipt
Debit --------- value of invoice if transaction is invoice else 0
credit ---------- value of receipt if transaction is receipt else 0
Balance ---------- running balance after each transaction


i also want to include opening balance which was before that period for which ledger is being viewed

i am also including a snapshot of ledger i used to creat in ms access and VB 6
for reference

take a look at report i want to achieve this

report attached is very advanced it also includes the pending orders of that customer and balance after adding pending orders but for now i only want to creat the upper portion which includes the ledger transactions
i have changed the name for my client's privacy to "XYZ"

Thanks in advance


Khawar

[sparkey]

Re: Customer Running Ledger Report

So, basically you want an Accounts Receivable aging report ?

Might be best to create a new table called AROPN or something like that. This would have posting for all invoices out, all cash receipts in and also include all adjustments (credit or debit memo's) issued against invoices. Once invoices are paid in full, you can mark them as closed.

Your other two tables hold invoice or cash receipts journal detail. The AROPN file only holds open accounts receivable balances. As an end of month routine, you can purge out paid in full invoices after printing a monthly aging report or exporting to a history transaction file.

[khawar]

Customer Running Ledger Report

I dont want aging report i want ledger like you seen in the report i attached
it would include all invoices whether paid or unpaid for any selected period

This is called AR ledger not Aging

Aging report is what i have attached now have a look at the report attached

also I asked how to include the opening balance like you can see the first line of report in my

customer ledger report attached in my previous message which shows the summary of all the transaction before the selected period for ledger( i-e includes all invoices, receipts and credit debit notes)

Actually i am evaluating Alpha Five and wanted to see how can I creat the reports that my clients require and infact client can require any type of report so i am considering whether i can do all things I am currently doing

so please help me to learn basics and then i will be here with you at this forum to help others. But if i am not satisfied it will be difficult for me to switch to Alpha Five

Thanks

[Tom Cone Jr]

Re: Customer Running Ledger Report

Khawar,

If your invoice and payment records were present in the same table, distinguished by say a "transaction type" field, then the top part of your report is trivial. Get the beginning customer balance (presumably from a 3rd table you haven't mentoned) and then filter and sort this "combined" table so that only records for the customer are included, and sort them by transaction date.

If combining your tables is not an option, you will need to create what is often called a temporary table, populate it with records from both invoice and payment tables, and then base your report on the temporary table. The temp table can be emptied after the report has run. The process of filling the temp table, running the report, and emptying the temp table is usually very quick.

Does this make sense?

[DaveM]

Re: Customer Running Ledger Report

Khawar,

This will actually be easier in Alpha than it was with VB or (I believe) Access and you won't have to buy a report writer.

I have done both reports in Crystal and Alpha. Will be doing it again very shortly in Alpha.

I have one table for payments and one for sales(could use just one). Will utilize the 2 tables plus the customer(for the names) table in sets to accomplish the task.
Actually, mine will be harder as there will be payments due that have no table, they will be brought into an array with finance fees and payoffs and a few other figures done on the fly and then being put into a report.

.

[DaveM]

Re: Customer Running Ledger Report

This is done using Tom's temp table to give you an idea = altogether 15- 20 minutes to make tables, basic form, 2 reports. Showing one. Of course, it is a throw away for me son it's like a tire(It ain't pretty).

Lets you know you can make whatever you want(almost in alpha).

Hope Tom doesn't mind I used his idea to help you.

.

[khawar]

Customer Running Ledger Report

Can any one creat a simple example for me just using customer, invoice, invoice line item and payments tables and a ledger report for me

it would be a great favor

thanks

Khawar

[Tom Cone Jr]

Re: Customer Running Ledger Report

Khawar, help us help you. Prepare simple tables and dummy data and zip them for posting here.

[khawar]

Re: Customer Running Ledger Report

Hi tom i have created a little sample and attached it as you said

kindly make a ledger report for me which should prompt that ledger for which customer and for which time period to be viewed

for simplicity i have added data for only one customer

thanks

Khawar

[DaveM]

Re: Customer Running Ledger Report

Stuck something together fast for you. It sould give you a bit of a start to do your own. It is not sofisticated, but works and lets you see what is happening.

You did say you did this in VB??

I redid a couple tables so they had dates and a id field so look carefully. It uses a temp table for the report based on date.


.

[khawar]

Re: Customer Running Ledger Report

I have worked out the customer ledger report and it is the first time i created a report in alpha five so ignore if interface is not good looking


Thanks to Dave who suggested me the way to do it


I uploaded the sample


Code is behind the customer ledger button. I used xbasic because using saved operations was asking for customerid variable twice so i converted it to xbasic and used one variable for two operations i-e posting invoices and posting receipts to temporary table.


Also i made some changes in structure added a total invoice value field which is updated by setting posting rule to the extension field in invoiceLineItems Table to get the invoice total in the Invoice Header table for using in ledger also I removed the date column added by Dave in LineItems Table coz its not needed i just want invoice total in customer ledger and not individual items


After posting to temporary table I created a set named CustomerLedger to get customer name and based the report on that set

I also added a calculated field on report named "BalDue" to summarize the current status of customer which states whether balance is payable to customer or receivable from customer according to closing balance of customer


This is shown at the end of the report


After doing all this I added a second report named CustomerLedger2 just for learning purpose or say an alternative to the previous approach
This reports displays same data but the difference is that it opens without any form takes parameters on OpenEvent of report and shows ledger of provided CustomerId
all scripting is performed on open event of report


Now one thing left if I add the date range parameter in report so that ledger could be viewed for some specific period how to get the opening balance before that specific period which is ths sum of all Invoices less sum of all receipts before that period



Thanks

Khawar

[DaveM]

Re: Customer Running Ledger Report

Thanks to Dave who suggested me the way to do it

It was Tom Cone's method.

you can add an xbasic to set dates and make a filter in the report to handle it.

.

[khawar]

Re: Customer Running Ledger Report

Sorry thanks to Tom also and all of you participating in this board

Dave setting filter is Ok but i want previous balance for example if I view ledger for the month of May 2008 to date i also want to show what was the balance of customer before may 2008 for reference see attached report in my first post first line is showing the opening balance

thanks


Khawar

[Tom Cone Jr]

Re: Customer Running Ledger Report

Here's an approach that does not use saved append operations to populate the temp table. Unzip the attachment to an empty folder.

Run the only script you'll find on the Code page of the Control Panel. ("Simple Ledger 1 Customer"). Select Customer A, and a date range of May 1, 2008 to May 30, 2008.

The script is pretty rough and could be optimized quite a bit, but it illustrates how one might populate the temp table with field values and computed values needed in the report. In the course of doing it's work it computes the beginning (prior) balance, and the running balance after each invoice or receipt.

Hope this helps. Holler if there are questions.

Later...
I've revised the report layout a bit, to separate the transaction amounts into separate debit and credit columns. No change to the script that populates the temp table. Sample_20080517_0956.zip is the latest.

-- tom

[khawar]

Re: Customer Running Ledger Report

hi Tom first of all thanks and sorry for that i was on holiday and today i am checking your messages

Its too late at night so i will look at it tomorrow

Thanks for your king attention

Khawar