Oct 04, 2022 06:59 AM
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 │ │Sessions │ ├─────────────────┤ ├──────────────────┤ ╱│Name │╲ ╱│Session Name │ ┌────│Sessions │───────│Start Date │ │ ╲│Related People │╱ ╲│End Date │ │ │is_current │ │Participants │ │ └─────────────────┘ └──────────────────┘ │ ╲│╱ │ │ └─────────────┘
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.
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.
Oct 04, 2022 10:43 AM
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.
Oct 04, 2022 02:23 PM
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.
Oct 04, 2022 02:52 PM
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
Oct 04, 2022 07:59 PM
@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:
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!