matching the settlement to bills

[Jwwwson]

I have trouble matching the settlement to invoice, I have two documents, one is bill summary, another one is settlement summary as shown below.

Since the settlement is paid in a lump sum, I can't simply match the billed amount using the bill number.

Bill Summary
Bill Number	Tasks No.	Bill No. + tasks No.	Amount
123456	A001	123456A001	100
123456	A002	123456A002	200
234567	A001	234567A001	200
345678	A001	345678A001	300

Settlement
Bill Number	Settlement amount
123456	300
234567	200

What I would like to achieve is like:
Bill Number	Tasks No.	Bill No. + tasks No.	Amount	Settlement amount	Outstanding balance
123456	A001	123456A001	100	100	-
123456	A002	123456A002	200	200	-
234567	A001	234567A001	200	200	-
345678	A001	345678A001	300	0	300.00

Thanks for your help in advance!

[fmvizcaino]

Hi @Jwwwson ,

In my attached example, I'm using a multi-row tool to update the settlement with the value of each bill. The idea of the solution is that, if you have a wrong settlement sum, the workflow will identify as I'm showing in the example.

Let me know if that works for you.

Best,

Fernando Vizcaino

[Jwwwson]

Hi @fmvizcaino, 

Many thanks for your help!

I tried to run it with my data set and I noticed that the data type of the settlement amount is set to up 'byte', in that case no decimal figure is allowed for the settlement amount column. However, if I change the data type to 'double' or 'int16', a negative figure appears in the settlement amount column if there is no settlement for that bill. Attached please find the revised workflow with the data type of 'settlement amount' changed. Kindly advise if there is any way that I can keep the decimal figures with no negative figures popping up in the settlement amount column.. 

Thanks again and wish you an amazing 2020!

Best regards, 
Jason

[fmvizcaino · Accepted solution]

Hi @Jwwwson ,

I have corrected my example. See if it works for you now.

Best,

Fernando Vizcaino

[Jwwwson]

Hi, It works! Many thanks! Best regards, Jason