Recipe & Ingredients

bradlegassick · 08-27-2020 03:57 PM

Hi - I have made a recipe costing app, but need to learn the best way to configure the data. I have 3 tables - BevDB (which is the Ingredients table with columns for Ingredient name, size, Unit of measure, cost & then necessary ID key column) - BevRecipe (which is the summary recipe table with columns for the Key, Name of recipe, Total cost of recipe(from the Recipe Detail table), Menu Category) - BevRecipeDetail (this table is set up as “A part of” the BevRecipe table with the reference being the BevRecipe Key, Ingredient1 through 10 columns, Quantity1 through 10 columns, Cost1 through 10 columns) I have managed to get this all working & calculating the correct recipe costs, saving to the sheets etc. But I am using many many ANY SELECT with _THISROW expressions to calculate the ingredient costs & pull the unit of measure through from the BevDB table. I thought there must be a better way to link the BevDB table to the BevRecipeDetail table ie use references. BUT I would need to use 10 references for the 10 Ingredient columns that all need to pull ingredient names, UoM & costs from the BevDB table. Is this possible? Or is there a better method of doing this? Cheers & thanks in advance.

Suvrutt_Gurjar

Hi @bradlegassick,

I believe you may wish to further elaborate the above statement. Do you have multiple columns in BevRecipeDetail table so that each the ingredient’s details for a recipe are listed in a single row? If so, you could use 1 row per ingredient details that could make a classic parent child table relationship between BevRecipe( parent) and BevRecipeDetail ( child) tables.

Based on your description, it sounds that the sample “Order Capture” app could be useful to you as a reference with following rough equivalents.

Sample App table;- Products , Your app table-BevDB
Sample App table;- Orders , Your app table-BevRecipe
Sample App table;- Order Details , Your app table-BevRecipeDetail

https://www.appsheet.com/samples/An-app-for-managing-customers-products-and-orders?appGuidString=245...

bradlegassick

Hi @Suvrutt_Gurjar, thank you for taking the time to give me some feedback. BevRecipeDetail does have multiple columns. Potentially each Recipe can have up to 10 ingredients. I have attached a screenshot of what the table looks like. There are potentially 10 Ingredients, 10 UoM, 10 Quantities & 10 Costs - These are all pulled from the BevDB table which is not connected in any way to the recipe table. It simply provides “Suggested Values” for all the Ingredient columns in the Suggested Value field. I am presently looking at the Order Capture app - this only has an example of 1 Product making up the order, not as in the real world potentially more than 1. So I have drawn a blank as to the best way to set the tables up.

Suvrutt_Gurjar

Hi @bradlegassick,

Yes, if one follows the pattern of Order Capture app, if there are 10 ingredients in a recipe, there will be 10 rows of ingredients in BevRecipeDetail , one each pulled from BevDB table. This in general is classic parent , child relationship.

Also BevRecipeDetail table will need to reference BevDB table.

Edit:

Not sure why you say so, there can be many products per order. Just a screenshot of an order among many orders.

bradlegassick

Hi @Suvrutt_Gurjar, so instead of having 10 columns for the ingredients - Ingredient1 through 10 - you have only Ingredient1, which somehow you enter 10 times in the form to build the recipe? Setting the BevRecipeDetail table up with 10 ingredient columns makes it possible to use Show_If to hide unused Ingredient columns & the recipe entry is done in 1 single process. By making multiple rows the user would be saving each entry of each ingredient - making a single process into multiple clicks. Do I understand this correctly?

bradlegassick

Yeah BUT you have to save each line item each time?

Suvrutt_Gurjar

Hi @bradlegassick,

Yes,you are correct, each line item needs to be saved each time. However I believe that is classic parent child relationship.

I suggested an option based on description and problem statement of many SELECT() statements being needed. I agree that you will be best judge of your requirement and associated trade off either on user data entry side or app configuration side.

I am sorry that I may not have additional sugegstion in this regard.

bradlegassick

Thank you @Suvrutt_Gurjar, I understand that the setup I initially had, with many SELECT statements works & provides the best user experience. I will persevere with that configuration. Have a great day! Thank you.

Suvrutt_Gurjar

You are welcome @bradlegassick.

Roman_Lojek

Dear Guys
I know it is a bit old but I am very fresh here. Regarding this above I have a question.
I understand that you can add meny products to one order. My question is:
if in the tables there is a recipe for one product. How you can call this product again to new order?
for example client1 bought product A with recipe (other products which create the product A). he wants to buy 1kg of the product (the recipe has product name and the qty % to give 1kg of product A). ClientB wants to buy the same product but 10kg (based on the same recipe). How can I call the same product (the same recipe). More over I would like to decrease the stock (since I am using ingredients from the stock)