Filtered group or junction table?

[Erin_Alter]

i have two tables that are linked. People and Service. Many different People Records can be linked to one Service Record. Additionally, one People Record can be linked to more than one Service Record.

Service Records include date fields- Start & End.

i would like to display a list of all of the People Records who are linked to any Service Records that fall within a specific date range. i want the list to show each People Record individually, with some key information from both their People Record and the associated Service Record. (e.g. from People Record- name, cell phone, emergency contact; from Service Record- country, start date, end date).

i tried creating a junction table, but when i used the fancy junction table script i realized that was a big mistake. i also tried building an automation that triggers when a Service Record enters a view (filtered for the date range i’m aiming at), it creates a record in a different table and lists the people. But i couldn’t figure out how to separate the many linked records.

Is it possible for the many linked People Records in the Service Record to be broken out back into individual records?

[Kamille_Parks]

Yes, that’s possible. But have you tried making an Interface to view your data in this way? You can have a “Record Review” layout for the Services table that displays the People Link field as a table.

That way you can look at each service (and even have a Filter element tied to the list of services to narrow down time period) and see a list of all the people linked to that service in a table-grid. Each person would show as their own line and you can hide/show the columns you need.

If you need to do more than display your info, your Automation can be modified to create separate junction records for each Person linked to the Service, though this would likely require a Run Script step as opposed to using the Create Record step.

[Erin_Alter]

thanks for the reply and suggestion. i haven’t tried using interfaces yet, but i’ll see what i can do.

i was hoping to be able to set up a view that i could share with someone who is not a collaborator. but if this is the best option, i can make the person a read-only collaborator.

i really do want to be able to have a master list of everyone though, not broken out by Service. using the example that i’ve seen on junction tables (from GAP consulting) with the class roster and taking attendance, i want to give the principal of the school a summary of all the students in all the classes for the day, alphabetically. i don’t want to break them out into individual classes, like the teachers need. i want the report that sums up all the teachers’ reports.

[Kamille_Parks]

That’s perfectly valid! Especially if you want to share with folks outside of your base.

Is it possible to show a screenshot of what you’re trying to achieve, and the script you tried but didn’t like? Depending on how much work needs to be done I can point you to the modifications to automate creating the junction records.

[Erin_Alter]

So i tried using the “Create Junction Table” script that is in Airtable extensions. And it does very clearly state that it will create every possible combination of records in the two tables chosen. i just do not have much experience with scripts, so it didn’t sink in just what that would mean. i would end up with almost 300,000 records.

so i don’t have exactly a screenshot of what i’m trying to achieve, but i do have two that might give more detail.

The first one is a screen shot of my Service Table. It shows a filtered view of only those service records that fall within a set timeframe. As you can see, there are a variety of people records linked to each service record.

The second screen shot is of my People Table. It shows a filtered view that is looking for all of the people linked to one specific Service Record.

The People view is closest to what i am trying to deliver, as far as being a simple list of people with key information associated with them. It can be sorted alphabetically by name.

Right now within the People table, i can filter based on Service name, but not service date. So i could filter for more than one Service name, but i would have to do that manually. I’d like to be able to set up a view that filters based on the start and end dates of the Service Table view.

i’m sure part of my problem is due to my explanation. i’m not sure i’m communicating exactly what i’m trying to do. but hopefully this helps. i do appreciate your thoughts!

[Kamille_Parks · Accepted solution]

Okay that helps. Since People can be linked to multiple Services, here’s an approach you might try:

Create 2 Rollup fields in the People table. One for the earliest {Start Date} of linked Services, and one for the latest {End Date} of linked Services. For “earliest” use the MIN(values) formula, and for “latest” use MAX(values).

Since these are now fields in your People table, you should be able to filter by time range in addition to or in place of filtering by the service name.

This requires no script or automation.

[Erin_Alter]

THIS IS EXACTLY WHAT I WAS TRYING TO DO!!! Thank you. that did it.

i haven’t used rollup fields before. i’ll do some additional testing to make sure that it works consistently. but being able to set up additional criteria on which records are considered before rolling up seems to have been the key i was searching for.

thanks so much!