0 Replies Latest reply: Jun 10, 2014 9:48 PM by Gus Ajram RSS

    Hierarchy parent child relationships

      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.

       

      ItemDescriptionItemIDParentIDParentQuantity
      COKE 600MLx2424100
      COKE 600ML29924124
      FANTA 600MLx2424200
      FANTA 600ML30024224
      COKE 2L747700

       

      Currently when I sum sales and quantity this is the layout

      ItemDescriptionSalesQtySalesValueItemIDParentIDParentQuantity
      COKE 600MLx241533.7524100
      COKE 600ML10832429924124
      FANTA 600MLx2481824200
      FANTA 600ML9628830024224
      COKE 2L514.75747700

       

      Ideally this is the layout I would like and I would convert the child item quantites and link them to the parent items

      ItemDescriptionSalesQty ConvertedSalesValue
      COKE 600MLx2419.5357.75
      FANTA 600MLx2412306
      COKE 2L514.75