Need some help with base structure for reporting
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)
I’ve done a little scheme:
The relationships:
- Many Collegiates go to a Trip
- Many Collegiates have a Menu Type
- Many Menus are from a given Menu Type
- A FoodItem could be in many Meals, and a Meal could have many FoodItems (we need a junction table)
- A Meal could be in many Menus, and a Menu could have many (we need a junction table)
Airtable allows you to create Many-to-Many relationships without the junction table, but if you want to store the ammount of an Ingredient on a Meal, or the amount of Meal in a Menu, you need them. Those are many-to-many relationships: https://support.airtable.com/hc/en-us/articles/218734758-A-beginner-s-guide-to-many-to-many-relation....
If you create them you could enter (and them Filter, Summarize and son on) so much data, being able to break it down by Meal, for instance. How much Almond do you need now for the Samosas? You can know that now.
I’m a little missing with your base, but I think your Order Sheet view can’t be right, because the amount of Ingredients (FoodItems) depends of the Meals, and you have not that information.
What are Trips? You have related them only with Menu Types. Every Trip is done by people with the same Menu Type? If not, that is the problem. That’s why I have added the Collegiates table.
I’m creating a demo base, and I’m thinking I miss more tables :grinning_face_with_sweat: , or I have to modify some relation/s.
I’ve changed a few things:
- I think a Trip has a Menu Type related, isn’t it? I’ve created that relation
- Also, I think you need to assign a Menu to a Collegiate, then you could count how many Collegiates have a given Menu, and then multiply by Meals, then by Ingredients.
I can’t do more today, I leave a shared link to the demo base.
