Alpha Video Training
Results 1 to 21 of 21

Thread: Best practice/methodology?

  1. #1
    Moderator Peter.Greulich's Avatar
    Real Name
    Peter Greulich
    Join Date
    Apr 2000
    Location
    Boston, MA
    Posts
    11,629

    Default Best practice/methodology?

    What is the best way to handle this kind of scenario?

    Given clients, many with multiple projects, and thus:

    Clients table
    Projects table
    Invoice table
    Payments table
    ===========

    When a payment is made, often it will be for multiple invoices and often for several different projects. I need to be able to allocate the payments to the invoices, but also to the projects. Presumably, a 1:M set, Payments:Allocated would do the trick, BUT: the majority of payments are for a single invoice. So one check may be for a given invoice/project, and another may be for several projects. The client needs to be able to look at a project, and see what has been paid against that project. This is where it gets sticky. In order to correlate, you then have to examine both the parent & child tables, since there may be single payments, or multiples (child records) against the project.

    What is the suggested method to normalize this equation?

    Please keep in mind, there is also the need to relate clients to payments (which is easy since you only need to look at the actual parent records - i.e. payments).

    See attached report snapshot to get an idea what I am talking about.

    P.S. I have been using the 1:M method in this case for years, but I'm not satisfied due to the lack of normalization as described above.
    Last edited by Peter.Greulich; 04-15-2007 at 05:48 PM.

  2. #2
    "Certified" Alphaholic
    Real Name
    Tom Cone Jr
    Join Date
    Apr 2000
    Location
    Florida
    Posts
    23,300

    Default Re: Best practice/methodology?

    Peter,

    1) Presumably your invoices go to one client at a time. Would they be limited to a single project, or might a single invoice cover charges on more than one project?

    2) Might design goals include being able to instantly see the unpaid balance owed on a given invoice? ... the unpaid balance on a given project? ... will receivables need to be aged by project or by client or by invoice ?

    3) Do you foresee the need to break "invoices" down into a header and an items table?

    -- tom

  3. #3
    Moderator Peter.Greulich's Avatar
    Real Name
    Peter Greulich
    Join Date
    Apr 2000
    Location
    Boston, MA
    Posts
    11,629

    Default Re: Best practice/methodology?

    Hi Tom,

    Quote Originally Posted by Tom Cone Jr View Post
    Presumably your invoices go to one client at a time.
    Yes

    Would they be limited to a single project, or might a single invoice cover charges on more than one project?
    One invoice for one project. Of course, a project might have any number of invoices

    Might design goals include being able to instantly see the unpaid balance owed on a given invoice?
    Exactly

    ... the unpaid balance on a given project?
    Yes

    ... will receivables need to be aged by project or by client or by invoice ?
    Client and project both

    Do you foresee the need to break "invoices" down into a header and an items table?
    Yes, items are billable hours


    Tom, keep in mind, this is an existing application running for many years. However, the single check to multiple payments + the single check to a single payment is my problem area.

    Thanks,

  4. #4
    "Certified" Alphaholic
    Real Name
    Tom Cone Jr
    Join Date
    Apr 2000
    Location
    Florida
    Posts
    23,300

    Default Re: Best practice/methodology?

    Ok, without claiming this is the "best possible approach", I've handled this by including a field in the invoice records to show both the amount paid (to date) and the invoice unpaid balance. As payment table records are entered a simple post field rule updates these fields in the invoice table.

    Like you, if a check covers more than one invoice I split it into multiple payment table records (the sum of which must match the check payment amount). Each split covers the portion allocated (and posted) to a single invoice. I use two different buttons to initiate the sequence. The first (used most often) is pressed when there's no need to split a payment among multiple invoices. The second is used when a payment must be split. Here, the button script populates a form with a browse object showing all the unpaid invoices for the particular client. The browse is based on a separate table called payment_splits which is used as a temporary table that gets populated when splitting a payment, and then emptied when fnished. The user then types in the portions allocated to each row, and a Save button validates the entries, and then commits the new payment table records. If the new payment table records are entered by the script, directly into the table, the post field rule won't fire, so I have the script do it in code.

    Maybe this will give you some useful ideas.

  5. #5
    Moderator Peter.Greulich's Avatar
    Real Name
    Peter Greulich
    Join Date
    Apr 2000
    Location
    Boston, MA
    Posts
    11,629

    Default Re: Best practice/methodology?

    Quote Originally Posted by Tom Cone Jr View Post
    Ok, without claiming this is the "best possible approach", I've handled this by including a field in the invoice records to show both the amount paid (to date) and the invoice unpaid balance. As payment table records are entered a simple post field rule updates these fields in the invoice table.
    That's exactly what i do.

    ...The browse is based on a separate table called payment_splits which is used as a temporary table that gets populated when splitting a payment, and then emptied when fnished. The user then types in the portions allocated to each row, and a Save button validates the entries, and then commits the new payment table records. If the new payment table records are entered by the script, directly into the table, the post field rule won't fire, so I have the script do it in code.
    Interesting. The diff. between your method, and mine, is I use a child table to keep a permanent record. But the child table uses the post field rule to post against the invoice.

    Maybe this will give you some useful ideas.
    Yes, it's food for thought. It also shows me that I'm not off the wall, as your method is substantively very similar to my own.

    Where my method really rubs me the wrong way, is how I have to jump thru hoops to show the Project balance owed.

    Thanks for your time, Tom.

    If anyone out there has insights or a better methodology, I'm all ears.

  6. #6
    "Certified" Alphaholic
    Real Name
    Tom Cone Jr
    Join Date
    Apr 2000
    Location
    Florida
    Posts
    23,300

    Default Re: Best practice/methodology?

    Where my method really rubs me the wrong way, is how I have to jump thru hoops to show the Project balance owed.
    If each invoice record includes a client id you can use a two table set (client primary) with a 1:N link to produce a report showing the total client balance.

    The total client balance can be computed whenever needed using a calc field and tablesum() with a filter on the client id (run against the Invoice table)

    If each invoice record includes a project id you can use a two table set (Project primary) with a 1:N link to produce a report showing the total project balance.

    The total project balance can be computed whenever needed using a calc field and tablesum() with a filter on the project id (run against the invoice table).

    If these are the kinds of "hoops" you wish to avoid, and you don't mind storing a bit of redundant data, you can always create additional post field rules to record transactions in both the client and project tables.

    -- tom

  7. #7
    Moderator Peter.Greulich's Avatar
    Real Name
    Peter Greulich
    Join Date
    Apr 2000
    Location
    Boston, MA
    Posts
    11,629

    Default Re: Best practice/methodology?

    Quote Originally Posted by Tom Cone Jr View Post
    If each invoice record includes a client id you can use a two table set (client primary) with a 1:N link to produce a report showing the total client balance.

    The total client balance can be computed whenever needed using a calc field and tablesum() with a filter on the client id (run against the Invoice table)

    If each invoice record includes a project id you can use a two table set (Project primary) with a 1:N link to produce a report showing the total project balance.

    The total project balance can be computed whenever needed using a calc field and tablesum() with a filter on the project id (run against the invoice table).

    If these are the kinds of "hoops" you wish to avoid, and you don't mind storing a bit of redundant data, you can always create additional post field rules to record transactions in both the client and project tables.

    -- tom
    Hhhmmm...more food for thought. In my case reports are not so much the issue, but form view. The client likes to scroll thru the projects and see the balance owed. Thanks again.

  8. #8
    Member
    Real Name
    Ron Leunis
    Join Date
    Sep 2005
    Location
    Netherlands
    Posts
    652

    Default Re: Best practice/methodology?

    Client 1:N Invoice ( a Client can have several invoices)
    Invoice 1:N InvoiceLines (an Invoice can have several Invoice Lines)
    Client 1:N Project (A client can have several Projects)
    InvoiceLines N:1 Project (billed) (An Project can have several InvoiceLines)
    Client 1:N Payment (A Client can do several payments)
    Lines 1:N PayAllocation with % (A line can have several payment allocations)
    Payment 1:N PayAllocation with % (A payment can have severalPayment allocations to a Line with percentage allocated to the line)

    Payment = total of PayAllocations

    When partial payment of an invoice, you need to hear from client which percentage he allocated to each invoice line = project.

    Client:Invoice:Lines:PayAllocations gives you % Invoices paid
    Client:Project:Lines:Payallocation gives you % Project paid
    ...I think ...
    regards, Ron

  9. #9
    Moderator Peter.Greulich's Avatar
    Real Name
    Peter Greulich
    Join Date
    Apr 2000
    Location
    Boston, MA
    Posts
    11,629

    Default Re: Best practice/methodology?

    Hi Ron,

    Thanks for your response.

    Part of your structure doesn't make sense to me (but it might just be me).

    Quote Originally Posted by rleunis View Post
    Invoice 1:N InvoiceLines (an Invoice can have several Invoice Lines)
    In my case, Invoice Lines are time (or expense elements)

    InvoiceLines N:1 Project (billed) (An Project can have several InvoiceLines)
    This doesn't make sense to me.

    Lines 1:N PayAllocation with % (A line can have several payment allocations)
    This doesn't make sense to me, either.

    Payment 1:N PayAllocation with % (A payment can have severalPayment allocations to a Line with percentage allocated to the line)
    Same deal...

    Client:Invoice:Lines:PayAllocations gives you % Invoices paid
    Client:Project:Lines:Payallocation gives you % Project paid
    Not sure that follows, since I don't follow the above.

    ...I think ...
    Maybe - - - ???

  10. #10
    Member
    Real Name
    Ron Leunis
    Join Date
    Sep 2005
    Location
    Netherlands
    Posts
    652

    Default Re: Best practice/methodology?

    Sorry Peter, I hope underneath clarifies:

    I did not find the method to use quotes in the forum, so I used " " instead when I quote.

    "In my case, Invoice Lines are time (or expense elements)"
    >> If you would have a table InvoiceLines, a column could be referencing a table ItemType with the entries Hours and Expense type.

    "InvoiceLines N:1 Project (billed) (An Project can have several InvoiceLines)
    This doesn't make sense to me."
    >>Table Invoice references a Table InvoiceLines. An Invoice has at least 1 InvoiceLine but can have more.
    The table InvoiceLines should have a column ProjectID to determine for which project you are invoicing this line. The table InvoiceLines can contain several
    InvoiceLines for an Invoice each refering to a particular Project ID and referencing a certain ItemType (Hours or Expense or VAT). So there is a 1 to many
    relationship between Project Table and InvoiceLines table. Therefore a Project can have several InvoiceLines.

    "InvoiceLines 1:N PayAllocation with % (An Invoice line can have several payment allocations)
    This doesn't make sense to me, either."
    >>You should have a table PayAllocation. This table contains data to allocate a paymentamount received in the Payment table to an InvoiceLine (of an
    Invoice). A percentage and/or AllocAmount could be a column in this PayAllocation table which determines for which percentage an InvoiceLine is payed, when a
    customer does not pay 100% of the total Invoice, but has indicated that he pays f.i. your hoursline 100%, but the expenseline only 50%..

    "Payment 1:N PayAllocation with % (A payment can have severalPayment allocations to a Line with percentage allocated to the line)
    Same deal..."
    >>>Give one payment received from a customer (which you store in the Payment table), you need to be able to allocate parts of this payment to several
    invoices and /or several invoiceLines. So one Payment received can consists of several allocations to InvoiceLines. The % allocates a percentage of the total
    payment. This could also be held in a column amountpartial in the PayAllocation table.

    Example:
    Invoice table: for Customer total usd 10000
    InvoiceLine table:
    IL_ID Invoicenumber InvoiceLine 01 10 hours @ usd 120 total usd 1200 projectid
    IL_ID Invoicenumber Invoiceline 02 1 Expenses made @ usd 8800 total usd 8800 projectid
    Customer pays 5600 and lets you know: he's ok with the hours, but he'l'only pays half of your expenses...
    Payment table:
    PaymentID Customer amount received usd 5600
    PayAllocation Table:
    PayAlloc id, PaymentID, AllocAmount 1200 to IL_ID, 100%
    PayAlloc id, PaymentID, AllocAmmount 4400 to IL_ID, 50%

    regards, Ron

  11. #11
    "Certified" Alphaholic
    Real Name
    Tom Cone Jr
    Join Date
    Apr 2000
    Location
    Florida
    Posts
    23,300

    Default Re: Best practice/methodology?

    Ron, if you click the "Advanced" button when the reply editor appears you're taken to a different editor, which includes toolbar buttons for quoting text...

    -- tom

  12. #12
    Moderator Peter.Greulich's Avatar
    Real Name
    Peter Greulich
    Join Date
    Apr 2000
    Location
    Boston, MA
    Posts
    11,629

    Default Re: Best practice/methodology?

    Thanks, Ron. Now I see what you are saying. Really, it's not terribly different that what I am doing. But neither does it get to the heart of my problem: single check payments vs. checks allocated to multiple payments. But Tom Cone's response shows me that I'm not doing anything "wrong", either.

    Thanks again,


    P.S. The attached image shows you how to do "quotes" and "code".

  13. #13
    Member
    Real Name
    Ron Leunis
    Join Date
    Sep 2005
    Location
    Netherlands
    Posts
    652

    Default Re: Best practice/methodology?

    Thanks Tom and Peter for the hint on how to use quotes.

    Peter, I think the model i mentioned deals with single, multiple as well as with part payments. A payment can be allocated to 1 line, to several lines and also to several lines of several invoices.

    regards, Ron

  14. #14
    Moderator Peter.Greulich's Avatar
    Real Name
    Peter Greulich
    Join Date
    Apr 2000
    Location
    Boston, MA
    Posts
    11,629

    Default Re: Best practice/methodology?

    Quote Originally Posted by rleunis View Post
    Peter, I think the model i mentioned deals with single, multiple as well as with part payments. A payment can be allocated to 1 line, to several lines and also to several lines of several invoices.
    Yes, I'm clear on that part. But what i was looking to do was to link the payments/allocated payments (two tables in my current db) to Clients/Projects. Actually, I already do that, but it's not "clean" in my view. I generate reports by client and/or project showing invoices and payments (including date paid, check #, etc.). That's why I would prefer a "clean" link back.

  15. #15
    Member
    Real Name
    Ron Leunis
    Join Date
    Sep 2005
    Location
    Netherlands
    Posts
    652

    Default Re: Best practice/methodology?

    Peter,

    That's why I would prefer a "clean" link back
    I do not understand what you mean with this. Define what a "clean" link back in you eyes mean.

    The client needs to be able to look at a project, and see what has been paid against that project. This is where it gets sticky. In order to correlate, you then have to examine both the parent & child tables, since there may be single payments, or multiples (child records) against the project.
    In a relational model you will cater for the most difficult situation you want to capture. If this means
    multiples (child records)
    then, when there is only one, you still have to populate the intermediate table with one entry, even if it would not be necessary in that particular case...

    Let us know when you find your "clean" link solution. I'm interested learning. What I learned from this thread is to use the quotes...

    regards, Ron

  16. #16
    Moderator Peter.Greulich's Avatar
    Real Name
    Peter Greulich
    Join Date
    Apr 2000
    Location
    Boston, MA
    Posts
    11,629

    Default Dumb & Dumberer

    The obvious finally hit me this morning. Talk about a blind spot. First, allow me to restate the "problem" (given my convoluted explanation in this thread):

    The Problem:
    Roughly 1 out of 5 payments is to pay off all or part of multiple invoices. The remainder usually pay off one invoice each. Thus most payments were allocated on the parent (i.e. check) level. The remainder were allocated on the child level (to multiple invoices). The allocations for both tables were performed with a simple posting rule (very reliable by the way). But, of course, this violates db normalization rules and forced me to "jump thru hoops" to make things work properly in various places - such as the project_invoice_payments set and the client_invoice_payments set - both of which I need.

    The solution (I'm surprised no one thought of this):
    I simply moved all of the allocations to the child table, period. Thus the 4 out of 5 single payment parent records now must have one child record to allocate the payment. Multiple allocations continue to use the child table as before. The sum of the child record(s) must equal the check amount field ("The Enforcer"). End of story.

    See the attached ugly form (under development) images which demonstrates this simple (and obvious) solution...

  17. #17
    "Certified" Alphaholic Mike Wilson's Avatar
    Real Name
    mike wilson
    Join Date
    Apr 2005
    Location
    Grand Rapids, Michigan
    Posts
    4,168

    Default Re: Best practice/methodology?

    Peter,
    Glad that you found your answer.
    I think that Ron might have been trying to say this when he wrote:
    Quote Originally Posted by rleunis View Post
    In a relational model you will cater for the most difficult situation you want to capture. If this means then, when there is only one, you still have to populate the intermediate table with one entry, even if it would not be necessary in that particular case...
    Mike W
    __________________________
    "I rebel in at least small things to express to the world that I have not completely surrendered"

  18. #18
    Moderator Peter.Greulich's Avatar
    Real Name
    Peter Greulich
    Join Date
    Apr 2000
    Location
    Boston, MA
    Posts
    11,629

    Default Re: Best practice/methodology?

    Mike, you are correct: Ron is right (and I missed that subtlety).
    Thanks for pointing it out.

  19. #19
    VAR
    Real Name
    Martin W. Cole
    Join Date
    Apr 2000
    Location
    Terrell, Texas (near Dallas)
    Posts
    5,949

    Default Re: Best practice/methodology?

    Check out a pictire on my website, for the Metallurgical app

    www.martinwcole.com

    I think I do basically the same thing you and Tom are talking about

    Tables:
    Payments
    PaymentDistributions
    Invoices


    One invoice may have paymentdistributions from many payments
    One payment may have paymentdistributions for many invoices

    A payment not completely distributed is handled as a credit
    Cole Custom Programming - Terrell, Texas
    972 524 8714
    martin_w_cole@msn.com

    ____________________
    "A young man who is not liberal has no heart, but an old man who is not conservative has no mind." GB Shaw

  20. #20
    Moderator Peter.Greulich's Avatar
    Real Name
    Peter Greulich
    Join Date
    Apr 2000
    Location
    Boston, MA
    Posts
    11,629

    Default Re: Best practice/methodology?

    Nice job, Martin. I mapped your set structure from the screen shots. Very comprehensive view.

  21. #21
    VAR
    Real Name
    Martin W. Cole
    Join Date
    Apr 2000
    Location
    Terrell, Texas (near Dallas)
    Posts
    5,949

    Default Re: Best practice/methodology?

    thank's Peter
    Cole Custom Programming - Terrell, Texas
    972 524 8714
    martin_w_cole@msn.com

    ____________________
    "A young man who is not liberal has no heart, but an old man who is not conservative has no mind." GB Shaw

Similar Threads

  1. Best practice -- application location
    By urbanski in forum Alpha Five Version 7
    Replies: 6
    Last Post: 01-27-2007, 01:15 AM
  2. Attachments best practice?
    By Pat Bremkamp in forum Web Application Server v7
    Replies: 3
    Last Post: 01-16-2007, 12:45 PM
  3. Best practice/folder structure?
    By cellboy in forum Web Application Server v7
    Replies: 5
    Last Post: 05-03-2006, 12:54 PM
  4. Set modification - best practice?
    By Paul H in forum Alpha Four Versions 7 and 8
    Replies: 2
    Last Post: 07-29-2004, 04:45 AM
  5. Practice safe Deleting (fields)
    By Greg Fong in forum Alpha Five Version 4
    Replies: 6
    Last Post: 07-05-2001, 05:28 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •