Hi, I'm not an accountant so this probably is totally wrong but what I do is this.
I have 4 tables involved Client (fairly obvious) Consultations (a consult is an invoice effectively) Payments Transactions. The transaction table links to the other three. It's fields: ID Client_ID Consult_ID Payment_ID Type Date Amount If the transaction is for a consult then type = 1, payment_id is blank and the amount is the same as the consult amount. If the transaction is for a payment then type = 2, client_id is blank and the amount is the negative of the payment amount. Client balance is the sum of transactions Payments are applied against the total rather than individual invoices. - no need to unlock invoice - clients can pre-pay Does that make sense? Wayne On Saturday, 15 October 2016, Chip Scheide <4d_o...@pghrepository.org> wrote: > Theory says: > do not store something that can be calculated. > Practicality often says otherwise > > line art structure: > > [invoice] <- [line items] <- [payment_lineitem_link] -- > <- [payments] <-----------------------------| > <- [addresses] > -> [Purchaser] > Entry/listing form shows Sum([Line items]) - sum([payments]) > > for reporting > Selection to array (various data) > process report by pulling data from arrays > > You never have to worry about : > - adding a payment - create a new record with the right invoice ID > - adding a line item - create a new record with the right Invoice ID > - current outstanding balance (read only) Sum([Line items]) - > sum([payments]) > - current total (read only) Sum([Line items]) > > you will rarely have to worry about : > - applying a payment (tracking against which line item(s) a payment > covers) > - deleting a line item (only an issue if done outside of an entry form) > - modifying a line item (only an issue if done outside of an entry form) > These are situations where 1 user may have locked a line item or > payment while another user is trying to change something. If the > changes are only allowed to be done while in an entry form then even > these go away, as if the parent record is locked the related should be > too (read only). > > > > > On Fri, 14 Oct 2016 09:20:05 -0700, Kirk Brooks 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 > <javascript:;>> > > 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 > <javascript:;>> 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 <javascript:;> > > ********************************************************************** > ********************************************************************** > 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 <javascript:;> > ********************************************************************** -- Regards, Wayne [image: --] Wayne Stewart [image: http://]about.me/waynestewart <http://about.me/waynestewart> ********************************************************************** 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 **********************************************************************
