Value list for a Calendar layout

[blissland]

I have a calendar layout that I'm using for a classroom.   On the List layout, each row is a week.  Each day has its own Topic field and a Lesson Field (e.g. MonTopic, TuesTopic, MonLesson, TuesLesson, etc.)

I have a Lesson table and a Topic table, and each Lesson record includes a topic field which is used to join the two tables.  The Calendar table is not related to either table.

On the Calendar layout, the topic field is filled in via a value list which is generated from the TopicName field on the Topics table.  

On this Calendar layout, I would like the Lesson field to be filled via a Lesson Value List that is filtered by topic after topic is selected.  I have no idea how to do this.  Do I need to have the calendar table related to the topic or lesson table, and if so, via what field?  

I have tried settling for a value list that is not filtered but rather displays both the topic and lesson, but this gives me a strangely incomplete list of lessons.  I'm aware that such value lists won't list duplicates, but it seems to provide a list that is close to a "no duplicates" rule but actually shows me two records that have the same topic, so it's not purely following that rule.  

[comment]

I am having trouble following the real situation you are tracking.

I gather that one topic has many lessons. Does a lesson have a date? Or is the calendar table used for the actual scheduling of the lessons (which would also mean that the same lesson can be scheduled more than once)?

Apart from that, having a field for each day of the week is a bit problematic. To do what you want (I think?) you would need a relationship showing only lessons in the selected topic. And with each day of the week being a field you would need a dedicated relationship to a separate TO of the Lessons table for each day of the week. I am not saying that's not possible or even unheard of. In fact, it might be easier to set up than the alternative. But it will add more fields to your calendar table and clutter to your graph, and any change you make will have to be replicated to all days of the week.

 

Edited December 10, 2023 by comment

[comment]

Just another thought: 

If you don't need a relationship between the calendar table and Lessons, you could bypass the entire issue by scripting the process of selection (e.g. select from a card window). That would save a whole bunch of fields and TOs.

 

[blissland]

Having a separate relationship for each day of the week did cross my mind as the logical solution but seemed too messy to be the best option.  Taking a step back on the calendar structure, I haven't imagined a solution that wouldn't use a separate field for each day since records in List view occupy rows.  If I wanted a calendar solution with each day as a separate row, then I could structure this differently, but I want a regular calendar layout.  

A lesson will get reused every year, so it doesn't intrinsically have a date.  But it could be reassigned a date each year on the lesson layout.  I've considered this, but I'm not sure if that's better than scheduling the lesson to the date on the calendar.   Yes, the same lesson could be scheduled more than once.    Also note that there are mornings and afternoon sections for each date, so I have monday morning and monday afternoon fields for both topic and lesson on the calendar table.

I get how scripting would save me TOs.  But how would that save me fields?  I want the calendar to show the topic and lesson in each day of the calendar, so wouldn't I need a field for each of those for each day regardless?

The next part of this plan is to count the number of classes on each topic, so I just mention that in case that would be relevant to the best way to set up the whole thing.  I wouldn't think a TO vs scripting solution would make a difference for this function, but maybe there's an issue I haven't realized yet.   

 

[comment]

42 minutes ago, blissland said:

I haven't imagined a solution that wouldn't use a separate field for each day since records in List view occupy rows.

There are ways to display individual events in rows, using either repeating calculation fields or side-by-side portals.

42 minutes ago, blissland said:

 get how scripting would save me TOs.  But how would that save me fields?

It depends. I'll get into more details if you decide to go that way. However, I see a potential  problem here:

42 minutes ago, blissland said:

The next part of this plan is to count the number of classes on each topic

That's not going to be easy with your proposed structure. The correct way would to create an "event" record for each occurrence of a lesson, then figure out how to display these events in a calendar view (maybe even use a web viewer or an add-on for this?)

 

 

Edited December 10, 2023 by comment

[blissland]

I've never used repeating fields before and didn't even know what they were until today.  I don't have a good sense of when that is useful or not useful relative to having a related table.  I'll have to sit with all of this for a bit.  I like the multiple tiny portal idea.  Thank you for all of this....

[blissland]

I've never designed a solution with a card window before.  Am I correct in understanding that the usual purpose of designing like this is if one wants to transfer data from one table, via a script, to an unrelated table, such as is the case here?  

[comment]

It's certainly an important use case, but not the only one. Only recently I used it to pop up a portal on top of another portal, and I am sure there are more that don't come to mind at the moment.

 

Edited December 11, 2023 by comment