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