Calculating items in recipe

[SilverReed]

Hi Guys,

I am newbie to the problem I have faced.

I have the following structure of the database:

Production Plan (plan ID to plan ID) Plan Details (product ID to product ID) Products table (Recipe ID to Recipe ID) Recipes table (Recipe ID to Recipe ID) Recipe Details table (Material ID to Material ID) Materials table

In Production Plan table I keep info about what I produce when, in what quantity

In Recipe I keep reference to Recipe Details

In Material Database I keep the info what is the price of each material.

What I would like to do is:

1. Using the recipe details calculate the sum of materials used (let say I have to products consists of sugar):

Recipes for:

1. Brown cake: 250 kg

a. Sugar: 200 kg

b. eggs: 50kg

2. Apple pie: 200 kg

a. Sugar: 100 kg

b. aroma: 100 kg

so I would like to make a join to get from plan of:

1. Brown cake: 250kg

2. Apple pie: 200kg

the info:

sugar 300kg

eggs: 50kg

aroma: 100kg

2. Having the info from Materials table of price per kg the value of month plan, ie.

sugar 1 eur

eggs: 0,5 eur

aroma: 20 eur

the info:

sugar: 300 EUR

eggs: 25 EUR

aroma: 200 EUR

Is it possible to get this information using my structure?

Thanks for any hints.

Luca

[comment]

I can't follow your description. Perhaps one of these may help:

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

http://fmforums.com/forum/topic/61891-different-values-in-multiple-instances-of-same-field-in-portal/

http://fmforums.com/forum/topic/62141-portal-data-tunneling/

[SilverReed]

Hi,

thanks for prompt reply, I have analyzed all the comments and I think I am very close to get what I need,

I made some example to explain a little bit my problem. What I would like to have is the report of the quantities (and values basing on prices) of materials used to compose the recipes of the products.

Finally I wolud have the following info:

for plan 92011 I need to have 720kg of tomatoes and the total price is $3 600

Kindly please give a hint?

Regards

Luca