Keeping Invoice History

[Bob Elliott]

Hi, it�s me again!

Thanks to all the great people on this message board I have my Invoice Application up and running and it works great! Now I would like to enhance it! I would like to keep a history of customer invoice for a yearly report for each customer! Is this a place to do posting? I have a invoice table with the following fields in it: Child_id, customer_id, invoice_no, invoice_date_ time_kept (hours & minutes), hourly_rate, invoice_total and grand_total� This table is used in a set that includes a �customer table and a child table�! How can I keep this information on the invoice because after they are paid they are deleted! This is a Childcare Application and does not generate a great number of invoice, but to time consuming to re-enter the information to a table for history!

[Jerry Hartness]

RE: Keeping Invoice History

Bob,
I do this monthly, After the invoices have been paid
I append the paid invoices to another tbl (called
History), then I delete the invoices. You can
base your reports on your history file just as you
do the regular file

Jerry

[Bill Warner]

RE: Keeping Invoice History

Bob, why are you deleting the invoices? If you don't, you can create reports directly from the invoicing set.

Jerry's idea would work, too. However, I have quite a few customers that do invoicing, and none of them delete the invoices. Is there a reason to do this (particularly since you said there aren't many)??

[Dan Blank]

RE: Keeping Invoice History

I would not delete any invoices. Maybe make a paid field and then filter so the record will not show.

For the History, Invert the set....Make the Customer database your Parent and link to the items table based on the invoice number. Then when a customer name is selected you will see a list of all the invoices associated with that customer.

Hope this helps
Dan

[Ken L Herrold]

RE: Keeping Invoice History

Bob:

I agree with the other posters. If you're willing to give up the disk space [very very nominal] to store nearly all of the inv information somewhere else don't delete them. Leave them where they are; have a field to mark them paid [if that's why you're deleting them] and KEEP em.

If you don't want to see them again, put a filter on the indexes excluding the paid ones.

Your reports at the end of the year for each customer could have information from any or all of those invoice fields right where they are!

Kenno

[Ohlen Cartmell]

RE: Keeping Invoice History

Ah the power of Alpha 5; always more than one way to skin the cat.

I opted to leave all invoices in the original file. As the file grew, printing reports, etc. got slower and slower as Alpha applied the report filter to all the records. I finally created interim tables for the reports and operations to zap and then fill the interim tables with the required data for the reports. So far, this has worked well, keeps all data in it's original state while producing reports in reasonable time.

[JohnZaleski]

RE: Keeping Invoice History

The biggest argument for keeping all transactions in the original tables is the "keep it simple" philosophy. Although simplicity is generally a good idea, I've found that unposted transaction tables that feed into other tables and then get purged is a very good way of inserting strong accounting controls into an application. I've worked closely with some of the good multi-user mid range accounting software (MAS90 and others) and for the most part they have transaction entry tables that ultimately get purged. It ultimately gets down to what the application is all about, and what is the best design that will achieve the desired results.

[CALocklin]

RE: Keeping Invoice History

Thanks for the interesting comment, John. Just more proof that there is always at least one exception to every rule. One I ran into recently was street addresses - generally there is no reason to separate the number from the street name but, for two commercial real estated companies I've worked with, it is almost a necessity.

[Mike Konoff]

RE: Keeping Invoice History

I'm a non-deleter too.

When a customer sends in a check I enter that into a "Payable" table, which posts the "amount paid" to the field in the invoice appropriately called "amnt_paid".

Works great...thanks to Alpha.

Mike

[Thomas Henkel]

RE: Keeping Invoice History

What do you do when an invoice is only partially paid???

I agree that keeping the invoices is proper. Purge them after copying to a history database, but only after a year or so. The accountants really like that. An auditor might get a little concerned when you delete an invoice without creating a history record.

You may want to set up fields on the customer database for total invoice amt, total paid, etc. these fields would be posted to for each invoice cut, and for each payment.

We are great prpopnents of creating functionally-specific seets. Alpha's flexibility allows this and makes our work much easier. If your main set gets too cumbersome for reports or whatever, build a new set just with the absolutely necessary tables. Invert their order, arrange them for easier flow. That is the beauty of Alpha!

[JohnZaleski]

RE: Keeping Invoice History

Tom,

I fully agree with your take on sets. I've gotten to the point that I don't consider any set my main set. I have data entry sets, data inquiry sets, reporting sets and others. Thanks for the term "functionally specific".

As to the deleting of records from tables. I see a lot of designs that attempt to use a single table for too many purposes. Let me run thru the sequence that some of the leading mid-range accounting packages use. For billing and receivables they use an invoice data entry table(s). After printing invoices and a mandatory sales journal they have a batch update process. This process among other things:

1) Creates an open invoice record into an AR ledger table.
This table is usually designed to hold all payments, credits and others. It is the source of agings etc.
2) An invoice history record that is an exact reflection of the original invoice. Some packages put a batchID on every historical invoice to allow journals to be reprinted.
3) They then purge the data entry table. No info has been lost, it has moved to other tables that may be more efficiently designed for particluar functions.

The idea is that:
1) The original invoice is finalized and can never be changed, not even by the cleverest of users (accounting controls).
2) A journal has been printed reflecting the completed financial transactions (accountants love that)
3) Any payment or adjustment will be a unique transaction that will go through the same process as the original invoice.

I am not saying that this is the only way to do things, but if you have a large multi-user system with lots of people entering transactions that have financial implications, it can be a good way of keeping the flow of info into a general ledger both accuratee and auditable.

John

[Jim Chapman]

RE: Keeping Invoice History

Hello Ohlen,

>>As the file grew, printing reports, etc. got slower and slower as Alpha applied the report filter to all the records

[Jerry Hartness]

RE: Keeping Invoice History

OK, OK, Keeping your invoices is a good thing, On my
budget report I copy only the records I need (for the
budget) to another tbl (using a filter) and then
create my reports from the new tbl. Each time I do
this I overwrite the old tbl and the report is current
and up to date. The reason I delete the paid invoices
from the current tbl is so I don't have so many to
scroll, browse, search for etc to mark them paid. My
history file is a set based on the vendors and the
invoice file is attached. Works great here, but we all
are great Alpha programmers and know how to make it
work our way.

Jerry