Using Views in Airtable to Improve Workflows

[@GarethPronovost]

Join me for our next *FREE TRAINING WEBINAR* here: www.garethpronovost.com/webinar-registration

[@openaircollect]

Gareth, you are the man! Such valuable content you share here. Thanks!

[@GarethPronovost]

I appreciate that, thanks OpenAir!

[@openaircollect]

@@GarethPronovost Hey I'm trying to figure out if a particular hack of the Kanban view on airtable is possible. Ever seen anything like this? A yes/no would be be awesome! thanks man. kzbin.info/www/bejne/qYOml6FsmNGjb6M

[@GarethPronovost]

@@openaircollect replied on the vid!

[@moroco.design]

So useful! Thanks! Just discovered your channel and I can’t stop learning Airtable! You’re an awesome teacher! 👏🏻👏🏻

[@GarethPronovost]

Thank you! 🙏 Happy to help

[@Chez4136]

This is the nerdiest way to grocery shop.... And I LOVE IT!!! Ultimately it will save time, and avoid wasted money (by avoiding last minute take out and having less food waste). And wasted food is terrible for the environment. Nerdiness for the win!!!!

[@GarethPronovost]

I'm about to buy a smart fridge so that I can pull this up directly on my refrigerator door! 🤓

[@richardfugelsang4603]

New to Airflow, loving your Tubes on AirTable. Thank you.

[@GarethPronovost]

Glad to hear it, Richard - thanks for watching!

[@collinsoninteriors5743]

Great videos-very concise and clear!!

[@GarethPronovost]

Thanks for watching!

[@michaelbecker6173]

Thanks I am an Airtable Novice and enjoying your tutorials

[@GarethPronovost]

Awesome - thanks for watching Michael - glad to know they are helpful!

[@Annazumi]

You had me at liquor store, ...and Costco(!) Thank again for a great vid

[@GarethPronovost]

Ha - right? Thanks for watching!

[@Devinza11]

Loving your demo's! Thanks for sharing, Gareth. : )

[@dennistucker1153]

Nice template.

[@kathrynwest1072]

Really like it. Been thinking about doing something like this myself. Bento box sounds good! Yellow tail gives me a headache though haha

[@GarethPronovost]

That bento recipe is 🔥!

[@juanitajean70]

This is great. Thanks for sharing.

[@GarethPronovost]

Thanks for checking it out!

[@dmcfarren]

Hey Gareth, love your videos about Airtable! I'm one of the owners of a small guest ranch and we just started using Airtable for Project/Task management and we're trying to figure out how we could use it to manage the meals we serve our guests and the shopping we have to do each week. We have a pretty fixed menu that we rotate through every 2 weeks during the guest season. The main thing that changes from week to week is how many guests we're serving. So we want to be able to indicate which meals (which are made up of one or more recipes) we'll be serving that week and how many servings we'll need, and then have Airtable tell us what ingredients we need to purchase. This base you created is almost exactly what we're looking for! A few questions/comments: 1) I noticed the same thing Erin did about the quantities. Could you add a Recipe-Ingredients table that creates a many-to-many relationship between recipes and ingredients which includes a Quantity field that would be the specific quantity of the ingredient for the specific recipe? That way you could avoid the multiple line items on the shopping list for the same ingredient, though I assume it would require a more complex formula that uses a Rollup field(?). 2) Like Dirk, my original idea was to have an inventory of ingredients that would be reduced as we prepared meals and increased when we went shopping, though I realize this introduces a whole other layer of complexity. It would eliminate the step of having to physically check to see if something is in stock when preparing the shopping list, but then again, it adds the step of having to update all the quantity-in-stock fields whenever we go shopping. So maybe more trouble than it's worth. 3) On a more generic note: Is there a way to do a "lookup" of a value in a table that is more than one level above the current table? In other words if I have a three level one-to-many hierarchy of tables, let's say Meals to Recipes to Ingredients, is there a way to lookup a value in the Meals table from the Ingredients table by going through the Recipes table? Or in the many-to-many example I gave above for dealing with the multiple ingredient listings, is there a way to to look up the Number of Servings (which is in the Recipe table) from the Ingredients table by going through the Recipe-Ingredients table? Again, love your videos and appreciate all the advice you're providing to the Airtable community.

[@GarethPronovost]

Hey! Wow, there's a lot to unpack here! Would you prefer to just hop on a call to review your questions? You can find my consultation link on my website.

[@dmcfarren]

