Drill Down Summary

[Zach_Varnell]

Hello Airtable Wizards,

I am in the process of developing a high school master schedule. At current stage, our goal is to be able to move classes from period to period while using blocks to both monitor total seat count by period and also to drill down to see the seat count, by period, BY GRADE COHORT. (ie. in addition to how many total seats in 1st period, how many are accessible to 9th graders given previous set “open to” parameters.

We have the following tables:

In the “Classes” table we’ve established the course offerings, number of sections, which grades the course it open to, and total number of seats by class concerning the entire master schedule.

In the “Teachers” table we have laid out our 8 period schedule and identified who will teach what. As you can see, we’ve got a block to calculate total number of seats available by period.

While it is important for us to shuffle the schedule to ensure that there is total seat balance between periods, we also need to see that each grade has the appropriate number of seats to meet it’s populations needs. Keep in mind that not all classes are open to all grades.

This (below) is as far as I’ve gotten in terms or pulling the “Open to” data from the “Classes” table. My Rows are what I want but I cannot figure out how to get the right settings/formula for the Columns to reflect each of the 8 periods and the data to communicate the number of available seats to the corresponding grade.

What am I missing?! Thank you for the help!

[Mohamed_Swella1]

Hi @Zach_Varnell,

Welcome to Airtable Community ! :grinning_face_with_big_eyes:

As I can see, you already have each period as a Field in your Teachers Table, this is not the best practice in Airtable. Better, use a new table (call it Periods) and link to this table what ever you want. i.e.: for the first record in Teachers, you will have 8 records instead of 1. OR, you can do that in the Period table instead and leave it the same in the Teacher table.

BR,
Mo

[Zach_Varnell]

Hello @Mohamed_Swellam,
Thank you for the reply! Can you please clarify a few things for me? When you say I have 1 record in the Teacher’s Table I’m not sure what you mean. Isn’t each Teacher considered a separate record in that Table? I’m wondering what a new Teacher Table would look like with periods serving as the record. How would the fields be filled in a way that helps visualize a master schedule?

For now I have taken your advice to establish a new Table titled Periods and established records labeled “1-8”. Again, I am unclear about the field set up and how that would give me the ability to create a block to monitor seat count by grade level?

Thank you for you patience and support, I am very new to this.

Best

[Mohamed_Swella1]

Hi @Zach_Varnell,

In this new table, you will have a record for each teacher in each period. Then you will link this table to the teachers table, so now in the teachers table you will have 1 cell that has all the periods they are working in. This is a general comment, it might come in handy later.

Now that you have done that, I dont see in the course table the number of students that are enrolled in each course. Do you have a list of students? How are you counting how many students are enrolled in each class and which period?

I think I need to see the table so I can help you more, if your table doesnt have sensitive info please share a view only link in a message.

BR,
Mo

[Zach_Varnell]

Here is the link to the CLASSES table that shows which the classes we are offering, the number of seats available in each, and to which grades they are OPEN TO.

This is the link to the TEACHERS table which we’ve used to map out the 8 period schedule.

Here is the link to the new PERIODS table that I am hoping to better understand how to use to achieve our goal.

The goal again, is to develop a block that tells us how many seats are available in the master schedule by Period, by Grade Level.

I keep coming back to the CLASSES Table and feel like there has to be a way to build something there with some roll-up from the schedule layout under the TEACHER table…

Thank you!

[Mohamed_Swella1]

Hi @Zach_Varnell,

Just as I was saying, it doesn’t seem that you have any data for the Classes by Period. i.e.: You have the total number of seats, but where is the occupation by Period? For example, English 9/10, you have total 250 seats, and it is spread over 10 classes with 4 teachers and over 3 periods. You do not have the data saying which class with which teacher has how many registrations, so therefore you cannot achieve what you are looking for with this setup.

Now, in order to do so, In this new table (Periods), you need to do the following:

As you can see in this screenshot, you should add the Teacher, Class, Period, total seats, and available seats. Link all these fields to your existing Tables.

Take a look at it and let me know if you are stuck.

BR,
Mo