Advanced functionality using lookup fields


This my first post here, and I’m trying to use Airtable to manage an English language school, and I’ll try to explain as simply as possible what I’m trying to do with Airtable, that I am able to do with spreadsheet software.

I’m trying to use a base for managing and following up topics that my students have followed or not.

Basically, I set up one table with the lesson dates, one with the students, one with the topics.
For each lesson (each date), I have a list of attending students, and one or two topics that I chose for this lesson (both are links to the relevant tables)

Using “lookup”, in the Students table, I can get a list of the topics each student has already done. Likewise, in the Topics table I get a list of the students that have followed each topic.

What I’d like to do:
• for a future lesson, I’d like to be able to fill in which students will be there, and that Airtable tells me which topics I could choose, that haven’t been followed by any of the attending students
• I was able to perform this in a spreadsheet software using one column for each student, with a number in it, and an ‘if’ function that compared a row corresponding to the next lesson with all the other rows

I hope this is clear, so far I’ve been able to get a list of the topics already done by attending students at a given date, and my idea was to compare that list with a list of all the topics, and list all the topics that haven’t been done by any of those student.

I hope I’m clear enough and that someone can help me :slight_smile: Of course I can give more details and share my example base.

Thanks :slight_smile: