3 Replies Latest reply on Apr 28, 2014 6:08 AM by philmodjunk

    Transactions and Payments

    TKnTexas

      Title

      Transactions and Payments

      Post

           I have two tables Customers and Transactions.  Transactions is very simplified, charges and payments.  If I am posting a payment, and put in the customer number, I want the unpaid transactions to populate a portal.  

            

           I have a TO for the posting of the charges.  I am not sure how to define the second TO.  Suggestions?

           The SeqNum in the Customer table is the customer number which is the secondary key (sKey) in the transaction file.  I am thinking populating the TranCode (PAY) and the Customer Number would populate a portal with transactions for the customer with a balance due greater than 0.00.

      Screen_Shot_2014-04-26_at_12.36.33_AM.png

        • 1. Re: Transactions and Payments
          philmodjunk

               How do you record a payment in this table of transactions where you can have paid and unpaid transactions? Cannot one payment pay off more than one transaction? Could a payment pay off part of a transaction?

          • 2. Re: Transactions and Payments
            TKnTexas

                 A payment could pay off a whole or part of any invoice with a BalanceDue greater than 0.00.

                  

                 I will have a payment layout based on this 2nd T.O.  Populating the customer number field (sKey) would populate the open invoices (BalanceDue greater than 0.00) for that customer.  Manually the user can enter numbers to the AmtApplied field.  When BalanceDue = 0.00 the invoice would stop showing in the portal.  

                 I am just having trouble defining the self-relationship (2nd T.O.)

            • 3. Re: Transactions and Payments
              philmodjunk

                   A payment could pay off a whole or part of any invoice with a BalanceDue greater than 0.00.

                   I gather that an Invoice is a transaction? If partial payments are possible, isn't it also possible that a single payment might pay off more than one invoice?

                   Either way, most businesses need to keep track of each payment as well as which invoices are paid off by it. This often entails at least one more table, possibly two in order to be able to log a single payment with the amount, date, method of payment etc. and yet link that single payment to each invoice that it pays off and which is able to record the portion of the total payment to be applied to a given invoice in order to record partial payments of an outstanding invoice.

                   Does that sound like what you need here?

                   

                        I am just having trouble defining the self-relationship (2nd T.O.)

                   Sorry, but I don't see a self relationship in your screen shot nor do I see a need for one here.