Deplete oldest numbers and their amount when new arrives
Hi all,
I was wondering if you could help me with below problem. When I receive a new number with an amount (first table), I want that this amount deducts my oldest open numbers (second table) and rounds it up. So if an open invoice will be taken partly, the amount of the new number needs to increase so that the partly taken number can be taken fully.
Table new
Number
Date
Amount
36A
02-05-2019
225
Table open
Number
Date
Amount
1A
19-04-2019
453
3B
15-04-2019
348
465A
02-04-2019
18
36C
05-03-2019
18
49A
05-03-2019
119
So in this case the numbers 49A, 36C, 465A and 3B (the four oldest numbers) are being taken and the amount that needs to be taken is 503 instead of the initial 225.
I don't want to mess up my current tables, but want to add the above sentence as text object to the sheet. I've added a simple QVW, where you can also see the text object.
I hope I've made myself clear and that you can solve this problem. Thanks in advance!
I hope it's a bit clear, maybe I can help a bit more. Below you see the formula that gives the right answer for this example. But as you don't know how many old numbers will be covered by the new amount, this needs to have a loop. And that is where my struggle is; how to create a loop that's checks per number (sorted from oldest to newest) if it exceeds the new amount.
