One product many recipies

[SilverReed]

Hi Guys,

I do have the following structure:

Finished product (related by id) RecipeInsert (related by recipeID) RecipeDetails

Finished product (related by id) RecipeContainer (related by recipeID) RecipeDetails

Couple of products has the same recipes, ie:

Icecream vanilla (packed into box with label)

Icecream lemon (packed into box with label)

I have the problem using data from Finished product table in RecipeDetails table

Ie. I have in Finished product table the info about the product weight I want to use in RecipeDetails table for calculation (ie. to get the information about necessary quanity of the boxes). Because the recipe is related to more than one product the FM takes the product weight for different productID.

let's say I make a layout using Finished product table as source:

I put header and portal. On portal I put the data from RecipeDetails

And for two different records I have the following situation:

For Pro001

Pro001 (weight:20g) <--> Rec 001 <--> RecDetail001 (product weight:20g, from Pro001)

Pro001 (weight:20g) <--> Rec 001 <--> RecDetail002 (product weight:20g, from Pro001)

Pro001 (weight:20g) <--> Rec 001 <--> RecDetail003 (product weight:20g, from Pro001)

For Pro002

Pro002 (weight:40g) <--> Rec 001 <--> RecDetail001(product weight:20g, from Pro001)

Pro002 (weight:40g) <--> Rec 001 <--> RecDetail002(product weight:20g, from Pro001)

Pro002 (weight:40g) <--> Rec 001 <--> RecDetail003(product weight:20g, from Pro001)

Is there any chance to make proper relation?

Kindly please help me to understand where I do wrong.

With Regards

Luca

[comment]

Can you explain what exactly your solution is tracking in real life?

[SilverReed]

Ok let me describe:

I have the database where I store the materials. The materials can be: Finished Product, raw material, package.

The Table looks like:

Materials:

materialID,

name,

kindOf <FinishedProduct, rawMaterial, package>

IngredientsRecipeID,

PackingRecipeID,

singleContainerWeight

now I have Recipes table for this products:

Recipes:

recipeID,

productID,

recipeName,

recipeType <rawMaterials, packages>

pricePerKg

Recipes have details

RecipesDetails:

recipeDetailID,

recipeID,

materialID (here I use the TO of Materials database so I have ie. price per item or kg),

quantity

So I can have the following relation:

1. Product Icecream TYPE1 (PRO0001)

a. Recipe for raw materials per 1000 kg (REC001)

sorbitol 200

maltitol 600

water 180

eggs 20

b. Recipe for package (REC002)

cartoon 50

plastic bag 50

and another one:

2. Product Icecream TYPE2 (PRO0002)

a. Recipe for raw materials per 1000 kg (REC003)

sorbitol 100

maltitol 500

water 280

eggs 120

b. Recipe for package (REC002)

cartoon 50

plastic bag 50

The problem is when I compose the layout with the FinishedProduct as the main table with the following graph relation:

FinishedProduct --<materialID:materialID>-- RecipesRawMaterials --<recipeID:recipeID>-- RecipesRawMaterialsDetails

FinishedProduct --<materialID:materialID>-- RecipesPackages --<recipeID:recipeID>-- RecipesPackagesDetails

the layout looks like:

-Header----------------------------

Name

singleContainerWeight

--------------------------------------

Now I put 2 portals

Portal->RecipesRawMaterialsDetails

Portal->PackagesDetails

and in Portal PackagesDetails I would like to use the information from FinishedProduct record (imagine if I know the dosage wight, I can calculate the cost of the raw material per single dosage, ie. the cost for the sorbitol per 20 gramms of product is $0,04).

And here occurs my problem:

when I choose the PRO0001 I have the data from PRO0001 in portal rows, but when I switch to PRO0002 still get the data from PRO0001.

It seems like I can use the REC0002 only once for PRO0001 because it is keeping the relation to data from this record (like relation one-to-one) but I would like to have the situation one recipe can be used in many products.

Is is possible?

Regards

Luca

[comment]

I am afraid you are - again - describing your database, not what happens in real life. It's hard to follow, and impossible to say if it's correct or not.

[SilverReed]

Ok I will try to show my problem in words:

I would like to make a calculation of finished product.

The product consists of tablets (recipe for tablets) and from package (recipe for packing)

The layout consists of the following sections:

1. Product details

2. Tablets details

3. Package details

In case of one product one recipe I have no problem

In case of many products one recipe I have a problem

for examples in product section I define:

1. product weight

I want to calculate the production demand, so I put the calculation field in the recipeDetails table with the following content 1000/productWeight*HowManyKgINeed -> than I have the quantity of the selected material.

As I wrote before everything is fine when I have one product == one recipe if I have many products to one recipe, I get an error, there is always taken the value from first product

ie.

first product (0,8kg) ----- first recipe ---- detailsLine (calculationfield: productWeight: 0,8kg)

second product (0,12kg) ----- first recipe ---- detailsLine (calculationfield: productWeight: 0,12kg) -- here I would like to have 0,12kg but it seems the first recipe keeps a link to first product although it should match with the second product.

I hope now it is more clear.

Regards

Luca

[comment]

Well, it's getting clearer, but...

Let's take a simple example: 1 recipe, 3 materials. The recipe requires:

• 2 units of Material A;

• 3 units of Material B;

• 4 units of Material C.

Next, let's have two products using the same recipe. Now, (1) what is the difference between the two products and (2) what exactly do you want to calculate for each product?

If, as I suspect, you want to calculate the exact quantities of each material for a given product, you need to select that product first - and the calculation will be correct only for the selected product out of the two products using the same recipe.

[SilverReed]

Hi, thanks for your posting,

so I have the following idea, situation:

generally for one set of product I need a recipe A consists of:

• 2 units of Material A[fileds, material name, standardQty, QtyforProduct(numberofSetsFrom Product* units)];

• 3 units of Material B[fileds, material name, standardQty, QtyforProduct(numberofSetsFrom Product* units)];

• 4 units of Material C[fileds, material name, standardQty, QtyforProduct(numberofSetsFrom Product* units)].

per one set

thats correct,

now

I have the calculation, with

Product Green 14 sets

so if I should have the recipeDetails as follow:

• 2 units of Material A, 2*14;

• 3 units of Material B 3*14;

• 4 units of Material C 4*14.

Product Blue 16 sets

so if I should have the recipeDetails as follow:

• 2 units of Material A, 2*16;

• 3 units of Material B 3*16;

• 4 units of Material C 4*16.

but in my case I got:

Product Green 14 sets

• 2 units of Material A, 2*14;

• 3 units of Material B 3*14;

• 4 units of Material C 4*14.

Product Blue 16 sets

• 2 units of Material A, 2*14; <- the recipe takes qtyValue from first record

• 3 units of Material B 3*14;<-the recipe takes qtyValue from first record

• 4 units of Material C 4*14.<-the recipe takes qtyValue from first record

I do connection using:

products -[=]- recipes -[=]- recipesDetails

so I think the problem is the relation products -[=]- recipes is one-to-one instead of many-to-one

?

Can you let me know what I would do better?

[comment]

Let me make this as simple as possible: a calculation field can calculate only one result per record. In my example, there are:

• 2 records in the Products table

• 3 records in the RecipeDetails table

Depending on where you place the calculation field, you can have either 2 results or 3 results. The problem is that you want to get 2*3 = 6 results at the same time - but there is no table that has 6 records.

Possible workarounds:

1. Calculate the results for one product at a time; i.e. when you are viewing the Green product, the calculation will multiply by 14; when you switch to the Blue product, it will multiply by 16. In this solution, you will never see both sets of results at the same time;

2. Use a custom function in the Products table to return multiple values in the same field (requires the Advanced version, and the result of the calculation is a list - i.e. it cannot easily be used in further calculations);

3. Change your structure, so that each product has its own set of records in the RecipeDetails table.

Note also that for some calculations, you could simply multiply the sum of individual results by a factor in the Products table; for example:

• 2 units of Material A * $10 = $20;

• 3 units of Material B * $20 = $60;

• 4 units of Material C * $30 = $120

Total cost per one unit of recipe = $200

To calculate the cost of a product, we use a calculation field in the Products table =

RequiredUnits *  Sum ( RecipeDetails::cCostPerUnit ) 

[SilverReed]

Hi I made example database, to show my problem

if you find a minute, please go to calculation layout and switch between 8th and 9th records

then go to materials leyout and see the connection between the products and recipes

i tried to invent workaround but it seems I am to short for that

RGS and many thanks for your patienty and given support

Luca

[comment]

I don't think your file adds anything new.

[SilverReed]

so there is no possibility to use the same recipe for two products?

if if try to use in packagesRecipeDetails the information from Materials table (like calculatingFactor) which is different for record 8 and 9, I always get the value from 8th record, what I think would happen is to get different values from different master records

[comment]

From the point-of-view of the RecipeDetails table, any reference to a field in the Products table will get data from the first related record in Products.

As I have already mentioned, you could select a product (by placing its ProductID into a global field) and use another relationship to get the data from the selected product. Using a OnRecordLoad script trigger, you could automatically select the currently viewed product.

[Lee Smith]

Hi I made example database, to show my problem

if you find a minute, please go to calculation layout and switch between 8th and 9th records

then go to materials leyout and see the connection between the products and recipes

i tried to invent workaround but it seems I am to short for that

RGS and many thanks for your patienty and given support

Luca

Please do not double post.

I have unapproved your post and deleted your file in the sample topic. The Articles, Tips, Techniques & Solutions

is for

Member contributions including articles, tips, tricks & techniques. Please provide links to previously posted threads, and demo or sample files & open source solutions. Discussion and dialog for implementation and development

Upload your sample file by attaching it to a Reply to this thread.

If you have any questions about this action, please contact me by Private Message.

Lee

[SilverReed]

Hi comment, thanks again for your posting, I am trying to summarize my knowledge?

So finally If I have to products, which have different volume (120ml and 140ml) and the same recipe (eggs, water, flour), and I want to get an info how much of each ingredient use (120ml * 23g of water or 140 *23g of water), I have to put twice the data to database? IE.: Recipe for 120 ml, Recipe for 140 ml? The only difference between them is the volumeFactor in Product table.

Hi Lee, trying to send the PM, but there is an error:

The member Lee Smith cannot receive any new messages.

Sorry for not using the option "attache to the reply" but I can not find the icon (I have only my media, and link)

OK Guys I found!

What I did is: I defined the global filed gMaterialID in the materials table, and put the script trigger into the layout (onrecordload) and with the script I force the filed to update with actual materialID

In recipes I made the fields masterID with the calculation = gMaterialID now I see the related recipe by relation, and the materialID of the master product by calculation. The same thing I will do for calculation factor.

Many thanks for your hints.

Luca

[comment]

I defined the global filed gMaterialID in the materials table,

You should define a global gProductID field in the Recipes table and a relationship:

Recipes::gProductID = Products 2::ProductID

Then, in the calculation field in the RecipeDetails table, use Products 2::Factor. Have the script set the gProductID field to the current product's ProductID.

[SilverReed]

Hey,

it is even better!

When the field is in recipe (not in product) table it seems it is even more simple to control the content (the script seems to work more efficient - without delay).

Thanks a lot!