Unit Conversions

#1

Hi! I’m hoping to use Airtable to help manage crafting materials. I’d like to record purchased amounts using the unit of sale (say, yards of fabric, or pounds of flour) and set up a way to automatically convert that to the actual unit used when crafting (like inches/feet of fabric, or ounces/cups of flour). Ideally, of course, I’d be able to enter such conversions once somewhere, and have a formula field automagically apply them.

I figure the solution is either:

  1. A “Conversions” table with fields Unit1, Equals#, Unit2 and some sort of Lookup/Rollup automagic in the Materials table
    or
  2. Some sort of funky formula with a ton of nested IFs (blech!)

Any ideas? I’ve tried searching both Google and the forums here but haven’t turned up anything.

Kind regards,
Jess

#2

I think I may have a working solution after playing with it a bit. Here’s the steps, in case anyone else comes looking for a similar solution.

Conversions table, with fields BuyUnit, ConvertRate, UseUnit and the primary key formula {BuyUnit} & " to " & {UseUnit}
Example records: foot to inch, foot, 12, inch

Purchases table has UnitConvert field as a link to the Conversions table. Makes it easy to simply start typing “foot” and see a list of possible conversions. The lookup fields UseUnit and ConvertRate are based on this link. Then I also have formula fields to convert from BuyQty to UseQty, based on ConvertRate.

I hope this helps anyone else! :slight_smile: