How about a data model? http://www.databaseanswers.org/data_models/invoices_and_payments/index.htm
-- Douglas von Roeder 949-336-2902 On Fri, Oct 14, 2016 at 11:58 AM, Wayne Stewart <[email protected]> wrote: > 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 <[email protected]> > 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 < > [email protected] > > <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 <[email protected] > > <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:[email protected] <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:[email protected] <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:[email protected] > ********************************************************************** > ********************************************************************** 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:[email protected] **********************************************************************
