I'm positive there has to be a better way to do this, so any suggestions overall would be appreciated. I'm figuring this out as I go along, so feel free to answer as if "for dummies."
My team schedules courses and assigns classrooms manually with a basic text-entry-type student information system, and so use FM to view information from multiple tables in one place. Summer term has several "parts of term" that overlap (e.g. one program might run the full 7 weeks, where another runs the first three weeks of the term and the third just the first two weeks), and they might meet on different patterns (two days per week, four days, five days), and in four or more different time blocks (8:30, 12:00, 3:30).
Because of this, it's hard to visualize the usage of a single room as we move courses around - that is, if one instructor changes their mind about their preferred schedule, seeing where there are open rooms or potential switches that can be made is difficult. I'm hoping to set up a layout which shows what is in a single room or building at various times throughout the day, across an entire 7 week summer.
So far, I've set up multiple calculation fields using CASE to display the course name/number/instructor if the part of term meets during week 1, week 2, etc., and then broken by day. 7 calculation fields per week, by 7 weeks, but because the output of these calculations are text fields, I'm not sure how to summarize by building/room/start time so that the courses display on the same line. Week 1 Monday's calculation is screenshot below - the rest are set up the same way.
See screenshot below - here, you can see that 5 courses meet in room A in the Science building at various start times and their schedules across weeks 1 and 2. Physics 101 meets MW at 8:30 am in both week 1 and week 2, and PHYS 202 meets TTh at 8:30 am (Thursday is "R" here). Is there a way to combine/summarize these lines so that if I searched on that building and room at 8:30 am I would see both courses on the same meeting line? This one is a simple one, but there are rooms that might have 5 different courses in there each week, so seeing them all on the same line would be helpful.
I've read this: https://www.teamdf.com/blogs/a-lightweight-cross-tabular-data-entry-method and tried setting these to numbers and doing the subsummary setup, but obviously it didn't work because they really need to display text.
Any thoughts? These are not meant to be text entry, just display, if that helps - should I be looking at portals instead?
Thanks in advance.