Advice on how to structure my base

Hi @Piano_Sensei - I don’t think you need a new table for past students, just an attribute on the students table to indicate past or present (e.g. a checkbox). You can then create two views on this table - one for current students and one for past students, using a filter on each to show the correct students.

I would also use the student current/past attribute to determine the parent current/past status. You could, I imagine, get the situation where a parent has two children and one is current and one is past (two child records, one parent record), so the status of a parent will be “current” if one or more of their children is current and “past” only when all of their children have a status of “past”.

Some screenshots of a quick mock-up:

Hope this helps!

JB

Thanks, I thought of that, but here’s the issue: I want other fields in the “past students” table like:
date of last lesson
reason for leaving
etc.

You could add these fields to the students table, hide them in the view “current students” and display them in the “past students”.

Oh wow, how do I do that? Also, how do I connect the current attribute to the parent?

Create two views:

Then use the “hidden fields” selector on the current students view:

For the status on the parent table, I’ve done it this way:

A lookup to get the child status:

Then, because this might have any number of ticks, I created another field (formula) to distil this to a simple yes/no:

Of course, you could just have the ticks if you want as this is an indicator of how many current students belong to a parent.

Wow, you’re amazing, thank you!