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)​

$0:=  lookup the line items



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>

> 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

Reply via email to