Creating a junction table to link Product with a default number and cost for that specific product

[Lu_Jenks]

Hello,
I am very new to airtable so I don’t know if my question makes any kind of sense. I hope that you will bear with me. I make bath & Body products. So far I have 3 tables with their related fields

1. Ingredients: Name, #Amount/PU (PU= Packaging Unit, e.g. 128 oz), $ Price/PU (e.g. $38), Fx Unit cost (per oz of material: Price/PU :heavy_division_sign: Amount/PU). I entered all the raw materials I use in my products in the Ingredients table, with their Amount/PU Price/PU.
2. Products: Here is where I am having a problem. Below are the fields:
   Name ( e.g. Body lotion), Ingredient 1 (Jojoba Oil - Linked to records from ingredients, I don’t know how to type the symbol), #Amount/PU, $ Price/PU, #AMT (Amount used in batch), Fx Cost of AMT {AMT 1}*{Price/PU 1}/{Amount/PU 1}. I have these fields for all the ingredients used in the body lotion.
   Then I have the following fields:
   a) Fx Cost of Batch: {Cost of AMT 1}+{Cost of AMT 2}+{Cost of AMT 3}+{Cost of AMT 4}+{Cost of AMT 5}+{Cost of AMT 6}+{Cost of AMT 7}+{Cost of AMT 8}+{Cost of AMT 9}+{Cost of AMT 10}+{Cost of AMT 11}+{Cost of AMT 12}
   b) #Number of Units per batch
   c) $Cost of Unit.
   My problem is, when I enter one ingredient, the Amount/PU and Price/PU are not automatically populated with the values already entered in the Ingredients Table. I have to enter them manually. After reading the guide for beginners several times and trying to create a Junction table in vain, I decided to come here to see if someone could help me. Thank you very much!

[Bill_French]

Welcome to the community Lu!

You might want to start here.

[Lu_Jenks]

Thank you so much Bill! I will go there now!

[Mohamed_Swella1]

HI @Lu_Jenks,

In addition to what @Bill.French said, I do think you are having problem because you are adding the amount and the PU in the same field, making this field not recognizable as a number, but rather a single line text (correct me if I’m wrong). You should separate the amount and the PU and make sure the amount field is a Number field so you can use it in the formula.

If you need more help, let me know.

BR,
Mo

[Lu_Jenks]

Hello Mo,

Thank you so much for your response. In fact, the “Amount/PU” is just the title. It’s only one value, i.e it is the amount of ingredient in a bottle or a box or a jar as it is packaged. So for instance, when I buy coconut oil from the store, it comes in an 84 oz jar. and this field is a number field in my table.Then the Price/PU is a $field, and corresponds to the price of the 84 oz jar. After spending 3 hours (!!!) on it, my husband was finally able to find the answer by using “Lookup” and linking “ingredients” (where I had already entered the list of ingredients with the corresponding Amount/PU and price per PU) with a new formula field in the same table called “unit cost of ingredient” : Fx Unit Cost = Price per PU/Amount per PU.

As I go along using the database and filling the forms, I will probably come across something I will need help with. When that happens, I will be sure to take you up on your offer.

Thanks again and be safe!

Lou