This is more of a generic table structure question than a specific Honeycode question, but maybe a resident genius can help me out. Basically, I am trying to set up a mixing calculator. I won't go too deep in the weeds with the particulars but it is basically a recipe generator with a multi-tiered BOM. The confusing part is a mixture is made of ingredients, but due to measurement constraints sometime a mixture is the ingredient. Say I want to make a mixture of component A (1 gram) and B (999 grams). My scale is only accurate enough above 10 grams. So I make a mixture of 10 grams of component A and 90 grams of component B, then I use 10 grams of the "pre-mix" (1 gram of A and 9 grams of B) to mix with 990 grams of component B to get the final mixture.
Believe it or not that is a very simple example. Sometimes I need to make a mixture to make a mixture to make a mixture and then combine it with a mixture made from 2 other mixtures. If your head isn't hurting already, I'll ask the real question.
How do I set up a table of mixtures that references a table of components that could include mixtures? I think I just confused myself.
A mixture is a combination of ingredients and/or mixtures. How do I model this?
I gotchu! I'm using cooking as an example here, but I think this is what you're looking for. In this example, first you make scrambled eggs, then add mushrooms to create "Mushroom scramble".
So this works for using a premix in a mixture, but what about using a premix in a premix? How do you go multiple levels without the constraints of a defined number of mixture tables? How can the same mixture be used as a second level premix in one mixture but a first level premix in a different mixture?
To use your example, how would you account for using the mushroom scramble in a mushroom scramble sandwich ?
An alternative to @AndrewB's suggestion is to make a single table RecipeIngredients, with the columns Recipe, Ingredient and Quantity. Format the Ingredient column as a rowlink to RecipeIngredients. Here's what it looks like for the mushroom scramble burrito.
First we need to add the raw ingredients. They don't have any constituent ingredients, so we leave the Ingredient and Quantity blank:
salt, -, -
milk, -, -
eggs, -, -
mushrooms, -, -
tortilla, -, -
For scrambled eggs, we add three rows:
scrambled eggs, eggs, 2/serving
scrambled eggs, milk, 1/4 cup/serving
scrambled eggs, salt, 1 tsp/serving
Note that eggs, milk and salt are actually rowlinks to the records we added previously.
To extend it for the mushroom scramble, we add the following two rows:
To list the ingredients for a recipe, use a filter: FILTER(RecipeIngredient, "RecipeIngredient[Recipe]=$[InputRow]")
One table, but you can nest recipes arbitrarily deep. If each recipe can be specified entirely in percent by weight, you could also add a cost per kg column and automatically calculate the cost for any recipe. You could also easily calculate the smallest batch you can produce of any recipe given your measurement limitations.
hi @JCoop, great question. Certainly a tough modeling question. I was scratching my head for a few hours trying to think if I can add something to the ideas provided above. But I have a perverse sense of challenge so here is my thought.
What if everything is a mixture. Sure some mixtures are of one chemical alone and some include other chemicals. You are actually looking for a many to many table that refers back to itself - if that makes sense. So I created this table:
Basically the Ingredient column is a rowlink back to the same table, showing MixtureName; and I made the convention that an Ingredient's Name and their Mixture Name are the same. Also an Ingredient has no amount because that is not necessary. If you want to keep track of how much supply you have on hand that's a separate table called something like: "StockAmount." I think this models what you are looking to do. (If it creates a black hole of circular references, and violates some normal form, you did not hear it form me )
I tried to make this useable so I created a simple screen with a Column List and a search box. Here is what it looks like:
Now this is not a perfect solution and the "Create" screen may be a bit tough, not to mention the search, filter or detail. You may also add some other "hidden" fields to help your viewing screens; maybe a "MixtureOrIngredient" M | I and you can use that to decide what to show. You can also create a custom search in my screen to search only the Mixture Name (not all the columns). But I think it can be done.
Let me know if the simplicity helps and if you manage to make useful editing and reading screens.
Another great idea @DanielTet! I just got back from vacation and have a ton of work waiting on me, but as soon as I get caught up I will give all three ideas a shot and see what works best.
After a little thought (I'm a glutton for punishment, as well), the main problem I see is how to display the all mixture information when referencing a complex mixture. For example, if an operator want to make the mixture from a mixture, how would he see the components and concentrations of the intermediate mixture(s)? Is there a way to nest a list object in another list object? Something like below for a mushroom scramble.
Egg Scramble Qty. 1 cup
A. Eggs Qty. 2 each
B. Salt Qty. 1 tsp
Mushrooms Qty. 1 each
Milk Qty. 1/4 cup
In the case above, the individual components of the egg scramble are displayed within the egg scramble object. This would also need to have to work for several layers of mixtures. Otherwise, for a complex mixture, many views would need to be opened simultaneously in order to get all the relevant information needed to make the mixture.
Yes, I think you would pick "Mushroom Scramble from the list" and view the ingredients, including the "Egg Scramble" which you can indicate is a mixture (perhaps with a small M, or perhaps with the ability to click on and "drill down"). I think it will be hard to make a multi layer drill down, list, but you could call the same screen with a constituent mixture, and use a back button to go back up to the parent mixture.
...
?
)