Jump to content
Claris Engage 2025 - March 25-26 Austin Texas ×

How to go through 5 related tables.


This topic is 4779 days old. Please don't post here. Open a new topic instead.

Recommended Posts

Posted

Dear All,

I have a very interesting issue with the following database structure:

  1. Production Plan, key: planID
  2. Plan details, keys: planID, productID
  3. Product, key: productID, recipeID
  4. Recipe, key: recipeID
  5. Recipe details, key: recipeID, materialID
  6. 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:

  1. Orange drink
  2. Forest drink

Recipes:

  1. 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:

  1. Is it possible
  2. 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

Posted

Sounds very similar to:

http://fmforums.com/forum/topic/61550-perpetual-inventory-extracting-totals-of-goodsingredients-used/page__view__findpost__p__291835

Posted

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 :(

Posted (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 by comment

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 account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.