Skip to main content

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 ➗ 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!

Welcome to the community Lu!


You might want to start here.

Welcome to the community Lu!


You might want to start here.


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

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


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

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


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

Reply


Terms & Conditions