recipes, semifinished product, ingredient base

[xbandx]

I would like to create a database of recipes, as follows:

first table ING_BASE:

1-ID_IngBase

2-Name of Ingredient

3-perc_fat

4-perc_carbs

...

second table ING_SEMIFINISHED

1-ID_SemiFinished

2-Name SemiFinished

3-ID_IngBase

4-Qty_gr

third table RECIPES

1-ID_Recipes

here my problem....

The recipe can contain many semifinished product and many ING_base ex:

the sugar is ING_Base, and the syrup50 is a Semifinished product ,consists of the following ingredients (ING_Base sugar and ING_Base water).

The recipe consist:

100gr of Sugar

200gr of Syrup50

how can do that, how can create the relations?

please Help ME!!!

Edited November 16, 2009 by Guest

[Søren Dyhr]

Well you're embarking on one of the more interesting topics in database development - an commendable endeavor, due to the likelyness of dipping your feets into recursive relational structures ... but the journey to getting you there where you understand what's going on, is likely put a strain on your abilities in abstract thinking.

Try to read this:

http://www.fmforums.com/forum/showtopic.php?tid/192581/tp/1/

Barbara provides you with a link along the lines of the debate which is a text of Jonathan Stark, which nails the problem as such, but from there to develop a fully fledged warehouse managment system is a lot of spit and polish required to grasp the concepts.

--sd

[xbandx]

I have create this contenitor....is right? or wrong?

I wait for your help!!

recipes.zip

[Søren Dyhr]

It's a start, try to look at this:

http://jonathanstark.com/downloads/Inventory.fp7.zip

--sd

[xbandx]

yes, i have study your example, but for me , is little bit more complicate to understand....

My mind is in REAL MODE not in RECURSIVE MODE : :

But I would like to implement the DB!!!

This is an example comprensible for me...

[color:green]Recipe 1:

Name:

..Recipe 1

Note:

..none

Fornitor:

..Fornitor 2

Tipology:

..Tipology 2

Quantity:

......500gr Recipe 3

[color:green]Recipe 2:

Name:

..Recipe 2

Note:

..none

Fornitor:

..Fornitor 2

Tipology:

..Tipology 2

Quantity:

......100gr Recipe 1

......200gr Ingredient Base 1

......300gr Ingredient Base 2

[color:green]Recipe 3:

Name:

..Recipe 3

Note:

..none

Fornitor:

..Fornitor 1

Tipology:

..Tipology 1

Quantity:

......100gr Ingredient Base 1

......100gr Ingredient Base 2

[color:red]Ingredient Base 1:

Name:

..Ingredient Base 1

Note:

..poudre

Fornitor:

..Fornitor 5

Tipology:

..Tipology 5

Quantity:

......10% Carbs

......20% fats

......50% protein

......20% other (calculating by difference)

[color:red]Ingredient Base 2:

Name:

..Ingredient Base 2

Note:

..poudre

Fornitor:

..Fornitor 4

Tipology:

..Tipology 3

Quantity:

......20% Carbs

......10% fats

......50% protein

......20% other (calculating by difference)

how can translate this into DB recursive?

please help me to undertand with a little file for examles!!

thanks a lot for everyone that can help me.

Andy

[Søren Dyhr]

is little bit more complicate to understand....

Hence my notion of an commendable endevour ... this is probably as much in the deep end of the pool you can get! If you read the thread I guided you to did I frankly admit it have taken me 5-6 years to get there. It's not challenges you eat for breakfast!

What you could do, is to read ISBN 1-55622-859-7 page 279 thru 315 to be sure to get all the needed intellectual "calculations" with.

--sd

[xbandx]

thanks, i have buy the book and i wait it about 15 day...

If you are willing to create a small sample file where I can practice to understand, I am grateful a lot..

bye

[Søren Dyhr]

I have looked a bit on your data, and sussing the context and purpose here - isn't the purpose more a task of "carving the carbs" than genuine storekeeping?

What you try to get is how many calories would a diet consist of ... although this should and could be solved recursively - might the simpler Don Wieland method be just up to it...

Would there always be modulo 100 quantities in the sets?

On the other hand here is my initial thoughts, the issue here is that all these calc's can be written more elegantly - but as such should the model be able to go to virtually any depth of recursion. But I must caution you I could easily have made an error here and there!

--sd

Recipes.zip

Edited November 18, 2009 by Guest
added a file

[xbandx]

Yes!! I think to understand...

but how can create a query for the list of ingredient for the recipe 1? (print the label of product)

I reply you the file correct (for me obviously..)

thanks a lot!!! great help!!

newRecipes.zip

[Søren Dyhr]

Could you allow me some more time with tinkering, since I've forgotten to treat an rawmeterial as it's sole ingredience - but it can't be done before after the weekend.

Meanwhile could you study this thread:

