I usually maintain a Payments (or Credits, not not including goods and services for sale) table. It can include many “types” of payments and charges. It’s needed to maintain an audit trail for A/R. I know it’s not, er, normal, but I also maintain Total and Amt Due fields in the Invoice table. Along with these I also keep functions which can scan these fields, identify mis-matches and reconcile them to the Lines and Payments records for Invoices.
Some reports often hundreds of thousands of invoices and millions of invoice lines. I can’t imagine I’d live long enough for 4D to build them without an [Invoice] Total field. Bill William W. Weale Business Owners Support, LLC. Operations Analysis MIS Advising Decision Support Systems > On Oct 14, 2016, at 12:20 PM, Kirk Brooks <lists.k...@gmail.com> wrote: > > I'm going to fork this thread into a separate discussion because I think > it's a good topic and way far afield of the OP. > > I have never been at a point in my development career when I haven't had at > least one project that had invoices involved. Most of my databases had the > 'classic' structure of an invoice table and a line items table. You add > line items, sum up them up and write the totals into the invoice record. > Fine until you start trying to track payments also. Hmm. So now I add > another couple of fields for 'payments' and 'balance due'. But now it's a > de-normalized situation: the sum of line items and sum of payments may not > be so static. What if a payment is being applied but the invoice record is > locked? I can't update the invoice so does that mean the payment can't be > entered? What do we do with the payment? And so on. > > Lot's of high end accounting databases started dealing with invoices by > looking up the values when needed. ie. > > Invoice_get_total(invoice id) = sum Invoice Lines > Invoice_get_balDue(invoice id) = sum Invoice Lines minus sum Payments > > > I tried that approach in 4D years ago and it was just too slow to be > feasible when building reports. Or maybe I wasn't as good at setting up > structures back then. It is undeniably faster to write reports from static > data. But in a dynamic environment (where someone is literally watching an > invoice for the balance due field to change) it gets more complicated. > > Another problem with the lookup approach is historical data. You spend a > lot of computer resources looking up static data that hasn't changed in a > long time and really won't ever change again. That's not what you want. > > My approach to invoices currently is this: > > - There are 'active' and 'inactive' invoices. Inactive ones have the > totals written to the record. Active ones don't. > - Invoice records are never opened in a user window. I make a dialog of > the invoice data but not the actual record. > > - Changes are submitted as variables, submitted to a single method for > validation and updating the record. > > I think this gives me the best of both worlds. A pref lets the user define > how long to leave an invoice in the 'active' state after it's paid. A month > or two is a usually enough. Plus, it's not like this is a one-time only > designation. An invoice can be changed from active to inactive and back as > needed, for some reason. > > Getter methods look at the invoice record to know where to get the data. > Like so: > > // Invoice_get_total(invoice id) > if(invoice_active) > > $0:= lookup the line items > > else > > $0:=[invoices]total > > end if > > > Editing invoices is restricted in various ways but the basics are pretty > much anyone can view the record, you can submit changes if you have > permission and those changes are managed by a central method. > > The final part is a background method that loads on startup, runs once a > day and looks for paid invoices that can be made inactive. > > This is all probably overkill for single user applications. In busy > multi-user dbs, where people creating invoices aren't necessarily the > people entering payments or where payments arrive from external systems, > this is a pretty good approach. > > On Fri, Oct 14, 2016 at 7:57 AM, Chip Scheide <4d_o...@pghrepository.org> > wrote: > >> Invoice, and invoice item >> - Invoice item is changed, which changes a total on the invoice >> both are locked to other users, until the transaction is completed. >> >> On Fri, 14 Oct 2016 16:49:10 +0200, Arnaud de Montard wrote: >>> >>>> Le 14 oct. 2016 à 16:11, Chip Scheide <4d_o...@pghrepository.org> a >>>> écrit : >>>> >>>> yes. >>>> But then : >>>> - if the 1 user changed a value, everyone probably needs to know that >>>> the record is being modified and whatever value(s) are displayed may >>>> not be correct. >> > > -- > Kirk Brooks > San Francisco, CA > ======================= > ********************************************************************** > 4D Internet Users Group (4D iNUG) > FAQ: http://lists.4d.com/faqnug.html > Archive: http://lists.4d.com/archives.html > Options: http://lists.4d.com/mailman/options/4d_tech > Unsub: mailto:4d_tech-unsubscr...@lists.4d.com > ********************************************************************** ********************************************************************** 4D Internet Users Group (4D iNUG) FAQ: http://lists.4d.com/faqnug.html Archive: http://lists.4d.com/archives.html Options: http://lists.4d.com/mailman/options/4d_tech Unsub: mailto:4d_tech-unsubscr...@lists.4d.com **********************************************************************
