Advice on how to structure my base

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

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

1 Like

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.

1 Like

Wow, you’re amazing, thank you!

1 Like