5 Replies Latest reply on Aug 10, 2015 11:37 AM by steveoh

    Payment to Multiple Invoices

    steveoh

      So I'm working on an old database and trying to add new functionality to it.  Something I Would like to do is to establish a method of applying a single payment to multiple invoices.


      It's probably the essential many to many scenario that has a lot of new developers scratching their heads.  This is what I have so far:


      [Payment] - - - - [Customer] - - - - [Invoices]


      I figured having an intermediary table would assist with the dealing with the many to many.  I am having issues, however, applying payments from "Payment" to "Invoices".    I can't seem to copy text the script itself and the file, as stated, is a currently used database so some of the information is live.  I've rewritten this so if theres errors it's likely they're of a transposing nature.  I left out a few debugging dialog boxes I added for my purpose and things of that nature.


      The problem lies somewhere in this area of code.  I can't get it to store any values, I've used custom dialog boxes that tell me the running balance of each variable and so I know those aren't the issue.


      CODE


      Set Variable[$Payment; Value:Payments::Principal Owed + Payments::Extra] //Set these separately for another reason we won't go into

      Set Variable[$PaymentBalance; Value: "0"]  //Initiating Variable for later use

      Set Variable[$BalanceOwed; Value:Invoices::BalanceOwed]

       

      If[$Invoices > 0]  //test if there's still money owed with invoices

                    If[$Payment > $BalanceOwed]

                          Go to Related Record [(from invoices using layout _)] //Left out names as they didn't seem relevant

                          Loop

                              Set Variable[$PaymentBalance; Value:$payment - Invoices::BalancePaid]

                              Set Field [Invoices::totalPaid; $Payment - $BalanceOwed

                              Set Variable[$Payment; Value:$PaymentBalance]

                              Set Variable[$BalanceOwed; Value:Invoices::BalanceOwed]

                              Go to Record/Request/Page [Next; Exit after last] //Move to next invoice is the intention here

                              Exit Loop If [ $Payment = 0 ]

                              Exit Loop If [ Sum ( Invoices::BalancePaid ) = 0 ]

                          End Loop

                    Else If [$Payment < $BalanceOwed]

                          Go to Related Record [(from invoices using layout _)] //Left out names as they didn't seem relevant

                          Set Field [Invoices::totalPaid; Value:Invoices::BalanceOwed - $payment]

                    End If

                Else If [$BalanceOwed = 0]

      //Not the problem area//



      Any feedback would be greatly appreciated

        • 1. Re: Payment to Multiple Invoices
          mikebeargie

          Your relationships should be:

           

          Customer --<- Invoices

          Invoices --<- InvoiceLines

          Customer --<- Payments

          Payments --<- PaymentSplits

          Invoices --<- PaymentSplits

           

          Basically you need another intermediary join table to handle the multi:multi relationship between payments and invoices for your new functionality. You may have to use some table occurrences to be able to display everything how you need, but fundamentally you are describing a multi:multi relationship, but have not setup your relationships that way.

          • 2. Re: Payment to Multiple Invoices
            mikebeargie

            Then your loop becomes making records in the PaymentSplits table until your payment = 0.

            • 3. Re: Payment to Multiple Invoices
              steveoh

              I'll try those changes, thanks for your help!

              • 4. Re: Payment to Multiple Invoices
                erolst

                What you have is:

                steveoh wrote:

                [Payment] - - - - [Customer] - - - - [Invoices]

                 

                which you would write as:

                 

                Payment >-- Customer --< Invoices

                 

                What you need is

                 

                Invoices >-- Customer --< Payments --< InvoicePayment >-- Invoices (yes, it's the same table …)

                 

                Meaning: a customer has many invoices, and many payments; every payment belongs to zero, one or many invoices.

                 

                (That allows you to record payments without initially (or, for that matter, at all) associating them with any invoice.)

                 

                So that is your data model.

                 

                steveoh wrote:

                 

                So I'm working on an old database and trying to add new functionality to it.

                […]

                The problem lies somewhere in this area of code.

                 

                So, what exactly is your script trying to achieve? And what is the problem?

                • 5. Re: Payment to Multiple Invoices
                  steveoh

                  I'll have to make that "Invoices Payment" tab.  Thanks for the help.

                  So, what exactly is your script trying to achieve? And what is the problem?

                   

                  As stated in the original post:

                   

                  The problem lies somewhere in this area of code.  I can't get it to store any values, I've used custom dialog boxes that tell me the running balance of each variable and so I know those aren't the issue.