Allocating payments to line items (1 Viewer)

[dobseh]

I've got what I think is a fairly standard order/invoice system in db I look after for a children's club. This club has a term based fee for membership, and each term an order is generated for each parent containing the fee for each of the children they have attending. In the db this populates a table ( or example tblfeespayable) with an orderid and a line for each child containing the fee for that individual. Another table(tblpaymentsmade) tracks the payments that have been made for each term and line item in tblfeespayable.

I've created the code to populate tblfeesmade where the full payment is made easily enough, however people being as they are occasionally someone will pay less than they should and handling this is where I would like some advice on how to go about allocating the payments.

Ideally what I would like to do is allocate it such that the first line item is written into tblpaymentsmade as fully paid and the remaining amount allocated to the second, so for example:

Bob's fee is £50
Alan's fee is £25.50
Their parent pays £75 which leaves 50p outstanding
Bob gets an entry for £50 in tblpaymentsmade
Alan gets an entry for £25 in tblpaymentsmade

I'm thinking that the way to do this is:

1. create a recordset that selects out Bob and Alan's fees from tblfeespayable
2. sets the amount_paid as a variable (I'll add a break here if amount_paid > total of feespayable)
3. a dcount to check if there are any payments matching the payment reference in tblpaymentsmade if this returns 0 goto step 5 else
4. a second recordset and a loop that checks whether a payment already exists in tblpaymentsmade for Bob and if there is compares it to the amount in tblfeespayable
   1. if the comparision is true then the loop skips to the Alan's record
   2. if the comparision is false then the difference between the two is subtracted from amount_paid and Bob's entry in tblpaymentsmade is updated
   3. if amount_paid = 0 the loop ends and we exit sub
   4. if not move to Alan's record and repeat the comparision or if we are EOF (i.e Alan has no record in tblpayments made) exit this loop
5. A loop with the recordset created in step 1
   1. Compare amount_paid to Bob's fee in tblfeespayable
   2. subtract amount_paid from Bob's fee and insert it into tblpaymentsmade
   3. if amount_paid = 0 then end loop and exit sub else
   4. compare amount_paid to Alan's fee in tblfeespayable
   5. subtract amount_paid from Alan's fee and insert the result into tblpayments made
   6. End sub

Does this approach seem sane or is there a better method for achieving it?

 

[bob fitz]

Can I just ask why there is a need to allocate receipts to individual fees?
Why not just compare the total of the fees charged to the total of the amounts received?

 

[dobseh]

Can I just ask why there is a need to allocate receipts to individual fees?
Why not just compare the total of the fees charged to the total of the amounts received?

It would be much easier if I could do it like this, but the club has a policy where if a child is leaves then they get a pro-rata refund based on what they have paid and the number of weeks into the term. This means the per child payments need to be tracked so the correct refund can be given...

 

[adhoustonj]

I would have a payments table that stores all payments made.
If the fee is $35 and they paid $1 dollar 35 times with different payments, then there would be 35 records in the payments table that would be summed up and compared to the fee amount.

 

[dobseh]

I would have a payments table that stores all payments made.
If the fee is $35 and they paid $1 dollar 35 times with different payments, then there would be 35 records in the payments table that would be summed up and compared to the fee amount.

There is a payments table. Your solution is fine but it's really just moving the problem from when the data is written to the table to when it's read. I'd still need to have make sure that if they need to refund then the amount paid for that child is pro-rated rather than just the total amount paid towards the fee.

If they pay £35 in £1 increments against two children with fees of £25 and £15 but they've only paid £10 towards the second then I need to make sure they only get £10 not £17.50...

I get that it is possibly a silly policy to have, but it's what they have decided and I have to work within this constraint.

 

[bob fitz]

It would be much easier if I could do it like this, but the club has a policy where if a child is leaves then they get a pro-rata refund based on what they have paid and the number of weeks into the term. This means the per child payments need to be tracked so the correct refund can be given...

So with the example that you posted earlier, how do you determine which childs fee was fully paid and which was underpaid?

 

[dobseh]

So with the example that you posted earlier, how do you determine which childs fee was fully paid and which was underpaid?

There is an indexed ID field in tblfeespayable that links the child to their fee for that term. This field is written into tblpaymentsmade when a payment is made.

 

[bob fitz]

There is an indexed ID field in tblfeespayable that links the child to their fee for that term. This field is written into tblpaymentsmade when a payment is made.

So, are you saying that if the £25.50 fee is entered before the £50 fee then it will be the £50 fee that is underpaid?

 

[dobseh]

So, are you saying that if the £25.50 fee is entered before the £50 fee then it will be the £50 fee that is underpaid?

No, the fees go into the feespayable in descending order of price, so that ordering by the ID always gives you the most expensive first. Also end users can't allocate a payment to an individual fee, the form for adding payments just allows them to enter an amount and the database is expected to sort out allocating it.

 

[Pat Hartman]

If you are allocating payments individually as they come in rather than as a batch, you would use a query that selects the unpaid fees for the family and order them descending by due date and then by outstanding amount so you apply payments to the oldest items first and the highest due amount. OR, if it makes more sense, order the amounts ascending so the smallest bill gets paid first. Then you loop through the recordset and compare the payment with what is due. If the due amount is <= the paid amount apply the full amount to the record, subtract it from the paid amt and read the next record. Keep applying and decrementing the paid amount.

What happens to over payments? Post your schema. You should have a child table attached to the payments that shows the allocation of each payment. You join the invoice to that table to determine which items are unpaid and that drives the allocation process.

 

[dobseh]

If you are allocating payments individually as they come in rather than as a batch, you would use a query that selects the unpaid fees for the family and order them descending by outstanding amount. Then you loop through the recordset and compare the payment with what is due. If the due amount is <= the paid amount apply the full amount to the record, subtract it from the paid amt and read the next record. Keep applying and decrementing the paid amount.

I think you are saying the process I posted originally with this?

What happens to over payments?

Payments entered get validated to ensure that amount_paid is not greater than the amount_oustanding

Post your schema. You should have a child table attached to the payments that shows the allocation of each payment. You join the invoice to that table to determine which items are unpaid and that drives the allocation process.

Happy to post the schema, but could you explain what the value of the child table is? I'm not sure why it would be needed, I don't need to keep track of the number of payments that were made towards the outstanding amount; If the parent pays £5 a week over three weeks or £15 in week one - it's all the same, the row(s) in tblpaymentsmade just get updated with the amount that has been paid.

The schema of the orders/invoices part of the DB looks like this. The two tables in question are the two at bottom right of the screenshot. There is an added wrinkle in this work as parents have the option to pay by up to three installments split across the term, which is why the termpayments are not part of the Orders table. That isn't really relevant to this problem though.

 

[Pat Hartman]

Not quite, you would never use a dcount in this process. The recordset you read needs to join to the payment table summed by invoice item and select ONLY items that are not fully paid. This recordset does not get updated. Payment transactions are calculated and appended to the applied table.

The point of the child table is auditability. you want each payment to be logged against an invoice item. If you simply update amounts, you will never be able to find your logic errors. This is money we're talking about and auditors are pretty persnickety about proving your results are correct. Having the child table lets you tie $5 against this item plus $10 against that item to the $15 check. You always need to specifically track where you applied a payment.

I don't think your schema reflects that.

 

[dobseh]

Not quite, you would never use a dcount in this process. The recordset you read needs to join to the payment table summed by invoice item and select ONLY items that are not fully paid. This recordset does not get updated. Payment transactions are calculated and appended to the applied table.

The point of the child table is auditability. you want each payment to be logged against an invoice item. If you simply update amounts, you will never be able to find your logic errors. This is money we're talking about and auditors are pretty persnickety about proving your results are correct. Having the child table lets you tie $5 against this item plus $10 against that item to the $15 check. You always need to specifically track where you applied a payment.

I don't think your schema reflects that.

Hah, I worked in PCI compliant companies and been part of a PCI audit - this ain't that, not by a very long stretch. There's no need at all for an audit trail or anything even close, although I fully appreciate that best practice should be to do it that way anyway. This is a very small club which makes less profit in three months than most people earn in a day. Suffice to say, I'm not getting paid to do this, so the simple/quick/dirty option is always going to be the choice

 

[Pat Hartman]

There is ALWAYS a need to be able to audit data. Take shortcuts at your own pareil.

so the simple/quick/dirty option is always going to be the choice

Q&D is the wrong choice when you are dealing with other people's money. Don't commit to doing stuff like this for free if you are going to be sloppy about it. You are doing them no favor. Quickbooks or even quicken is a far better choice and quicken has pretty inexpensive on-line versions.

It would be far better to not automatically apply a payment than to take shortcuts in the code. Everything you do should be done using best practices. Let the user manually apply the payment. Your code just makes sure they don't allocate more than 100% of the payment amount. That is the simple solution.

 

[dobseh]

Sorry, but we are going to differ here. This isn't taking automatic payments, there is always going to be user input to enter amounts that have been paid. The money in the bank doesn't go anywhere and is always there as a transactional record of payments that exists in a fully auditable form outside of this Access Database.

There is a world of difference between simple and sloppy. There is a world of difference between ultimate best practice and fully functional/fit for purpose. There is a world of difference between systems that global banks run, systems that small businesses run and systems that tiny NFP charities and clubs run. In an ideal world, everything would be perfect and every i would be dotted and t crossed, but we live in the real world where that isn't the case and compromises have to be made.

Your message is incredibly condescending, preachy, unhelpful and pretty offensive to be honest.

 

[gemma-the-husky]

Like others have said (but perhaps not), you raise an invoice and allocate payments against the invoice as a whole until the invoice is cleared. You don't want to try to pick individual items on an invoice.

Matching to an invoice is both simpler and the correct way to do it. You may also find you need to raise a credit note against the invoice, and match that in a similar way..

You have an invoice table, a cash receipts table, which balances with your bank account. Many receipts will be for invoices, but some may not.

So you then need an invoice matching table to relate the receipts against the invoices. It may be that you would never have a receipt covering more than one invoice, in which case you could store the invoice number in the receipts table, but that would fail if you did receive a payment for more than one invoice - although you could get round that easily enough.

I think what you are proposing (an auto matching process to invoice lines) is actually harder than the above.

To take your example. A parent gets an invoice for $75.50 for 2 children. If he pays $75.00 he still owes 0.50. You may issue an adjustment/credit for that 0 50, or you may pursue it. There's no need to allocate the $75.00 to distinct items on the fee note. When he pays the balance, or you issue an adjustment the fee note becomes fully matched.

 

[dobseh]

Like others have said (but perhaps not), you raise an invoice and allocate payments against the invoice as a whole until the invoice is cleared. You don't want to try to pick individual items on an invoice.

Matching to an invoice is both simpler and the correct way to do it. You may also find you need to raise a credit note against the invoice, and match that in a similar way..

You have an invoice table, a cash receipts table, which balances with your bank account. Many receipts will be for invoices, but some may not.

So you then need an invoice matching table to relate the receipts against the invoices. It may be that you would never have a receipt covering more than one invoice, in which case you could store the invoice number in the receipts table, but that would fail if you did receive a payment for more than one invoice - although you could get round that easily enough.

I think what you are proposing (an auto matching process to invoice lines) is actually harder than the above.

To take your example. A parent gets an invoice for $75.50 for 2 children. If he pays $75.00 he still owes 0.50. You may issue an adjustment/credit for that 0 50, or you may pursue it. There's no need to allocate the $75.00 to distinct items on the fee note. When he pays the balance, or you issue an adjustment the fee note becomes fully matched.

I fully agree that this is the best approach, and it's the one I have taken in the invoices/orders of physical items section of the DB(I forgot to include the payments table for this in the schema screenshot). The reason I can't take it for this piece is the need to be able to refund individual invoice lines based on how much has been paid towards each one. It's the refund side that is problematic, not so much the debt one.

It may be easier and less time consuming to see if I can persuade the club to change the refund policy rather than try and work around it, as it seems that the issue people in this thread have had is not my method for overcoming the challenge, but the fact it exists in the first place.

 

[gemma-the-husky]

I fully agree that this is the best approach, and it's the one I have taken in the invoices/orders of physical items section of the DB(I forgot to include the payments table for this in the schema screenshot). The reason I can't take it for this piece is the need to be able to refund individual invoice lines based on how much has been paid towards each one. It's the refund side that is problematic, not so much the debt one.

It may be easier and less time consuming to see if I can persuade the club to change the refund policy rather than try and work around it, as it seems that the issue people in this thread have had is not my method for overcoming the challenge, but the fact it exists in the first place.

In that case, personally, I would try to get them to issue separate invoices for each child, so that each child is specifically allocated, and it's much simpler.

 

[dobseh]

In that case, personally, I would try to get them to issue separate invoices for each child, so that each child is specifically allocated, and it's much simpler.

We tried this, parents only paid one invoice because they thought the other was a duplicate. This doubled the workload of the club co-ordinator and made lots of people very unhappy. What's that saying about "if you design something to be idiot proof, the universe will design a better idiot"...

​

 

[gemma-the-husky]

Well you seem to have tried everything. I can see why you might try to automatch invoice lines, but that might catch you out bearing in mind your notes about the refund policy. In which case, it might be better to get the administrators to match the lines manually.