I'm having trouble figuring out how to relate my tables
I have a price list which has calculation fields to determine (as much as possible) unit prices for each item in fluid ounces kilograms etc. some of them are of course empty because there is no consistent way to get a volumetric measurement from say, 50lbs of flour.
That being said, I also have a recipe table with recipe name, total yield, 20 each ingredient fields, with accompanying unit, amount, unit price and prep fields. I assumed that I would need to have a separate table with fields that do the do the calculating parts like yield % and recipe scaling options. That will all be easy though after I figure out how to get the first part down.
First, I want to require each ingredient field in a record to be a value from the item field in the price list which I'm pretty sure I've got
Then, I want the unit price field to look at the unit field and display the info from the appropriate per unit price fields in the price list table that corresponds with the ingredient chosen... Make sense? In a spreadsheet application this is accomplished with nested "IF" Formulas that contain "vlookup". so basically
if(Unit="ea", vlookup(Ingredient, price list, ea, False), If(unit="fl oz", vlookup(ingredient,price list,fl oz,False) Etc etc...
Does this require a many to many relationship? Do I need some kind of intermediary table? I literally just started using Filemaker yesterday and I could definitely use some help.