Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Jun 01, 2018 09:13 PM
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.
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!!
Jun 06, 2018 10:59 AM
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?
Jun 06, 2018 06:26 PM
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. :winking_face: 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.
Jun 06, 2018 06:36 PM
Huzzah! :tada: :confetti_ball: :tada: :confetti_ball: … :mage: :star2: :star2: :zap: :japanese_ogre: :zap: :star2: :star2: :zap: :princess: :zap:
Jun 06, 2018 07:36 PM
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!
Jun 07, 2018 08:48 AM
I’d love to see this base. Can I get an invite?
Jun 07, 2018 09:38 AM
I agree, following this thread since the beginning, this looks pretty interesting :grinning_face_with_smiling_eyes: .
Maybe a we’ll see an “Airtable Universe” version of it some day :winking_face: …
Aug 19, 2018 03:25 AM
Hi
I wanted to say that this base is so impressive, it shows the vastness and true potential of Airtable at it’s best. It’s truly inspiring.
MK
Oct 22, 2018 01:24 PM
@Filipa_Didier
I would too love to see this base!
Can i get an Invite?
Thx!
:m:
Apr 19, 2019 04:49 AM
I just made the base available on Airtable Universe - GOYA - Cosmetic formulation app - Airtable Universe
Or through this site: https://goyabase.wordpress.com
Thank you, @W_Vann_Hall!!
May 08, 2019 06:54 PM