HELP! Reached Airtable field limit


#1

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


#2

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


#3

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


#4

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? :wink: )


#5

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


#6

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!


#7

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


#8

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


#10

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…


#11

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


#12

@Filipa_Didier

Looking through your base now. At first glance, it appears the description of [Ingredients Stock] you gave in your initial post actually applies to [List of Ingredients] — at least as far as your sample base goes. Is that correct, or am i looking in the wrong place?


#13

@Filipa_Didier

I’ve PMed you a link to my modified version of your base, as I wasn’t sure how public you had wanted it. Frankly, if you’ve not made it public, I recommend you don’t: It is truly one of the most impressive bases I have ever seen, and it undoubtedly represents substantial intellectual property. I honestly found it quite inspiring: No only have you done an amazing job of leveraging Airtable both to capture and to facilitate your work flow, you’ve also made excellent use of Airtable Blocks as a way to isolate and document core business functions. @Zoelle_Egner, @Katherine_Duh, @Shani_Taylor, @David_Peterson and anyone else at Airtable tasked with helping users make the most of the product, I strongly urge you to contact @Filipa_Didier and ask if she’ll allow her base to be shared within Airtable for reference and training.

—— or maybe you’d be better off asking if she’ll share Version 2.0 of the base, as the current version is a bit of a mess. (On Filipa’s behalf, the current incarnation is probably more correctly Version 0.8 or 0.9, as most of the ‘mess’ is merely unused or redundant fields spawned during development.)

Again, out of concern for protecting potentially proprietary data, I’ll comment mainly over PM. What I can say in open is that I was able to implement a mechanism that greatly reduces both the amount of data retained and the amount of work required to enter and manipulate it.

For example, while my variation still requires a separate table to drive the Chart Block displaying fatty acid percentages, I was able to replace your 352-field [Product Fatty Acid %] table with a [New Fatty Acid %] that not only contains only four fields but is also entirely auto-generated. :wink: Admittedly, I had to beef up a couple of other tables a bit, occasionally with the help of some rather ugly formulas; however, most of the time I made Airtable write its own ugly formulas, which I then copy-and-pasted into the appropriate formula configuration field.

Caveat: I focused primarily on solving your maximum-field issue (and at the same time, I hope, providing some UX improvements). As a result, I have no doubt that, were I to go through the base and delete those fields I consider no longer necessary, I would break other critical functionality. I think you should simply be able to modify your routines to access my equivalent fields — that is, I don’t think I managed entirely to eliminate any data points — but there’s always the possibility I did.

I’ll take the remainder of my comments to a private channel.

Once again, a very impressive base, and I thank you for the opportunity to work with it.


#14

Huzzah! :tada::confetti_ball::tada::confetti_ball::mage::star2::star2::zap::japanese_ogre::zap::star2::star2::zap::princess::zap:


#15

Wow, thank you, @W_Vann_Hall!
Yes, the base is messy, there will be a lot of clean up to do at the end…
For the fetching of the ingredients’ components, in the beginning it was done through the List of Ingredients’ table. But then this was wrong, because the List of Ingredients should have the average components % and the Ingredients Stock should have the actual components % of the specific ingredient used in the formulation (like an apple can have an average 2% Vitamin A, but the particular apple I used in a particular recipe has 4% Vitamin A).
So I started changing this… when I sent you the base, the Lab Notebook was already fetching the components from the right place (the Ingredients Stock), but the Product Fatty Acid % hadn’t been changed yet.

Anyways, thank you so much for the time you lost with this, I really can’t thank you enough!

I’m still to receive your PM… nothing arrived to my Inbox!


#16

I’d love to see this base. Can I get an invite?


#17

I agree, following this thread since the beginning, this looks pretty interesting :smile: .
Maybe a we’ll see an “Airtable Universe” version of it some day :wink: