Calc Invoices to Pay?

Fairlight fairlite at fairlite.com
Mon Mar 3 21:25:44 PST 2008


This public service announcement was brought to you by Stanley Barnett:
> Hi,  Does anyone have any filePro code for this specific problem?  
>  
> A customer sends a check for say $2145.54 to be applied to his account.
> His account balance is $9845.52 and covers 42 invoices.  He wrote the
> invoice numbers on his check, however they are not legible and after
> spending an hour trying to find the invoices that the customer intended
> to pay, I gave up and will need to contact the customer for verbal
> confirmation tomorrow when the customer is in their office.
>  
> I need a routine that can find the correct invoices quickly by only
> supplying the check amount.  We are assuming the customer did not
> partial pay an invoice.

I don't think that's possible to do with 100% accuracy.  If there were two
invoices for the same amount, or if there were multiple permutations of
invoices that were in different amounts but come to the same sum, you're
really faced with a user-level decision that no program should be making
with something as important as AR that can affect the credit ratings of
customers.

If you're still determined to go ahead with a bad plan like this, you're
really looking at a matrix calculation, I believe.  You have to assemble
the invoices, probably in earliest-to-latest chronological order, and
stack two arrays--one with the invoice number, the other with the amount
for the invoice.  If you could get it to the point where the amount of the
total of the array is equal to the total payment applied, you'd have your
theoretical winning combination.  But you'd have to go through potentially
every combination of invoices to find this magic combination, wiping the
arrays at the start of each run--hence the matrix-like quality of the
calculation.  Then there's the fun part (I'm not even sure how I'd tackle
it) of tracking combinations that didn't work.  Theoretically that should
be part of the algorithm, but you still have to track it.  Say no solution
with invoice included #207 worked.  You'd have to track that somehow so
that your attempted permutations further down the line didn't even try to
include it--you've already found out it won't work.

You're basically writing the equivalent of a password cracking program, but
with financial sums instead of characters--and with multiple possible
outcomes instead of one absolute "this is it!" solution that's guaranteed
to be right.

It's actually a further bad idea for most businesses.  There could be
disputed charges if things are misapplied, chargebacks, extra fees involved
with the banks of both parties.  Name it.  All because the program made the
wrong choice.

This -really- is something I strongly feel should be a user-level
decision with someone manually taking responsibility for the decision and
signing off on it, even if it were technically possible to make it 100%
accurate--which it isn't.  It'd be nice if it could be automated, sure.
The reality is that there's always going to be a doubt and margin for error
with automation of this specific type.  In finance, that's unacceptable.

Probably not the answer you were looking for, but there you go--the
unvarnished reality.  Proceed onward pursuing this at your own risk.

mark->
-- 
"Moral cowardice will surely be written as the cause on the death
certificate of what used to be Western Civilization." --James P. Hogan


More information about the Filepro-list mailing list