@@GarethPronovost Since writing this 2 weeks ago I've come up with solutions to most of what I asked about. 1) I created a Recipe-Ingredients table that creates a MTM relationship between Recipes and Ingredients, and I moved the quantities of each ingredient needed for each recipe to that new table. I also used Rollup fields and modified the formulas so the Shopping List adds up the quantities of each ingredient needed for the different recipes and only lists the ingredient one time with a total amount needed. Another thing I realized is that ingredients are used in recipes using one unit of measure, but are purchased using a different unit of measure, so I added Recipe Unit (RU) and Package Unit (PU) fields to the Ingredients table along with a "Number of RUs in a PU" conversion multiplier. Along with that, I also created a Units table with all the different units of measure and a Units Converter that contains all the conversion multipliers to go from one unit to another. 2) I let go of the idea of creating and maintaining a running inventory of ingredients, since that just seemed like more work than it was worth, and would require not only updating every time ingredients were used to prepare meals, but also every time we went shopping to replenish ingredients. So for now we'll just stick with the methodology you use, which is performing a physical inventory to see what ingredients we actually need to purchase, and updating the "Already in Stock" field before going on a shopping trip. 3) In regards to looking up values in a table that is more than one level above the current table, I figured out that I just need to add a Lookup field to the table in the middle. I'd love to share with you our resulting "Meal Planning" base and see what you think. And I might still give you a call just to talk about this stuff further. Thanks! Dave McFarren

[@avarycooney1499]

@@dmcfarren Are you able to give any insight on how you achieved point 1? I'm trying to set up something similar and having a heck of a time.

[@nhillborn2]

Do you have a copy of the base with the views (shopping list, etc) included as well?

[@GarethPronovost]

If you copy a base then the views are copied with it. However I'm not sure if this template is still live, this video is getting old. 😅 We did an updated video not too long ago though!

[@nhillborn2]

@@GarethPronovost thanks! I was looking at the old one. The new one does have the different views I was looking for.

[@colibrcolibr6103]

Incredible template

[@GarethPronovost]

Thanks!

[@erindaugherty6108]

Love this template! I only have one problem. When I add multiple recipes, there are usually a lot of the same ingredients: butter, milk, etc. For example, I am in shopping prep mode, and there are three different lines for butter, three lines for sour cream, and two lines for milk. Do the quantities not add together and appear on one line?

[@GarethPronovost]

Glad you're liking the template - and you make a great point, Erin! That has become a problem in my use of this as well. Perhaps the preferred solution would be to create ingredients without a quantity. This way, you could link every recipe directly to those ingredients. The way it's currently built, since each ingredient has a quantity, it's treated as a unique record in Airtable. This is what leads to having multiple records of "milk" instead of adding them all together. Personally, I like having the quantity there and I just do a quick addition in my head if I see a double or triple entry. Hope this helps!

[@TheCourtney20clark]

Hi Gareth- I am trying to create my own base that has the same concept as yours. I am a beginner but would like to figure out how to create spreadsheets on my own. The problem I cant seem to figure out is, when I am checking in the field "Put on the List" (which is the same as your Recipe Checkbox) for the recipe to make for the week I can't get a view in the ingredients tab to only show me the ingredients for those specific recipes. I created the different views for each step of my process but it doesn't seem to be flowing correctly. Thank you!

[@GarethPronovost]

Hi Courtney - thanks for the comment! Sounds like you have most of the pieces correctly in place - I think you just need to create that "view" that shows only the ingredients on the list. You might want to check out this recent video on filtering views: kzbin.info/www/bejne/eoWTpXWtgdacmbc Sounds like you want to see only those records that have been "Put on the List" - to do this, you can create a new view that is filtered to only show records where that checkbox is selected (hiding all records where it is blank). Hope this helps!

[@FITOProductions]

Great job on this video👌

[@TheHow2Channel]

Thanks for the great tutorial. Once you get the information into Airtable, how do you export it out into printed our digital form? I'm a newbie.

[@GarethPronovost]

Hi Shanene - thanks for watching! It's pretty easy to export the data. On the toolbar, you can click the ellipse (...) to see more options. Form there you can select "Download CSV" to export the data to a Comma Separated Value file. Or, you can select "Print View" if you want to print the data. Hope this helps!

[@bryanlauth8253]

Is there a link to get this template? Did I miss it in the video notes?

[@GarethPronovost]

It might have been lost over the years. You can check out a read only version here and create a duplicate for yourself! airtable.com/invite/l?inviteId=invt5Jzq485N7mVQP&inviteToken=7eba9238ef6d19a1c79c36faec8c6399b8c2781af16efc77b540a3f45f143d5e

[@stephenbelyeu2172]

What would you do if you had common ingredient used like Salt or Ground Beef but had it in multiple recipes and with each recipe the amount is different? Would you have to have like 15 salt records in the ingredient field on the Ingredient table?

[@GarethPronovost]

Great point Stephen. The way I built this template, unfortunately, would require you to add the same ingredient multiple times. That said, we will soon be releasing an update to this video with a free downloadable template that is improved from this tutorial. Stay tuned!

[@TheScootermac315]

@@GarethPronovost Would love to see this! Any news on update?

[@GarethPronovost]

@@TheScootermac315 - so many new features and tools being released that we had to shelve this idea for the time being... sorry! Hopefully we have a chance to pick it back up again soon!

[@alexfullerton8099]

@@GarethPronovost can you share the original template you created in the meantime?

[@jaydencoventry7153]

Are you just duplicating the base each week and then deleting the old one once you go shopping, or is there a better way to reset the shopping list?

[@GarethPronovost]

No - I just delete the checkmarks to reset the base each week!

