I need help linking my Item Hierarchy. In the example below we buy cases of drinks and either sell the cases or break them up and sell the individual bottles.
They are linked in the Item table where the bottle has a ParentID which is the actually the ItemID of the parent item.
Looking for way to link them so I can see sales converted in the same method we purchase them which is cases.
ItemDescription
ItemID
ParentID
ParentQuantity
COKE 600MLx24
241
0
0
COKE 600ML
299
241
24
FANTA 600MLx24
242
0
0
FANTA 600ML
300
242
24
COKE 2L
7477
0
0
Currently when I sum sales and quantity this is the layout
ItemDescription
SalesQty
SalesValue
ItemID
ParentID
ParentQuantity
COKE 600MLx24
15
33.75
241
0
0
COKE 600ML
108
324
299
241
24
FANTA 600MLx24
8
18
242
0
0
FANTA 600ML
96
288
300
242
24
COKE 2L
5
14.75
7477
0
0
Ideally this is the layout I would like and I would convert the child item quantites and link them to the parent items
