7 Replies Latest reply on Mar 5, 2014 12:37 PM by philmodjunk

    Invoice Payment System

    JLHTechSolutions

      Title

      Invoice Payment System

      Post

           I am working on creating a payment tracking system, but I am struggling in making it function properly. When a payment comes in, I enter the information into my PAYMENTS layout. I select the ACCOUNT that the payment is for. I have a portal on my page that displays all the INVOICES for that particular account. 

           What I want to do is to take the total payment and apply it to an Invoice. Sometimes I will get a payment that is for 1 invoice. Sometimes the payment will cover multiple invoices. I've been reading different posts and other people's thoughts on this, but I am having difficult making it work.

           I have created a JOIN TABLE which I call LEDGER. Payments are entered into the PAYMENTS table and linked to the appropriate ACCOUNT. Then in the portal, INVOICES are shown. I have a AMOUNT_APPLIED field that is inside my LEDGER table. Here is where I would enter in the amount of the payment that gets applied to a particular INVOICE.

           Where I am having problems is when I create a new record in my PAYMENTS table and select an ACCOUNT, it pulls up all the invoices for that account and shows me what I entered in for the other payments in the AMOUNT_APPLIED field. They shouldn't be there as I might have to enter in more payments for that particular invoice before it's paid in full.

           In my relationship, I have checked the box to "ALLOW RECORD CREATIONS" on the side of the LEDGER table. I am not sure what I am missing here in making this work. At some point, I will create a script, but for now, I seem to have a problem getting this to work as it should.

           Here is what my relationships look like.

           And my layout.

           I would appreciate any help as I have been struggling with this for the past few weeks.

           Thanks in advance.

        • 1. Re: Invoice Payment System
          philmodjunk

               Your link to the join table from payments should not be by Account ID but by payment ID, then your portal will only show join table records for the current payment.

          • 2. Re: Invoice Payment System
            JLHTechSolutions

                 I'm confused right now as everything in my layout is coming from the top TOs  (T24a, etc). I wasn't able to make the T24c or T24d TOs produce anything, but I haven't deleted them yet as I thought I might need them. 

                 Which TO are you talking about I need to change?

            • 3. Re: Invoice Payment System
              philmodjunk

                   I was not specifically referring to any of them. I can't see the match fields so they don't really tell me anything. You said:

                   

                        Where I am having problems is when I create a new record in my PAYMENTS table and select an ACCOUNT, it pulls up all the invoices for that account and shows me what I entered in for the other payments in the AMOUNT_APPLIED field.

                   If you are matching to ledger records by account, you will see all past applied payments for that account. If you match to ledger records by PaymentID, you will only see ledger records for the current payment.

                   Both relationships can be useful, but have different purposes in your management of the payment process.

              • 4. Re: Invoice Payment System
                JLHTechSolutions

                     I've updated my screenshot to show the match fields. For what I am trying to accomplish, where should my portal be pointed at? Are my relationships correct, or do I need to change anything?

                • 5. Re: Invoice Payment System
                  philmodjunk

                       It depends on what you want to see in that portal and how you plan to use it. A portal to T24c placed on a layout based on T24 will only show ledger entries linked to the current payment. That may be useful for apportioning a mult-invoice payment over more than one invoice.

                        

                  • 6. Re: Invoice Payment System
                    JLHTechSolutions

                         I want to select the account from the top half and have the portal populate with Invoices that still have an outstanding balance. There I can select which invoice to apply the funds to and have it marked as paid once the total payments equal the invoice total.

                         If I create the portal from T24c, do I have to set up a filter so it will only show the invoices that are unpaid or do I do that another way?

                    • 7. Re: Invoice Payment System
                      philmodjunk

                           It sounds like you need TWO portals, one to list unpaid invoices for the selected account and one for managing the ledger entries linked to the current account.

                           A portal to T24a1 would list all invoices for a selected account (Provided that the matching record exists in accounts. You don't actually need T24a, you could link T24 directly to T24a1.) A portal filter could be added to omit those invoices that are fully paid.

                           And a button in this portal could be scripted such that clicking it creates a ledger record linked to that Invoice and the current payment.