Invoice Payment System
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.