SilverReed Posted November 8, 2011 Posted November 8, 2011 Dear All, I have a very interesting issue with the following database structure: Production Plan, key: planID Plan details, keys: planID, productID Product, key: productID, recipeID Recipe, key: recipeID Recipe details, key: recipeID, materialID Material, productID Material is a TO of Product The following relation is set: Production Plan (planID) >---< (planID) Plan details (materialID) >---< (materialID) Product (recipeID) >---< (recipeID) Recipe (recipeID) >---< (recipeID) Recipe details (productID) >---< (productID) Material The fileds I use are as follow: Production Plan: month Plan details: month, product, qty Product: name, price, netto weight Recipe: name Recipe: material, qtyPer1000kg Material: name, price OK lets define, I have: Products: Orange drink Forest drink Recipes: Orange drink: Water 950 Sugar 30 Aroma orange 20 [*]forest drink: Water 920 Sugar 50 Aroma forest 30 Plan: Month: 10 Plan details: Orange drink 10kg Forest drink 20 kg And finally I would like to get an report of raw materials use: Month: 10 No Material name Qty 1 Water 27,9 (calc: 950/1000*10 + 920/1000*20) 2 Sugar 1,3 (calc: 30/1000*10 + 50/1000*20) 3 Aroma forest 30 (...) 4 Aroma orange 30 (...) And the first question is: Is it possible May I use the structure I have, or I need to make a new tables? Any help I would like to thank in advance With Regards Luca
comment Posted November 8, 2011 Posted November 8, 2011 Sounds very similar to: http://fmforums.com/forum/topic/61550-perpetual-inventory-extracting-totals-of-goodsingredients-used/page__view__findpost__p__291835
SilverReed Posted November 9, 2011 Author Posted November 9, 2011 I tried to make it as in example but I have very strange effects. The problem is I can not match Plan line ID with materialID So I should have (the source for the report is by ingredient): planID-----Banana Drink ------ Water 50 planID-----Banana Drink ------ banana aroma 30 planID-----Banana Drink ------ sugar 20 but I get planID-----Banana Drink ------ Water 50 planID-----Banana Drink ------ banana aroma 30 planID-----Banana Drink ------ sugar 20 ? ---------?????-------flavour 30 ----------?????-------milk 1200 I just want to say it is taking all lines from recipe details, not only the lines connected with plan :(
comment Posted November 9, 2011 Posted November 9, 2011 (edited) The problem is I can not match Plan line ID with materialID AFAICT, your case is simpler than the one in the other thread: in addition to the core TOG: Sales -< LineItems >- Products -< Quantities >- Materials you need only a global gSaleID field in the Quantities table, and the following relationship: Quantities::gSaleID = LineItems 2::SaleID AND Quantities::ProductID = LineItems 2::ProductID --- P.S. I don't see why you have separate tables for products and recipes. Edited November 9, 2011 by comment
Recommended Posts
This topic is 4779 days old. Please don't post here. Open a new topic instead.
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now