AnsweredAssumed Answered

Report from order throught the product to products parts (3 main tables, 2 join tables)

Question asked by LukaszKi on Jul 21, 2014
Latest reply on Jul 22, 2014 by LukaszKi

Title

Report from order throught the product to products parts (3 main tables, 2 join tables)

Post

     Dear FM Developers,

     I do have a problem to build a report to show the ingredients which are necessary for order.

      

     The structure of the relationship is as follow

      

     [order]

     customerID

     orderID

      

     [orderLines]

     orderLineID

     orderID (relationship to order)

     productID

     quantity

      

     [products]

     productID (relationship to orderLine)

     recipeID 

      

     [recipes]

     recipeID (relationship to product)

      

     [recipeLines]

     recipeLinesID

     recipeID (relationship to recipe)

     quantity

     materialID (points to productID in TO of products named materials )

      

      

     ok real example would be

      

     the customer places an order for 

      

     #order 1 

     -- Green Drink - 2 bottle

     -- Red Drink - 1 bottle

      

     the green product has an recipe

      

     Recipe 1 

     -- water 1 qty

     -- green dye 1 qty

     -- bottle 1 qty

      

     the red product has an recipe

      

     Recipe 1 

     -- water 1 qty

     -- red dye 1 qty

     -- bottle 1 qty

      

      

     I would like to make and report showing

      

     # order 1 ingredients necessary:

     -- water 2 qty

     -- green dye 1 qty

     -- red dye 1 qty

     -- bottle 2 qty

      

      

     The problem is I do not know how to make an report to show the ingredients and summary via the following structure:

      

     [order:: orderID] <-> [orderLines:: orderID | productID] <-> [products:: productsID | recipeID] <-> [recipes:: recipeID ] <-> [recipesLines:: recipeID | materialID] 

      

     is there any chance to make that kind of aggregation?

      

     many thanks for any suggestions.

      

     Luca

Outcomes