I am trying to create a customer balance report which would show me aged customer balances using the following aging periods :-
Current
30 Days
60 days
90 Days+
My tables have the follg structure
Invoice Table ( holds all transactions entered for a customer - Invoice, Credits & Payments)
Accode ( customer Code)
InvNo
InvDate
InvTotal ( Credits and Payments are shown as negative)
InvType ( 1 if it is an Invoice, 3 if it is a credit note, 6 if it is a payment)
Noalloc ( This shows the actual amount allocated in total for a particular payment or credit note
sometimes a payment or credit is not completely allocated for ex when a customer has overpaid)
Alloc Table ( this holds the allocation details of payments or credit notes against an Invoice)
InvNo ( same as InvNo in Invoice Table)
AllocNo ( the Invoice no against which the payments is allocated)
Amount ( the amount of this allocation)
So lets say a customer has 2 invoices InvNo 100 - $500
and Inv No 101 - $1000 and they have paid $1250 which has InvNo -P1 ( as it is a payment)
The entries in the tables would be as follows:-
Invoice Table
Acccode InvNo InvDate InvTotal InvType Noalloc
cust1 100 Date1 $1000 1
cust1 101 Date2 $500 1
cust1 P1 Date3 - $1250 6 $1250
Alloc Table
InvNo AllocNo Amount
P1 100 -$1000
P1 101 -$250
I joined the two tables using a left outer join from Invoice.InvNo to Alloc.AllocNo to capture entries in
Invoice table which have no allocations. These would be Invoices which have not been paid at all.
I tried to group the report first on Accode and then on Inv no to get the amount owing on each Invoice.
The problem I run into is that I cannot get calculated summary fields to work when the Invoices have no entries in the Alloc table.
I would appreciated if anybody can setup the report for me which would give me the oustanding balances by aging as well as list all unallocated transactions which would basically add upto the customers outstanding balance.
Thanks
dlg
Start Free Trial