http://fmforums.com/forum/showtopic.php?tid/142035/tp/0/all/1/

...and perhaps this:

http://www.spf-15.com/fmExamples/BilloM.fp7.zip

--sd

[xbandx]

ok man..I have study...but my mind is frammented...I need your "DEFRAGGER"

I understand a lot of question in this days , but i have some basic question (for you)

1) how can create a SUM of CarbsInGram inside the table [IngreedienceItemLine] for the product 4? or generic product with TipologyID = Semilavorati?

the real calculation is: Sum of single value of Carbs (100+75+4+80)=259 Divide the single Sum of quantity (100+200+300+400)=1000 multiply 100.

259/1000=0,259*100= 25,9 Carbs for the Product (Cuore di Panna).

2)in the DietItem Layout I can't realize a correct list of Component for the single recipes...Why? I become CRAZY!!! many many hours....

But I am happy that with your help I am learning....

thanks a lot

newRecipes1.zip

[Søren Dyhr]

I think we're doing it a bit too complicated to ourselves, since Carbs, Fat and Proteins are just components as well, so Shawns (...the BilloM) model is what it really takes!

Please note when entering data in the model ... some refreshing issues exists due to the use of a related summaries undocumented behaviour ... but it was the only way of optaining the virtues with repeating calcfields - which BTW is used to reduce the number of near redundant calc'fields...

But clicking outside the fields ...seems to do the trick!

--sd

Ingreediences.zip

Edited November 24, 2009 by Guest

[xbandx]

Tanks a lot Søren..

I understand that is a "big work"..but a little steps I procedes...

Question:

why inside the Table IngreedienceItemLine I realize a correct calculation for p_zuccheri and tot_p_zuccheri but I see only 2 field correct, and the other are void!! why?

many many thanks!!

newRecipes_2.zip

[Søren Dyhr]

why inside the Table IngreedienceItemLine I realize a correct calculation for p_zuccheri and tot_p_zuccheri but I see only 2 field correct, and the other are void!! why?

Could I ask you to use the newest template I made instead, since I otherwise will I at least loose track of approaches involved ... the previous version have errors not worth digging down into any more - and I could only appologise for tumbleweed you need to kick here.

The new model sums correctly no matter how deep the recursions encountered, and if carb, fat and proteins not sufficiently covers what things consists of add an extra repetition to each and every position where the repeating field occurs.

--sd

[xbandx]

thanks I migrate into the new example...see you later!!! heheh I hope whit a big surprise.

andy :(

[xbandx]

ok i understand the examples and is exactly what I need!!

I have some question:

Q) how can create the labels of ingredients:

Ex. test2

Ingredients:Saccarosio,Cuore di Panna(Solidi Magri del Latte,destrosio,Panna Vaccina 75%MG, Saccarosio,Neutro).

or other labels

Ingredients:Saccarosio(106 gr.),Solidi Magri del Latte (39,6 gr.),destrosio (27 gr.),Panna Vaccina 75%MG (21 gr.),Neutro (5 gr.).

and after print the label...

andy

thanks a lot.

Ingreediences_Copy_2.zip

Edited November 26, 2009 by Guest

[Søren Dyhr]

Before looking at your version of the newst approach, is it simply to Add an new record, and the write the ingredients it consists of in the portal below, Alright entering a number isn't the most convenients way of doing it. Some sort of listing including all availiable components say a cartesian portal will do the trick.

Now the lists would then probably need to dwindle, to prevent taking the same thing twice ... Do you know enough filemaker to build such a portal or should I show you how to do it?

See how some sort of structured selection can be done:

http://www.fmforums.com/forum/showtopic.php?tid/192169/fromsearch/1/hl/dwindle/tp/0/all/1/

One more thing is that some sort of validation is required to prevent the ingredience or recipee gets doubled by accident.

Ingredients:Saccarosio(106 gr.),Solidi Magri del Latte (39,6 gr.),destrosio (27 gr.),Panna Vaccina 75%MG (21 gr.),Neutro (5 gr.).

and after print the label...

You need to explain this? My initial take here is that you simply create a new layout where only the label and the percentages are visible ... such a layout can be tricky indeed to make and you might investigate and borrow this:

http://fmcollective.com/2007/08/29/pseudoportals-with-alternating-fill/

--sd

Edited November 26, 2009 by Guest

[xbandx]

I have see the duplicating problem...but I think to sorting the ingredients (by familyType) before insert into new recipes..right?

You need to explain this? My initial take here is that you simply create a new layout where only the label and the percentages are visible ... such a layout can be tricky indeed to make and you might investigate and borrow this:

http://fmcollective.com/2007/08/29/pseudoportals-with-alternating-fill/

--sd

yes if you have a little examples...

thanks...

[Søren Dyhr]

I can't promise you to finish some concepts already by tomorrow, there are other assignments need for attention. But with a bit of luck might I squeeze it in!

