I am building out a recipe management tool and am looking for help with a way to convert units of measure. For example, if I enter Butter as an item in my table that has a unit of measure of pounds with a cost associated with it, I would like to also be able to use other weight units of measure such as grams, Kg, oz/wt, etc and have the cost converted. I already have the multiplication table built out for all these conversions, but need help figuring out the most efficient way to do this. I am not sure if it makes sense to do the conversion and store the data for each possible conversion for every item in the “Items” table, or do the conversion with a formula in the “Recipe” table itself.
Here is a sample doc I have created to work on this problem.
In my point of view, and as an Engineer, I would suggest to always refer to a Master Unit for each type of quantity.
As european (i.e. metric system) :
- Weight > kg (but probably more grams if you work for food/kitchen)
- Volume > m3 (OK, we can take liter or even mL)
Anyway, build a table like this with master unit per type of quantity. You will have one unit per type.
Then, rather than making 1000+ conversion from each unit to each unit, just create the conversion to the master table. For each other unit you’ll just have to put one only conversion to your master unit.
Then, when you’ve got an ingredient, transfom your initial unit to the master unit, and the opposite to your destination unit. Then you get a global factor you can apply to convert the quantities. For that I use lookups to OtherUnit, and I filter the unit for Destination to be the same type as initial.
If you’ve a price on a give line, the price is still the same, no matter which unit this is. If you want ratio, you can divide price by quantity one to get USD per US Gallon or USD per Milliliter in example bellow.
I’ve created a new page in your sample doc, have a look and tell me if this is easier for you ?
@Quentin_Morel Thank you for the detailed reply. While I do see the benefit of having a Master Unit, from a user standpoint, it does not work for me. I want to be able to enter items in the units of measure that they are purchased.
No problem with that, my table “ingredient” allows you to enter items with whatever unit you want. What I suggest is just to always go to the masterunit of a given type if you need to make conversion, rather than creating a very huge matrix of conversion for all measurement.
for example, i would not create a wide table with
liter <> milliliter
liter <> galon
galon <> milliliter
but just galon, liter,m3, each converted to milliliter if we speak about volume.
In my table ingredients, you can really chose whatever unit you need. And with this process, you can convert it in any unit from the same type, with a smaller “master unit table” conversion
So are you recommending converting all volume units to one unit (ex. mL)? Then if I want to use a different measure in a recipe, would I be converting from mL?
Exactly ! That’s what I call a master unit for each type. It will work like a pivot point.
We do exactly the same when we learn physics at school, cause the formula you have only work with master unit.
Have a look on the sample doc I’ve created a page to illustrate this
@Quentin_Morel I’ve incorporated your suggestions and they have been very helpful. Thanks! I have built them into a new sheet (but also added your conversion page to keep it handy). New Sample Doc.
On this new doc, I have all the items on the “Item List” being converted into the Master Unit (g or mL). Then on the recipe database, I convert to the desired unit from the Master Unit.
Now I have more things to work through that I hope you can help with.
The way I have the recipe conversions set up, it is allowing the user to select a volume measurement for items with a Master Unit that is a weight and vise-versa. I would like to be able to either filter the selection box named “Unit of Measure” on the Recipe table or at least have a way to error check this by giving a blank result for the price if the units don’t match up. I solved this problem
The other thing I want to be able to do (that would make my request above even more complicated) is convert items from a weight to a volume. This would be based on a conversion factor which would be specific to the individual item (example: the conversion from flour pounds to cups is .3197). So to tie in my other problem, some items would have both a weight and volume, and others would only have one of the U/M.
Yes I see, according to density of each product, you will have several factor to transform a weight in a volume. Logical.
Honestly, this is more a problem about physics rather than coda
Still using Master Unit, you have to define the density of each element. For water it will be 1, for oil 0.960, etc… of course converted according to your main units.
You have no choice doing this, I suggest you create a “physical conversion table” for concerned ingredient, with unit 1, unit 2, and the factor to go from 1 to 2, and define the factor. Even if coda is superpowerful, it cannot find alone (maybe a pack or OpenAI can ).
Once you’ve got this physical conversion table, this is exactly the same processus as for different unit of the same type…
what do you think ?
Dear @Ben_Peine and @Quentin_Morel ,
Sorry jumping in, but I assume this post might be of your interests.
I have to admit, that I didn’t try it.
@Jean_Pierre_Traets I took a look at that pack and I don’t think it will work for my application.
@Quentin_Morel (or anyone willing to help) I am making progress, but again at an impasse. In a nutshell, here is what I am now doing:
“Items” are entered in the Item List table. Here they are converted to a Master Unit (g or ml), based on if the original purchase unit is a weight or volume unit.
To be able to convert from a weight to a volume (and also from one of these units to an abstract unit such as Each or Case), I created a separate table where items have different conversions. These are the units of measure that will be used in the Recipe table. So I started by each Item having a row in this table listing the Master Unit of that item with a Conversion Rate of 1:1. In the example below, Butter first has a row for it’s Master Unit, “Gram”. Then it has a conversion to volume in “mL”. Ideally based on those rows, I would be able to use other weight and volume units based on converting them.
On my Recipe table, I have played around with a Lookup of the above Conversion table based on the Item Name and the Units of Measure as the lookup items. Using Butter as the above example, the Lookup field would be populated with Gram, milliLiter and Pound. My goal is to not have to list Pound for the Butter example, but still populate the Lookup with all weight measurements (since Gram is a Unit of Measure listed) and also have all volume measurements (since this item has a volume conversion). My thinking was to use Select List with a formula, instead of the Lookup. Then I could use the LIstCombine function with some If statements. In my head that function would work like: If the Item has a weight (g) Unit of Measure, the list would populate with all the weight measurements. Same with volume measurements. If the Item has both available, it would populate the list with both weight and volume measurements. Is this possible? There may also be a much easier way to go about this that I am overlooking.