I just realized (the hard way) Airtable has a limit of 500 fields per table…
Here’s what I need to do:
I have a table 1 Ingredients Stock, where I list all the ingredients I have. There are several types of ingredients and, among them, some are Oils or Butters, and for those there are 29 fields where I input a % (that will be the % of a specific component that the ingredient has… imagine an apple can have 12% Vitamin A and 32% polyphenols and a pear can have 25 % Vitamin A and 2% polyphenols - nonsense, but just an example).
Table 2 is My products, with the product names and dates.
Table 3 is Lab notebook, where I list all the product batches I make - I choose the product name (linked field to Table 2) and list the ingredients (linked field to Table 1) and corresponding % used - up to 20 ingredients.
Table 4 is Product Fatty Acid % - this table allows me to know how much % of a specific component (like Vitamin A and polyphenols from the previous example) does the product have - 29 records, one for each component.
- I choose the product name (linked field to Table 3) and have 20 lookup fields that fetch the ingredients in Table 2 - I copy the product name down the 29 records.
- Next to them, a linked field to Table 1 - I copy/paste the previous lookup field and all the way down to the 29th record, and this allows me to autopopulate 20 lookup field per ingredient with the component % in the ingredient.
- Another lookup field with Ingredient % used in the product (that it fetches in Table 3) - times 20, as there can be up to 20 ingredients in a product.
- One field per component with a formula that calculates component X % in product
- One last field that filters the component % in the formula (if record = component X, fetch component X % in product)
This table gets me a Blocks graph with the components % in the product.
My problems:
-
Table 4 data needs to be constantly changed, as to be able to get a graph, I need to input the components as records - so I link the product name and the ingredients and copy/paste it all the way down to get the components %. If I want to add a new product, I have to delete all the previous records and go over it again for the new product :frowning:
-
Airtable only allows 500 records (columns) per table. I’m short of columns to be able to cover all the 20 ingredients…
So what I need help with is:
-
Is there a way to get the graph organizing Table 4 by product instead of by component, so that the data doesn’t need to be replaced every time I need to input a new product?
-
Is there a simpler way to fetch the data in Table 4 other than hundreds of lookup fields?
I don’t know if I’ve made myself clear, it’s difficult to explain… i’ll gladly share the base by message! Help!!
