Combinations of numbers vs a total - how to calculate?

[Angus McKinnon]

I've had an interesting challenge thrown in my direction by one of our administrators. Part of their job is to reconcile credit card payments that we have received, against orders that are in Filemaker. 

98% of the transactions are simple to reconcile, but the remainder can be problematic. This is often caused by other staff not marking FM orders as having been paid, but nonetheless it still needs picked through. 

The problem is compounded by the way we receive card payments into our bank account, there are usually a number of payments lumped together for the day(s) in question. This makes it hard to spot unreconciled payments. If there is one individual payment for, say, £45 landing in the account, and an unmarked order worth £45, that's easy to marry up. Where it gets more complicated is when there are three or four unmarked orders, and all (or none) of them could be attributed to the one composite payment that has landed. 

What I'd like to be able to provide for them is a calculation function that shows how different combinations of totals would match with the payment. Here's a grossly simplified example:

Unmarked order 1:  £5

Unmarked order 2: £10

Unmarked order 3: £15

Total received: £15

 

Anyone glancing at this can tell that the total received could have been made up of Order 1+2, or Order 3. It's much harder (and more time consuming) to work it out manually when there are multiple larger, irregular numbers to consider. I'd like to automate this process for them if possible. 

 

I can think of a script that plods through each potential combination, but this is going to get exponentially more cumbersome and very time consuming to write. I'm guessing that there must be a more elegant way of doing it, but it's well outside the areas I'm familiar with. I suppose it's essentially a math problem, which is not one of my strengths!

 

Can anyone suggest a good way to attack this?

[comment]

10 minutes ago, Angus McKinnon said:

I can think of a script that plods through each potential combination, but this is going to get exponentially more cumbersome and very time consuming to write. I'm guessing that there must be a more elegant way of doing it,

I don't think there is. See if this helps:
https://fmforums.com/topic/105432-find-to-the-nearest-sum/?do=findComment&comment=477089

 

[Angus McKinnon]

That's incredibly helpful - glad to know that I'm not the only one hitting this kind of challenge. (Although the other poster described it far more accurately than me)

I've downloaded the demo file, and to be honest that will be enough to give to the administrator to experiment with. It'll certainly save a chunk of time compared to the manual method. I could probably find a way of integrating the capability into our main solution (and being able to automatically copy in the totals from the unreconciled orders) but it's overkill unless they are going to use the core functionality.