So I’m new to Airtable and things seem very different to what I am used to in respect of MS SQL Server as a former software developer and DBA where I would write endless complex stored procs and triggers etc. Now a restaurant owner, I decided to write a basic recipe and pricing calculator to help me with the ever changing prices of stock since covid-19 but am already stuck so I’ll simplify an example of what I’m trying to do with a with a minimum amount of tables and fields for now as per below diagram of 3 tables:
So the first table ‘external_ingredients’ contains products bought from suppliers with the cost and the weight of the item in grams in this case.
The ‘internal_ingredients’ table contains the recipe of external ingredients used to make the internal ingredient, and the percentage of the cost per ingredient based on the weight of what we need to make it, as well as a sum of the cost and the sum of the weight by the grouping.
The 3rd table however ‘FINAL_RECIPE’ is more of a representation of what I want it to look like rather as this is where its going wrong. I want to be able to pick an ingredient from either of the 2 tables mentioned above, which is dead simple to do in MS SQL SERVER by using a stored proc and a temp table, which is what I’m kind of missing here…its like i need a table in between the 2 ingredient tables and the final recipe table, that holds the 2 ingredient tables in a separate new table if that makes sense. Any ideas how I can achieve something like this or what I showed in the diagram would be greatly appreciated.