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]) - 
- 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 
- 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 <>
> 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 <> 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:
> Archive:
> Options:
> Unsub:
> **********************************************************************
4D Internet Users Group (4D iNUG)

Reply via email to