Allocate sales quantity to import purchase quantity

[PhyuLayKhine333]

Hi 

I am having problem with solving this scenario. I have multiple transaction of specific model per day . I want to allocate this sales quantity to import purchase quantity which will also have specific model with multiple day. My criteria is using date , product model and import declaration number. The following is an example of data and the result i want to get ; 

Sales Table

Sales Date	Product Name	Quantity
3/1/2022	A	30
5/1/2022	B	20

Purchase Table

Purchase Date	Import Declaration Number	Product Name	Quantity	Cost	Exchange Rate
2/12/2022	100161238551	A	20	200	1.5
2/19/2022	100161238554	A	20	200	1.7
2/22/2022	100161238557	A	20	200	1.3
3/12/2022	100161238580	B	20	500	1.8

The Result i want to get

Purchase Date	Import Declaration Number	Product Model	Quantity	Cost	Exchange Rate	Sold	Closing Inventory
2/12/2022	100161238551	A	20	200	1.5	20	-
2/19/2022	100161238554	A	20	200	1.7	10	10
2/22/2022	100161238557	A	20	200	1.3	-	20
3/12/2022	100161238580	B	20	500	1.8	20	-

Can someone help me? Also thanks in advance.

[TomMartens]

Hey @PhyuLayKhine333 ,

please explain the rule that leads to the expected result.

Regards,

Tom

[PhyuLayKhine333]

My rule is like first in first out method using purchase date's declaration import number, purchase date criteria. Product A from purchase table which have different multiple import declaration number link with product A from sales table quantity and date criteria . The final i want to get is cost of sales and closing inventory of fifo method .