Designing a base to manage club enrollments

[Tim_Wilson]

Hey everyone,

I’m an archery coach, and I’ve been using Airtable for a few years to manage information about the people who participate in our archery club and their achievements. I’m trying to figure out the best way to track the relationships among the archery students and their parents or guardians. Note: it’s possible for someone who is a parent of one of the archery students to be a student in our program themselves. This is complicating the design.

So let’s consider two tables from my base: People and Sessions.

     ┌─────────────────┐       ┌──────────────────┐
     │People           │       │Sessions          │
     ├─────────────────┤       ├──────────────────┤
    ╱│Name             │╲     ╱│Session Name      │
┌────│Sessions         │───────│Start Date        │
│   ╲│Related People   │╱     ╲│End Date          │
│    │is_current       │       │Participants      │
│    └─────────────────┘       └──────────────────┘
│            ╲│╱                                   
│             │                                    
└─────────────┘                                    

The People table holds information about everyone: participants, parents/guardians, etc. For a participant, the Related_People field holds one or more references to other people in the People table. For a parent or guardian, that field has references to one or more of their children who are participants. Again, a parent could also be a participant in this scenario.

The is_current field is a formula field that holds a 1 or 0 based on whether that participants is enrolled in the current session. (Side note: I like to use field names that look like variables in computer programs for fields that are intended function more or less like variables.)

I can pretty easily figure out whether a participant is_current by checking to see if they are enrolled in the most recent session. The challenge is getting the parents (or any other related person) of a current participant to also show a 1 in the is_current field. I’d like to have a view in my base that shows all currently enrolled participants and anyone who has a relationship with one or more of them.

I also considered using a third table to store information about the relationships, and as a side benefit that would allow me to also store additional info about the type of relationship (i.e. parent, grandparent, sibling, etc.). So I would be open to hearing thoughts about that approach as well.

Eventually, this will all find its way to a Glide App that our team coaches can use to quickly contact participants or their parents.

[Nathaniel_Grano]

You could add a rollup column that Counts the is_current field from Related People. If that rollup is greater than zero OR if the person themselves is enrolled, show them in your view.

[Tim_Wilson]

That’s a cool idea. In this case, however, there are records in the People table corresponding to people from past sessions. This approach doesn’t distinguish between parents of currently enrolled participants and those of previously enrolled participants. I’m going to play with this idea some more though.

What I need is a way to identify whether there’s a currently enrolled participant present in any person’s list of Related People. Those records plus any current enrolled participants constitute the set of records I need.

[Nathaniel_Grano]

This solution does distinguish between parents of currently enrolled participants and those of previously enrolled participants.

In my example above, the rollup is looking at the is_current field of each of the linked People. So the resulting count is the count of how many People with is_current checked are linked to a given record. Thus, if the # of current relations field is greater than 0, it is because there was at least one linked People record with is_current checked.

[Tim_Wilson]

@Nathaniel_Granor, I see now what you did there. :grinning:

I don’t think I was sufficiently clear in my original post, so what you suggested didn’t quite work for me. The good news is that I was able to take what you suggested and make it work perfectly. For the sake of someone who might run across this thread in the future, here’s what I did.

First, the difference is that in my situation the Related People field is populated for nearly everyone. The kids link to their parents, and the parents link to their kids.

First I did a Lookup column called archers_in_current_session pointing to Related People and retrieving is_current. In my case, however, is_current is a formula field that returns True or False rather than a check box. So the result of that lookup is blank for someone with no links in Related People, a 1 for a parent with one participant or a 1, 1 for a parent with two participants, and a 0 for a parent with none of their kids enrolled in the current session.

From there it was an easy formula in a current_parent field to identify a parent with a child enrolled in the current session. Here’s the formula:

REGEX_MATCH(CONCATENATE({archers_in_current_session}),"1")

That formula gives a 1 (TRUE) when the parent has a currently enrolled student and a 0 (FALSE) for a parent without a currently enrolled student or a student themself. (The purpose of the CONCATENATE is simply to convert the integer output of the formula to a string that can be matched with the regular expression.)

Now it’s a simple matter to create an OR view that simply looks for current_parent = 1 and is_current = 1.

Thanks for the help!