Set sum from multiple figures

[petal]

here goes: i often need to match a clients bank deposit to multiple invoices. for example, they might deposit $100 which is payment for 4 invoices of $10, $20, $30 and $40 respectively. the only problem is that there might be many other invoices outstanding and i need to use trial and error to find the invoices they have paid.

is there a script that will run through all the outstanding invoices for a particular client and then show the combination that matches a set amount.

for example, i want to enter the $100 referred to above and then let the script sort through the entries for that client until it finds the invoices that add up to $100.

this would be a massive time saver for me every month!

i might add that i am about to upgrade to filemaker 8 advanced. might this open the door to a solution?

Edited November 19, 2005 by Guest

[SlimJim]

There is also the possibility that their deposit does not match any combination of invoices.

I would look towards creating an otstanding balance and simply mark the invoices as paid in the order in which they are raised UNLESS.. the client tells you which invoices they are paying.

[petal]

thanks. of course you are right, it might not match the sum of any of the invoices. it might even match the sum of a different set of invoices, but more than likely, it will match the sum of a just one set of invoices. crediting in chronological order may be technically correct, but in practice i would only have to come back later and change the credits if they did not match.

the problem is still out there. is there a solution using a recursive script?

[SlimJim]

There do not seem to be any takers. I have given this some thought and I cannot see any quick and efficient ways of doing this so lets have a shot at "brute force".

I have written a script which is based on the assumption that you have isolated into a found set the outstanding invoices which are candidates for payment. The only way that I can see is to calculate all the sums of the subsets of the invoices. You can see what I mean by brute force! if there are n invoices then there are 2^n -1 subsets. I am attaching a pdf of the script. You need 4 fields in the script , In which is either 0 or 1 and at the end specifies whether or not that invoice is to be paid, Outstanding which is the amount outstanding on a given invoice, Payment which is the amount of the payment received and finally Difference which tells you how far away you are from matching the payment amount. The script is designed to make this number as small as possible in absolute value. (if you want this to be small and positive or small and negative then changes will have to be made in the script.) I also enclose a zipped sample file in fp7 format. If you put an amount into the payment field and click the assign button you will get an idea of how sloooow this is (particularly if you put in an amount which is not a multiple of 5)

TestAmount.pdf

InvoiceAssign.zip

[petal]

thanks, i will give it a go at some point in the next week or so and will let you know.