7 Replies Latest reply on Nov 9, 2014 2:21 PM by beverly

    Payments & Part Payments

    dchabot

      Hi,

       

      Beverly mentioned from another thread about payment and part payment tables to allow spreading a payment among several invoices.

       

      Excerpt below:

      Hello, Rob. I agree that you should have this a separate table (even for occasionally). I have two tables for accepting $$:

       

      Payments

      Payment_splits.

       

       

      Payments get the full amount of a check (or other form of payment), "splits" get the division of the full amount into the appropriate invoices. This is how a lot of accounting programs work. One payment can be used for multiple invoices and invoices can have multiple payments.

       

      It's not that difficult and if you already have your repeating field, you can export then import to divide out the values into a related table.

       

      Beverly

       

      I am not able to get to the splitting of a payment across multiple invoices.

       

      This is what I have:

       

      Customers - Jobs - Job Invoices Line Item(Invid) - Payments(invid) - Payment Splits(invid)

       

       

      When entering a payment/check amt you look up the job by id, which brings down the customer info etc, then a button to lookup the invoices.

      Invoices come up and you can select which one to apply the payment to, which may not be the whole amount.

       

      So far OK, but I cannot figure out how to bring in the payment splits!!!!!

      I'm missing a many to many?

       

      Thanks so much

       

      DChabot

        • 1. Re: Payments & Part Payments
          erolst

           

          This is what I have:

           

          Customers - Jobs - Job Invoices Line Item(Invid) - Payments(invid) - Payment Splits(invid)

           

          Shouldn't that rather be:

           

          Customers --< Jobs --< Payment Splits >-- Payments >-- Customers ?

           

          (ignoring LineItems as not being pertinent to that topic)

          • 2. Re: Payments & Part Payments
            dchabot

            Eroist,

             

            Thank you, I tried that relationship but it didn't work.

            Why wouldn't I want to look into the line items file where all the invoices reside vs the Jobs file which doesn't

            contain the invoice details?

            • 3. Re: Payments & Part Payments
              erolst

              dchabot wrote:

              the line items file where all the invoices reside vs the Jobs file which doesn't

              contain the invoice details?

               

              Because you said you want to split payments between several invoices, not between individual parts of an invoice; that means that you would regard the invoice as a whole.

               

              If you want to treat that differently, you could use

               

              Customers --< Jobs --< Job Invoices Line Item >-- Payment Splits >-- Payments

               

              which means that every line item is balanced by one split payment. If you want to be able to balance a line item by multiple split payments (possibly from several different payments), you need another table between the line items and the splits.

              • 4. Re: Payments & Part Payments
                gdurniak

                Most solutions have a Jobs table,  an Invoices table,  then an Invoices Line Items table for the detail

                 

                If your Invoices all have just one item,  then one Invoice table would suffice

                 

                Perhaps that's what you've done

                 

                greg

                 

                > Why wouldn't I want to look into the line items file where all the invoices reside vs the Jobs file which doesn't contain the invoice details?

                • 5. Re: Payments & Part Payments
                  beverly

                  Hey, Deb!

                  If I enter the payment from an invoice, I make the relationship to the splits, but allow creation of related records and this make the "payments" record, too. I have various ways to get to the payments or splits, it all depends on what is easier for you. No additional joins just the creation of related.

                   

                  Create a pop-up to enter a payment then assign the splits

                  or enter a split to an invoice, which also creates the "payment".

                   

                  Yes, you have to enter the full amount on payments. Yes, you have to enter the amount of the split per invoice.

                   

                  I guess the "splits" IS the "join file" between payments and invoices. But I have the relationships just as you do. The invoice ID goes into the splits, not the payments.

                   

                  I don't have the relationship to the "line items", but to the invoice so I don't know if that's your disconnect.

                   

                   

                  Invoices -< splits (allow creation) >- payments (allow creation)

                   

                  HTH,

                  Beverly

                  • 6. Re: Payments & Part Payments
                    dchabot

                    Beverly, thanks so much for your easy explanation.

                    I'm almost there!

                     

                    I want to track the amount that is being applied until they equal the check/payment amount so a dialog can

                    be displayed like 'You haven't applied the whole payment' or 'Payment has been applied' and so forth.

                     

                    How do you track the amount applied?

                    I've set a variable $ckamt, and then I setup a global balance where I add the total part payment variable $partpayt for each invoice until

                    it reaches the check amt.

                     

                    Set variable $ckamt/ckamt

                    Pause

                    'Accept Button'

                    Set Variable-  $partpayt_total

                    Set field: gbalance + $partpay_total

                    Show custom dialog "Hurrah"

                     

                    Does this seem a bit clumsy?

                     

                    Thanks so much!

                     

                    Deb

                    • 7. Re: Payments & Part Payments
                      beverly

                      Base it on Sum(splits)  = amtDue? That's what I do.

                       

                      -- sent from myPhone --

                      Beverly Voth

                      --