I have data in table like
Customer | Invoice No | Date | Value | Paid | Balance |
---|---|---|---|---|---|
ABC | 1 | 01/12/2020 | 25 | 0 | 25 |
ABC | 2 | 01/12/2020 | 50 | 0 | 50 |
XYZ | 3 | 02/12/2020 | 200 | 0 | 200 |
XYZ | 4 | 04/12/2020 | 100 | 0 | 100 |
ABC | 5 | 04/12/2020 | 500 | 0 | 500 |
Now I received amounts for customers as below
ABC 540 XYZ 210
ABC | XYZ |
---|---|
540 | 210 |
After receiving the amounts my table should like this
Customer | Invoice No | Date | Value | Paid | Balance |
---|---|---|---|---|---|
ABC | 1 | 01/12/2020 | 25 | 25 | 0 |
ABC | 2 | 01/12/2020 | 50 | 50 | 0 |
XYZ | 3 | 02/12/2020 | 200 | 200 | 0 |
XYZ | 4 | 04/12/2020 | 100 | 10 | 90 |
ABC | 5 | 04/12/2020 | 500 | 465 | 35 |
I got some clues but this works only for date based values but I need customer and date based.
Any help will be appreciated