Base Design for Marking & Viewing Attendance

[Sistema_Aotearo]

Hi, Airtable community!

I'd love to pick anyone's brains at how I've set up my base.

The issue I'm trying to solve feels rather simple. I want to display my attendance record when it gets marked.

My current set-up:
I have two tables; One with a record of all my lessons, and one with a record of all my students. These tables are linked twice. One link shows students scheduled for the lesson, and the other shows students who attended. I have automations that let me add students from my scheduled link to the attended link with a click of a button in an interface.

I'd love some thoughts on how to display the attended students. I could have an empty "attended" field on the side that fills up as I run my automations, but I'd like it to be displayed directly on the students already displayed with the "scheduled" field.

Here are a few of my current thoughts. I could try colour conditions, but to use this accurately seems to require many dynamic conditions that don't exist yet. With the new dynamic linking, maybe making an "Attendance Record" junction table would help.

This seems like it should be rather simple, yet it feels very complicated! I'd appreciate some thoughts on the base design!

Thanks,
Sistema Aotearoa

[jossylala4all]

It seems like you've put a lot of thought into your base design, which is awesome! One way you could display attended students directly with the scheduled ones is by using a formula field to check their attendance status in your "Attendance Record" junction table. It might take a bit of tinkering, but I'm sure you'll find a solution that works great for you!

[Sistema_Aotearo]

The bit I get stuck with this formula is how to choose which "Attendance Record" to check. Or am I able to make a formula to check based on the lesson's record in an interface?

Am I able to display different information based on the lesson I click into on an interface?

I feel like my problem is that "Scheduled" and "Attended" are two different sources from the same table but I'm trying to make them more connected.

[Sistema_Aotearo]

OR maybe there's a crazy script/formula I could use that summarizes both the "Scheduled" and "Attended" link fields. But keeps all the data or somehow subtracts the attendance from the scheduled. Idk I'm just throwing random ideas out there haha