Need help on how to apply payments (1 Viewer)

[jdcallejas]

Hello,
I have been here before almost a year ago and I am still having an issue on how to apply payments to my invoices.... Ok I have created an application for my apartment complex, I am very advance on it. We are able to create leases for our tenants and make maintenance workorders create reports for all this stuff... NOW the time to start taking payments its here!!!! I had been going from one thing to another figuring out how to do this... The way I have it right now is that for each lease we would create an invoice. In this invoice I have a subform where I can pick different types of charges like late fees, rent fees, nsf fees ect.

Than i created a payment form where we pick the the tenant and input payments.

I need to know how i can apply payments to the outstanding invoices the client has.

Example; if the tenant has two invoices with a total amount of $100 each, he has a grand total of $200 balance due. Now he only made a payment of $100, I would like to have the procedure to check the oldest invoice and apply the payment to that invoice. Now he has a blance due of $100. Then the tenants makes another payment of $50, I would like the procedure to check the oldest invoice and apply the payment but since he only made $50 payment the invoice still is pending but the new balance due is $50.

How can I do this?

Thanks,
Daniel

 

[HiTechCoach]

I handle it like this:

Payment (1) > (many) Allocation (junction table) (many) < (1) Invoice

The idea is to use an allocation table to link payments to invoices. The allocation table will hold the amount of the payment to be applied to the invoice.

 

[jdcallejas]

Thanks for the response,

Ok I understand what you are saying, the payment form would be my parrent form and the junction table would be my child form. So what I think your saying is that I would have a key field on my junction table that has the relationship to my Invoice table. I would make this a combobox will list my invoices. Where would I put the amount of the payment? Do I make a payment field on the junction table? or the payment amount should be at the payment table?

If is not to much trouble can you create a sample database? or if you want I can create it with a simple client table, products table, Invoice table and invoice detail table. This way all you have to help me with is with the payment table and the payment detail table. This way I can see and study what you did.

I just dont understand how I would apply the payment....
Please let me know if you can do this so I can make this small database.

thanks,
daniel

 

[Atomic Shrimp]

I handle it like this:

Payment (1) > (many) Allocation (junction table) (many) < (1) Invoice

The idea is to use an allocation table to link payments to invoices. The allocation table will hold the amount of the payment to be applied to the invoice.

I think there could be a case for storing the payments in the same table as all the other transactions - the Allocation junction table then becomes a way of joining bits of the transaction table to other bits of itself.

 

[gemma-the-husky]

i think atomic shrimp has this correct

you have an invoice file

you need a receipts file in which to store the total payment
you need a payment allocation file in which to store the invoice/payment match pair

if you dont you are ok until

a) a single payment covers multiple invoices OR
b) a single invoice is paid in 2 or more instalments

Bear in mind that all self-respecting payment modules will have a facility for an unmatched payment - eg the lessor pays you an amount that isnt correct, so you cant match it exactly - so you want to leave it in suspense until you sort out what was paid

Personally, I would have thought it very dangerous to ASSUME any payment matches specific invoices, especialy the earliest. At best, suggest that a payment can clear certain invoices, but always get a user confirmation

 

[Atomic Shrimp]

I agree - another scenario that can happen is something like a customer with debt problems agrees to pay a monthly set amount, which does not relate to any specific invoices - so you just clear off what you can, when you can - oldest first. *(the same kind of thing can happen with disorganised customers who just send payments for what they believe to be the right amount)

In all of these scenarios, the way I would handle it would be:

(the outstanding balance is defined as the sum of any transactions in the table that are not matched via the payments allocation junction table)

Add the payment as a transaction to the transactions table as a negative value (same as a refund or credit, in terms of the value for the transaction history)

Allow the user to select a number of unmatched/unallocated invoices (and select the payment transaction)

If the total value of all the selected transactions is greater than zero (meaning the invoices selected exceed the payment), it is not possible to proceed - the user can cancel out, leaving the payment transaction unallocated, for now.

If the total value of all the selected transactions is exactly zero, (meaning the payment exactly fulfils the invoices), the process needs to add records to the junction table, joining the invoices to that payment.

If the total value of all the selected transactions is less than zero (meaning the payment exceeds the value of the selected invoices), the process needs to do something like:
Add records to the junction table, joining the invoices to the payment
Add an 'unallocated cash carried forward' transaction to the transaction table - essentially a mini-invoice for the remaining amount.
Add a record in the junction table, joining the above balancing record to the payment
Add an 'unallocated cash brought forward' transaction to the transaction table - essentially a credit for same (opposite) value of the previous balancing transaction - and leave this unjoined to anything, for now.

