Hi,
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
ItemDescription | SalesQty Converted | SalesValue |
COKE 600MLx24 | 19.5 | 357.75 |
FANTA 600MLx24 | 12 | 306 |
COKE 2L | 5 | 14.75 |