Announcement

Collapse

The Alpha Software Forum Participation Guidelines

The Alpha Software Forum is a free forum created for Alpha Software Developer Community to ask for help, exchange ideas, and share solutions. Alpha Software strives to create an environment where all members of the community can feel safe to participate. In order to ensure the Alpha Software Forum is a place where all feel welcome, forum participants are expected to behave as follows:
  • Be professional in your conduct
  • Be kind to others
  • Be constructive when giving feedback
  • Be open to new ideas and suggestions
  • Stay on topic


Be sure all comments and threads you post are respectful. Posts that contain any of the following content will be considered a violation of your agreement as a member of the Alpha Software Forum Community and will be moderated:
  • Spam.
  • Vulgar language.
  • Quotes from private conversations without permission, including pricing and other sales related discussions.
  • Personal attacks, insults, or subtle put-downs.
  • Harassment, bullying, threatening, mocking, shaming, or deriding anyone.
  • Sexist, racist, homophobic, transphobic, ableist, or otherwise discriminatory jokes and language.
  • Sexually explicit or violent material, links, or language.
  • Pirated, hacked, or copyright-infringing material.
  • Encouraging of others to engage in the above behaviors.


If a thread or post is found to contain any of the content outlined above, a moderator may choose to take one of the following actions:
  • Remove the Post or Thread - the content is removed from the forum.
  • Place the User in Moderation - all posts and new threads must be approved by a moderator before they are posted.
  • Temporarily Ban the User - user is banned from forum for a period of time.
  • Permanently Ban the User - user is permanently banned from the forum.


Moderators may also rename posts and threads if they are too generic or do not property reflect the content.

Moderators may move threads if they have been posted in the incorrect forum.

Threads/Posts questioning specific moderator decisions or actions (such as "why was a user banned?") are not allowed and will be removed.

The owners of Alpha Software Corporation (Forum Owner) reserve the right to remove, edit, move, or close any thread for any reason; or ban any forum member without notice, reason, or explanation.

Community members are encouraged to click the "Report Post" icon in the lower left of a given post if they feel the post is in violation of the rules. This will alert the Moderators to take a look.

Alpha Software Corporation may amend the guidelines from time to time and may also vary the procedures it sets out where appropriate in a particular case. Your agreement to comply with the guidelines will be deemed agreement to any changes to it.



Bonus TIPS for Successful Posting

Try a Search First
It is highly recommended that a Search be done on your topic before posting, as many questions have been answered in prior posts. As with any search engine, the shorter the search term, the more "hits" will be returned, but the more specific the search term is, the greater the relevance of those "hits". Searching for "table" might well return every message on the board while "tablesum" would greatly restrict the number of messages returned.

When you do post
First, make sure you are posting your question in the correct forum. For example, if you post an issue regarding Desktop applications on the Mobile & Browser Applications board , not only will your question not be seen by the appropriate audience, it may also be removed or relocated.

The more detail you provide about your problem or question, the more likely someone is to understand your request and be able to help. A sample database with a minimum of records (and its support files, zipped together) will make it much easier to diagnose issues with your application. Screen shots of error messages are especially helpful.

When explaining how to reproduce your problem, please be as detailed as possible. Describe every step, click-by-click and keypress-by-keypress. Otherwise when others try to duplicate your problem, they may do something slightly different and end up with different results.

A note about attachments
You may only attach one file to each message. Attachment file size is limited to 2MB. If you need to include several files, you may do so by zipping them into a single archive.

If you forgot to attach your files to your post, please do NOT create a new thread. Instead, reply to your original message and attach the file there.

When attaching screen shots, it is best to attach an image file (.BMP, .JPG, .GIF, .PNG, etc.) or a zip file of several images, as opposed to a Word document containing the screen shots. Because Word documents are prone to viruses, many message board users will not open your Word file, therefore limiting their ability to help you.

Similarly, if you are uploading a zipped archive, you should simply create a .ZIP file and not a self-extracting .EXE as many users will not run your EXE file.
See more
See less

Best practice/methodology?

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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, 05:48 PM.
    Peter
    AlphaBase Solutions, LLC

    Peter@AlphaBaseSolutions.com
    https://www.alphabasesolutions.com



  • #2
    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

    Comment


    • #3
      Re: Best practice/methodology?

      Hi Tom,

      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,
      Peter
      AlphaBase Solutions, LLC

      Peter@AlphaBaseSolutions.com
      https://www.alphabasesolutions.com


      Comment


      • #4
        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.

        Comment


        • #5
          Re: Best practice/methodology?

          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.
          Peter
          AlphaBase Solutions, LLC

          Peter@AlphaBaseSolutions.com
          https://www.alphabasesolutions.com


          Comment


          • #6
            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

            Comment


            • #7
              Re: Best practice/methodology?

              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.
              Peter
              AlphaBase Solutions, LLC

              Peter@AlphaBaseSolutions.com
              https://www.alphabasesolutions.com


              Comment


              • #8
                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

                Comment


                • #9
                  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).

                  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 - - - ???
                  Peter
                  AlphaBase Solutions, LLC

                  Peter@AlphaBaseSolutions.com
                  https://www.alphabasesolutions.com


                  Comment


                  • #10
                    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

                    Comment


                    • #11
                      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

                      Comment


                      • #12
                        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".
                        Peter
                        AlphaBase Solutions, LLC

                        Peter@AlphaBaseSolutions.com
                        https://www.alphabasesolutions.com


                        Comment


                        • #13
                          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

                          Comment


                          • #14
                            Re: Best practice/methodology?

                            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.
                            Peter
                            AlphaBase Solutions, LLC

                            Peter@AlphaBaseSolutions.com
                            https://www.alphabasesolutions.com


                            Comment


                            • #15
                              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

                              Comment

                              Working...
                              X