[@leonardlow5342]

What happends after u hv gotten ur groceries ? Do u go in an uncheck every single checkboxes ?

[@GarethPronovost]

Hey Leonard - yes, back when I recorded this video some years ago that was the case. Now though, I have an automation than automatically unchecks everything once a week.

[@misswandalove]

What about adding cost per ingredient and adding the total cost? Is there a tutorial video for that?

[@GarethPronovost]

Hey, I like your thinking! That's a great upgrade to this template, thanks for the feedback! Sadly, I didn't include that for this version of the template, but that would be an awesome upgrade. Also, I was thinking a cool improvement would be to add calorie counts to the meals for people who like to watch their caloric intake. I don't have a tutorial for that specific request, but it would involve a Roll Up Field to calculate the Total Grocery Spend. I definitely need to create a tutorial around the Roll Up Field, since I've received a few comments around it recently. I'll get something out for that soon! Thanks again for the feedback/comment! :D

[@misswandalove]

Entrepreneurship by the Numbers awesome, thank you! In the meantime, I'm going to look up the Roll Up field.

[@hawkeyeve6939]

hi gareth im starting to use airtable i will like to know if a can make a dashbord for inputs instad of using the table instaedi mean EX:nasdaq price: XXXX S&P price: XXXX DJI price XXXX and that auto field the table of the price of the dayand have then a formula and query to show me the winings of the day

[@GarethPronovost]

Hey HawkEyeVe, for this I'd recommend that you create a table for Tickers and another table for tracking Ticker Price. Every day, you can create a new record for the Ticker Prices and relate back to the Ticker through a linked table. This way, you can record ticker prices over time, allowing you to graph them or apply various other formulas. Hope this helps!

[@MGons2002]

How do I move a few items from table 1 to table 2? It seems like it should be easy. But, I can't figure it out. Help!!!

[@GarethPronovost]

You can build an automation to recreate a record in another table, but most of the time, you don't want to recreate records and move them from one table to another. This is generally representative of a spreadsheet mindset, not a database

[@קלעסק-פתרונותאוטומציהלעסקים]

where can i find the template link?

[@GarethPronovost]

use this link and then create a duplicate for yourself! airtable.com/invite/l?inviteId=invt5Jzq485N7mVQP&inviteToken=7eba9238ef6d19a1c79c36faec8c6399b8c2781af16efc77b540a3f45f143d5e

[@קלעסק-פתרונותאוטומציהלעסקים]

@@GarethPronovost YOU ARE SO AWESOME! THANKS!!

[@rosbyduhart5884]

So what would I do if I was making a database for Pet groomers and I have clients with more than one dog?

[@GarethPronovost]

Dogs would be one table and Clients are another table - then build a link between these tables so that each dog is assigned to one client, and each client can have multiple dogs.

[@ericyeh1995]

it seems like the template isnt working anymore.

[@rfonagy]

Where is the link to the this airtable base?

[@52traits]

Any update?

[@rfonagy]

@@52traits No not yet. I did make my own however that works well. I can share it.

[@GarethPronovost]

Dang, you all went back in time with this request! This video is almost 2 years old, but surprisingly I still have the database here: airtable.com/invite/l?inviteId=inv6vWCzsqePa9uKU&inviteToken=5dfdcaab71cca7b96f1c1363690525ee36956f33376a17009613ad376654e4c5 This is a read only link, but you can create a copy for yourselves from this template!

[@rfonagy]

@@GarethPronovost Thank you very much Gareth, Great work on all the videos Thank you so much

[@rfonagy]

I have been meaning to contact you, I have designed a Practice Management software for Accounting but need to fine tune it. Now with stacker, this could be bigger than jetpack and other Practice Managment Software. It has timesheets tracking and just about everything else. I wanted to see about fine tuning it and then selling it to other Accountants as they have expressed interest. I would like to contact you about this.

[@dirkjandewit2712]

How about creating a table with things you have in stock. This could first of all be used to select the recipe. For example you have cauliflower, make a view with all cauliflower recipes. And second of all when making the grocery list, can you have airtable check if it is already in stock and then move it from stock to "reserved stock". For example you add a recipe that needs 1 can of diced tomatoes. You have 4 cans in your pantry. When you add the recipe to your grocery list, it will not add the can of tomatoes because you have it in stock. But it will remove 1 can from your stock.

[@GarethPronovost]

Yes! Great points! All of that is possible with Airtable, although I tried to keep this example simple for the sake of brevity. However, what you've described is similar to solutions we build for our clients.

[@dirkjandewit2712]

Gareth Pronovost Would be awesome if you could consider doing a video about some of these advanced features!

[@naomidiaz3672]

Thanks.

[@GarethPronovost]

Any time! Thanks for watching!

[@kevinleesmith]

A view is a query is a question....

[@christianfarley]

The man knows his priorities. First stop: The liquor store!

[@GarethPronovost]

I mean, let's be real... 🤣

[@SabriCanOkyay]

She also must be in software field then

[@danjackman01]

You should delete this video, update the template, or something. And Lime Soy Tofu is not tasty.