I run a collegiate outdoor program, and I’ve been hoping to use Airtable to systematize the food planning and ordering for our trips. I’ve got a basic structure set up that works, but I am having trouble getting the reports out of it that I need. (If this topic is better put in another forum, mods should feel free to move it.)
Right now, I’m working on our orientation program, which involves about 450 participants on about 48 different trips. Because of various individuals’ food needs, different trips will have different menus (i.e., nut-free, gluten-free, etc). Here’s the table structure so far.
Food items are placed into meals. Meals are then placed on menus, which adds a date component (i.e., peanut-sauce pasta with chicken happens on a certain date). Trips are then assigned to menus based on participants’ needs.
So far, this works great. As I hoped, I can use different views in the Food Items table to generate order lists for our various food vendors.
Where I struggling now is how to generate a list of the items and amounts we need to pack for any given trip. (i.e., Trip 15 gets 35 oz of spaghetti, 24 oz of peanut butter, 2 oz of soy sauce, etc). I’ve been experimenting with two conceptual approaches:
- Use Page Designer
- Get the needed lists from filtered views
Strategy 1 (Page Designer) seems to fail because one can’t easily display data from more than one table away. For example, I can make a Page Designer block based on the Trips table, but then I can’t get a table in the Page Designer view that shows all the ingredients (from Food Items) along with calculated amounts.
Strategy 2 (using grouped and filtered views) fails because I haven’t yet figured out a way to bring over information about a specific trip. For example, most trips consume pasta in multiple meals. If I build a view in the Food Items table, I haven’t figured out how to have calculations done based on the amount of pasta needed for that individual trip. All the math leads to me to all the pasta needed for all the trips, which is useful for ordering but not for packing.
Is there a strategy here I am missing? Or am I misapplying the strategies I have? I don’t have a background in databases or coding, just learning as I go, so it’s entirely likely I’m missing something fundamental.
Here’s a link to the base with some data for those who are so inclined:
(Link to base)