6 Replies Latest reply on Jul 22, 2014 2:57 PM by LukaszKi

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

    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

        • 1. Re: Report from order throught the product to products parts (3 main tables, 2 join tables)
          philmodjunk

               From here, it seems like you have Products and Recipes as two tables where they could actually be a single table--simplifying your datamodel by one "occurrence box" in your relationship graph,

               But whether or not you make that change, you can use Go to Related records from your Order layout to pull up a found set of RecipeLines records on a layout based on RecipeLines. You can then sort these records, if desired, to group them by MaterialID so that each item is listed only once and a combination of summary fields and calculation fields can compute combined quantities for each material specified in the recipe for an ordered product.

          • 2. Re: Report from order throught the product to products parts (3 main tables, 2 join tables)
            LukaszKi

                 I will try to use the function to get the recipelines in relation to orderID in the new layout and then to make the calculation via the formula:

                 recipeLineQty / recipeSum * orderQty and I hope it works. 

                 Thanks for the hint!

                 One more thing I cannot find to get relation.

                 Should I make some additional filed in recipeLines?

                  

                 The system do not know in any way how to link the recipeLines with the order. The connection is 2 tables away

                 in ORDERS there is a connection to PRODUCTS via join table ORDERLINES. The product keep the connection to RECIPES. But there is no relation between  RECIPES (and RECIPESLINES) and order.

                 Kind Regards

                 Luca

            • 3. Re: Report from order throught the product to products parts (3 main tables, 2 join tables)
              philmodjunk

                   RecipeLines is already linked to orders in a relationship according to your description of your database. Go to Related Records is not limited to those table occurrences immediately connected to your layout's table occurrence. You can use it to refer to RecipeLines from your Orders layout to pull up all related recipeLines records so long as you have the chain of relationships as you have described.

              • 4. Re: Report from order throught the product to products parts (3 main tables, 2 join tables)
                LukaszKi

                     It is working! I need to play with recalculations based on the quantities in OrderLines!

                     Is there any chance to populate the quantity from OrderLines to RecipeLines? If I have this value I can make a recalculation of material needed.

                      

                     many thanks!

                      

                     Kind regards

                     Luca

                • 5. Re: Report from order throught the product to products parts (3 main tables, 2 join tables)
                  philmodjunk

                       Each record in RecipeLines is not specific to any one order.

                       We'd need to use either ExecuteSQL or set up an additional relationship between RecipeLines and  a new occurrence of OrderLInes such that a summary field or Sum function can compute the total quantity needed for that one RecipeLines specified material. Neither option will be particularly easy to do.

                       The relationship method would require that you use a script to populate a text field in RecipeLines with the IDs of every record in OrderLInes that specifies a product that links to that recipelines record.

                  • 6. Re: Report from order throught the product to products parts (3 main tables, 2 join tables)
                    LukaszKi

                         Hmm, I thought it would be easier (like calculation field based on the field from reated table via relation graph)

                         exactly it seems to be a hard way. Even the additional relation is hard (no specific field to make relation. The only way would be to replace the product with the recipe (not to order the product but to order the recipe). But the thing is I made such structure in Ms access and it worked. Here I do not have an idea how to move on.

                          

                         Any way I appreciate your help and patience.

                         Luca