--sd

[xbandx]

ok in the meantime...I study!!

[Søren Dyhr]

ok in the meantime...I study!!

I think I would need more than that from you, could you make two new fields in the latest version you made, something like category and subcategory, and what each ingredience belongs to logically?

--sd

[Peter (duksis3)]

I have similar task and solve it via filtering - we have Filter in functions list, isn't it?

When car dealer offer a new car to customer there is the Base Price and Option Price.

Total Price is Base Price + Options Price.

Options are different - Simple with fixed price and Complex - including several options.

In a lot of cases - during offering several Complex Options you can get the situation when these Complex Options include the same Simple option. That mean - you cannot offer right price.

For example -

Option pack 1 include electrical mirrors and electrical windows,

Option Pack 2 include electrical mirrors and audio control near the steering wheel.

Customer are interested in all options, so you need not to double price for electrical mirrors.

You knew the price for Each Simple Option.

Option packs can include up to 10 simple Options.

Via calculations (filtering) and scripts I get the right Price in every case and do not worry about what is included in very different Option packs.

It is complicated and I spend a couple of weeks to solve that.

The same can be used regarding recipes, ingredients, ingredient sets etc. to find overall ingredient weight in finished product and so on.

[Søren Dyhr]

Peter this is what basicly was suggested by making this link!

See how some sort of structured selection can be done:

http://www.fmforums.com/forum/showtopic.php?tid/192169/fromsearch/1/hl/dwin...

But making the decisions for an Icecream faktory in the northern Italy, when sitting in Denmark would probably be neglecting a few details as well as emphasing something which isn't worth it.

Filtering or dwindling is likely to be required in some sort of measure as core task, since databases have been called "Vessels Of Meaning" by some, would the avoidance of conveying irrelevance a task only possible to complete in cooperation with the user/reciever of the solution. Arbitrary chosen filtering is most likely worth nothing!

--sd

[xbandx]

ok guys.. another some basic question for you...

1)is possible to show/hide the portal if the user select differet Tipology (Tipologia Gusto)?

2) I will that in the portal (see Layout Ingredienti_Base), the list of component have a nice ICON near the name, associated to the tipology (see table Tipologia). Ihave tried with container, but nothing appears...

3)Why in the portal "Composizione" (see Layout Ingredienti_Base) there are always one or two record void? (see id_ingrediente 10 or 16).

4) I realized that I must use the Relationships to obtain data filtering, but I have not figured out how to do this in the portal..

I would like that when the user had selected inside "Tipologia Gusto" the SEMILAVORATO , and in the portal want to add a Ingredient, the list must be have only the components named "MATERIA PRIMA".

As always thanks in advance...

andrea

Ingreediences_Copy_3.zip

[Søren Dyhr]

1)is possible to show/hide the portal if the user select differet Tipology (Tipologia Gusto)?

Yes try with invisible tabs a.k.a.

http://www.databasepros.com/FMPro?-DB=resources.fp5&-lay=cgi&-format=list.html&-FIND=+&resource_id=DBPros000691

2) I will that in the portal (see Layout Ingredienti_Base), the list of component have a nice ICON near the name, associated to the tipology (see table Tipologia). Ihave tried with container, but nothing appears...

The relation is wrong the dependency lies with the ingredient ID chosen not a tipologia ID which needs to be entered each time a line is created. Please investigate this movie:

http://www.filemakermagazine.com/videos/graph-rules-four-rules-to-remember.html

3)Why in the portal "Composizione" (see Layout Ingredienti_Base) there are always one or two record void? (see id_ingrediente 10 or 16).

The portal has the "Allow creation of related records..." feature turned on - why not remove it and replace it with scripted creation of lines ... the options here are pretty comprehensive to choose from, but if you study the template i linked to some days ago will you see that clicking in the raw material portal will initiate the required scripting.

http://www.fmforums.com/forum/showtopic.php?tid/192169/fromsearch/1/hl/dwin

4)

See how 3) works! Or call Guiseppe Pupita from Buliga IT....

Then a question, why do you Extend( at all since you have removed all traces of repeating fields otherwise ... it is as if you don't get the idea of compacting near identical calc's into one single field where the position is grouping each base component in it's own row ... where they can't mix accidentally. If you don't use repeaters why bother using summary/getsummary when it can be done with aggregate functions instead. The use of summaries is only dictated by the use of repeaters!

http://fmhelp.filemaker.com/fmphelp_10/en/html/func_ref3.33.39.html

--sd

[Peter (duksis3)]

Probably I made not correct description.

Because the sample you give isn't about what I'm writing before. Similar -yes.

Filtering is very useful for Resource management (not "Resource management" like in FM samples) databases. And I learn it from sample I have told before and now have many implementations.

Regarding factory in Italy and development in Spain - every thing depend from process description and process understanding - how literate we are to find "common language".