Advice on how to structure my base

[Piano_Sensei]

I own a music school and keep records of current and past students. Right now I have a table with current students. One field is “parents” which I have linked to the “parents” table with all their contact info. What I want to do is also have a table of PAST students, which would still link to their corresponding parent. However, in the parents table, I don’t want it to show both current and past student’s parents. Can I set up something so it will show in the parents table which ones are current and which ones are past?

Thanks

[JonathanBowen]

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

[Piano_Sensei]

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.

[JonathanBowen]

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

[Piano_Sensei]

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

[JonathanBowen]

[JonathanBowen]

Create two views:

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

[JonathanBowen]

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.

[Piano_Sensei]

Wow, you’re amazing, thank you!