HELP! Reached Airtable field limit

[Filipa_Didier]

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:

1. 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:

2. 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:

1. 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?

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

[Filipa_Didier]

I am completely blocked with this… could someone help, pleeeease? @W_Vann_Hall @Jeremy_Oglesby @Julian_Kirkness?

[Jeremy_Oglesby]

Go ahead and send the base invite over - I’ll try to take a look later tonight. It’s tough to imagine the implications of your layout from just a text explanation. That is an impressive amount of lookup fields though!!

[W_Vann_Hall]

Feel free to bounce ideas off me as you work. I’ve worked on a couple of things lately that were nothing like @Filipa_Didier’s problem but used techniques that might apply… The obvious solution is to rotate her base 90 degrees — but just how that might be accomplished escapes me.

(I predict the answer will probably involve having ingredients entered one per record, using a custom ARRAYJOIN() call to roll them up into a portmanteau variable, passing them to another table, and breaking them back out a component at a time — but, then, isn’t that my answer to virtually everything? :winking_face: )

[Filipa_Didier]

yes, it is! i’m sure there’s another way, but i’m completely stuck, i just can’t figure it out! i just sent you the link.
thank you!!

[Filipa_Didier]

i’ve read your posts where you explain this method (which is great, by the way) and i’ve tried to see how i could use it in this base, but in my case there are so many variables i just get lost!

[Adam_Dachis]

Hi! If you still need help with this, let me know. I’m not sure I’m fully grasping how you set it up because it’s hard to visualize, but if you still need help/want more suggestions please send me a link to the base and I’ll see if I can help sort it out. :slightly_smiling_face:

[Andy_Lin1]

I might be misunderstanding your table layout completely, but is there a reason why the contents of Table 4 can’t be added to Table 3? (You could then group Table 3 by product batch to get subtotals.)

Table 1 - Ingredients Stock
[Short Text] Ingredient Name
(29 Fields) [Percent/Number] Components
[Link] Table 3

Table 2 - Products
[Short Text] Product Name
[Link] Table 3

Table 3 - Lab Notebook
[Formula: {Product Name} & {Batch #} & {Ingredient}] Product Batch Ingredient *1
[Link] Table 2
[Number] Batch #
[Link] Table 1
(29 Fields) [Lookup, Table 1] Components
[Number] Ingredient Amount
(29 Fields) [Formula: Lookup * Ingredient Amount] Component Amounts *2
(Optional) [Link] Table 4

*1: You can theoretically leave the Ingredient field out, but that might create confusion with Table 4
*2: I forget if you can simply use a Rollup instead of a Lookup in the prior set of 29 fields to do this calculation.

(Optional) Table 4 - Product Component %
[Short Text/Formula] Product Name + Batch #
[Link] Table 3
(29 Fields) [Rollup, Sum(values)] Sum of Component Amounts

The optional Table 4 allows you to get the subtotal information within the cells (rather than in the group headers with Table 3 alone).

[Filipa_Didier]

Andy_Lin1:

Table 3 - Lab Notebook
[Formula: {Product Name} & {Batch #} & {Ingredient}] Product Batch Ingredient *1
[Link] Table 2
[Number] Batch #
[Link] Table 1
(29 Fields) [Lookup, Table 1] Components
[Number] Ingredient Amount
(29 Fields) [Formula: Lookup * Ingredient Amount] Component Amounts *2
(Optional) [Link] Table 4

hi andy!
the problem is, by adding in Table 1 29 lookup fields per ingredient, since I can have up to 20 ingredients per product, these lookup fields alone take 580 columns…

[Andy_Lin1]

Oh, I forgot to mention, in Table 3, each ingredient of each recipe gets its own record, similar to the setup in this answer:

Quantity of Ingredients in Recipe Manager Ask the community

To log the quantity of ingredients, you’ll need to make a separate table for recipe line items. Here’s a tutorial on implementing line items (which also contains an explanation of why you need to ... The example in that tutorial is related to the product catalog example, but I’ve also built a sample recipe base myself which uses the same concepts: [image] Ingredients table, with cost per cooking unit. [image] Line items tabl…