Create a record synced to another record in the same table, for scheduling

[Drew_Lentz]

Hi All! My team uses a base for scheduling trainings, including course code, instructor, and staff assignment. In our scheduling table, we have 1 record for each class, grouped by week.

However, for classes that span more that 1 week, our team likes to have another record displayed for the following week(s). Ex: Course ABC is running over 2 weeks, we create 2 records, one for each week that the course spans. (I’m sure this isn’t best practice, but our team likes to have stand-ups to review the upcoming week, including any courses that are continuing from the week prior.) Problem is that we keep a lot of data tied to each course (enrollment, financials, etc.) that currently needs to be manually copied into the 2nd or 3rd week of any multi-week class.

Any ideas/thoughts on how to automatically create another record for week 2/3 of a class, ideally with the ability to inherit the data from the week 1 record (ongoing), so we wouldn’t need to input the same data across 2-3 records for the same class?

For context, we run 4-5 classes per week.

[Drew_Lentz]

Here’s a screenshot of the Base for context. See course code PD359 (course code is 4th column) is listed in both week of 4/5 and week of 4/12, as the class runs during both weeks. Enrollment (6th column) is entered correctly as 4 students into the Week 1 record (row #2), but as is often the case, has not been updated in the week 2 record (row #6). Ideally i’d like the Week 2 record to inherit the week 1 data. Alternatively, I could opt to not enter ANY data into week 2-3, but i’m loving the idea of those records just having all empty columns. Open to any ideas!

[Kamille_Parks]

Have you tried having a table for [Courses], with fields that store essential data which should be shown for all weeks, and a separate but linked table for [Course Weeks] which pulls in all of the Course data through Lookup fields?

[Drew_Lentz]

Hi Kamille, thanks for replying. We do have a separate table for Courses, with all course-level info (hours, materials, instructor(s), etc.) that is linked in the scheduling table shown above.

We don’t have 3rd table for something like [course weeks] as you described, if i’m understanding your suggestion correctly. Would that be a table that lists 1 record for each course session, even if it spans multiple weeks? We could then use lookup fields in the main scheduling table to link to that [course weeks] table, and create multiple records on the main scheduling tab for sessions that span multiple weeks, linked to a single record in the [course weeks] table?

I think that would help, though in that case it would be great if somehow the records on the main schedule tab could be automatically generated from the data input in the [course weeks] table? i.e. course that falls within 1 week → 1 record on the main scheduling table, but course that spans 3 weeks → generates 3 records on main scheduling table, with lookup fields for enrollment, financials, etc.

[Kamille_Parks]

I misunderstood your data structure. It does not appear you need to make a new table (what I suggested or [Course Weeks] is just your existing [Scheduling] table).

If you already have a table for [Courses] and all records in your existing [Scheduling] table are supposed to have the same field values based on the course, not the week, why not just store the values for those fields in the [Courses] table and show them in the [Scheduling] table via lookups?

[Drew_Lentz]

Ah I see. Yes, we do that now, and pull in [courses] information via lookups into [scheduling]. Issue is that 1 course could be scheduled several times within a year, so we don’t input information like enrollment, instructor, revenue in the [courses] table, but rather the [scheduling] table, as that info is tied to the particular instance, or session, of that course.

All of that works fine now, but breaks down a bit for course sessions that span multiple weeks, as our team likes to have multiple records in [scheduling] for the the same same course session if it spills into the following week (or 2). For ex, If a session of a course spans 2 weeks, we create 2 records in [scheduling]: week 1 and week 2 (see rows 2 and 6 in screenshot above). Our team inputs enrollment, etc into the first record (week 1) but often forgets to update the record for week 2 with the same info, which ends up causing a lot of confusion as to which is accurate.

[Kamille_Parks]

Okay, so you do need a third table as originally suggested. Your [Scheduling] table is your table for weeks. The third table, acting as the middleman between [Courses] and [Scheduling] would hold information about each session of the course which remains the same regardless of the week. Instead of linking weeks to courses, you’d link courses to sessions and each session to one or more weeks.

Your original interpretation of my first suggestion was correct. And there are existing scripts to generate a number of linked records based on the current record’s info (i.e. if session lasts 4 weeks, link it to 4 new week records.)

[Drew_Lentz]

Ok cool! Thank you so much for your help. Do you know the best place to look for those scripts?