Re: Managing invoices - write the total or look it up?
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 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 <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
Re: Managing invoices - write the total or look it up?
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:=
Re: Managing invoices - write the total or look it up?
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 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/arch
Re: Managing invoices - write the total or look it up?
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> > 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