-Then next time you receive a payment from the customer, the remaining part of their payment is still unallocated, waiting to be cleared against future invoices - also, because it's a credit transaction in their transaction history, it should appear on any statement document you generate for them.

The exact same process also works for clearing refunds or other credit-type transactions against invoices.

 

[gemma-the-husky]

thinking again, one other thing that payment systems ALWAYS need is a discount. Lets say your tenant owes you £1000, but you agree to accept £900.

You need a way to clear the £1000 invoice using £900 cash plus £100 discount/allowance

Actually implementing payment systems is not so easy in Access (or any system to be honest)- you probably need to make use of temporary tables (or at least temporary flags) and update properly only when the payment is reconciled/balanced. In a multi-user system you also need some way of controlling the way multiple users set these payment flags. just in case users interfere with each other.

 

[Atomic Shrimp]

thinking again, one other thing that payment systems ALWAYS need is a discount. Lets say your tenant owes you £1000, but you agree to accept £900.

You need a way to clear the £1000 invoice using £900 cash plus £100 discount/allowance

I'd give that discount its own transaction record - treat it as a single line credit, then clear it along with the relevant batch of invoices.

Or implement some kind of feature in the cash posting module that enables a discount transaction to be created on the fly (as with carrying forward of unallocated cash)

 

[HiTechCoach]

daniel,

Thanks for the response,

Ok I understand what you are saying, the payment form would be my parrent form and the junction table would be my child form. So what I think your saying is that I would have a key field on my junction table that has the relationship to my Invoice table. I would make this a combobox will list my invoices. Where would I put the amount of the payment? Do I make a payment field on the junction table? or the payment amount should be at the payment table?

I was only describing the table, not the form design. I was not saying anything about what would be the payment for, the sub form, or what controls to use, like combo boxes. That is all handling when you are designing the User Interface (UI).

First you must get the table structure correct. Once this is done. then you can start worrying about the UI.

Back to the tables ...

Yes, there will be an amount of payment to allocated to this invoice field in the allocation junction table. If you take all the allocation records for a payment and sun the, you normally will get the total of the payment, providing the payment was totally allocated to invoices.

Thanks for the response,

If is not to much trouble can you create a sample database? or if you want I can create it with a simple client table, products table, Invoice table and invoice detail table. This way all you have to help me with is with the payment table and the payment detail table. This way I can see and study what you did.

I agree with the others that that this can become very time consuming (lots of programming) to create if you want to make it very easy for the end user. I use temp tables, and lots of VBA code to handle this in my applications.

Thanks for the response,

I just dont understand how I would apply the payment....
Please let me know if you can do this so I can make this small database.

There are several way to handle UI. I try to design the form to match the workflow.

I will see if I can come up with some examples for you.

I'd give that discount its own transaction record - treat it as a single line credit, then clear it along with the relevant batch of invoices.

Or implement some kind of feature in the cash posting module that enables a discount transaction to be created on the fly (as with carrying forward of unallocated cash)

I agree. Everything that changes the balance should be a line item. IMHO, a discount should be handled just like adding on a late fee.

The advantage to this will be is in the reporting that can be done.

 

[Niroth]

How about this: a lease table - customer, invoice No., charges, amount due etc

A payment table - amount paid, time paid, invoice No., customer etc

In query: sum of charges - sum of amount paid = amount still due.

You can query by customer or by invoice number, so you will know when an invoice has been paid in full, or only partially, and same with each customer.

 

[Atomic Shrimp]

I don't really see any reason to keep the payment records in a separate table from all the other transactions - they have many common fields, the same kind of relationships to the customer table, etc. They're just transaction type 'payment' instead of 'invoice'.

-And there are contexts in which you might want to list them along with all the other transactions - on a statement of account, for example - it's generally easier to split them out when you want them separate, than it is to combine them when you need them together.

 

[Niroth]

I suppose it depends on the kind of operation you want to do. I found keeping the two type of transactions separate was easier for both entry and reconciliation later. It only requires 1 or 2 query to calculate what's needed, even to list everything together. For me, the payment table was linked to the invoice table, using invoice number, and the fields actually weren't that similar.