Create third table with individual records for each instance of a one to many relationship already set up

[Mark_Ryman]

Noobie here with a problem that I think should be easy but need some guidance. I have 2 tables (Customers, trips) that are linked. Each customer record can show up in multiple trips and each trip has multiple customers associated with it. I want a third table that I can sort by chronological trips (represented in their unique trip codes) and have a separate record for each customer that is associated with that trip code (like trip rosters). I’ll need to pull several attributes in from each customer from the Customers table for each roster and this new table will then be used to transfer data thru zapier to another tool. I’m a little stumped on how to start for building this table. Thanks in advance

[kuovonne]

Welcome to the Airtable community!

Sounds like you want a junction table.

Once you understand the concept, there are a variety of existing scripts and extensions that can help create junction records. However, unless every customer always takes every trip, you are probably better off manually creating your junction records.

[Mark_Ryman]

Thank you for the informative article. I think you are correct that a properly configured junction table may be what I need. It was helpful for me to go through the examples and gain a better understanding of the relationships we already have between our existing tables, but I’m still not clear on how to accomplish this task. Each trip in my Trips table has a column/field called Roster that links to records in my Customers table for each person that has signed up for that specific trip. I would like for each of those customers to appear on its own line in my new table, along with their email and phone details. Each trip has between 1 and 20 customers on the roster. Since each Trip Code is unique, I’m thinking that makes sense for the primary field in my new table, but unclear how to define the relationship to get the records to display one customer per line per trip code.

[kuovonne]

Mark_Ryman:

I’m still not clear on how to accomplish this task.

Where are you currently storing trip codes?

It sounds like you already have a many-to-many relationship with a direct link between your [Trips] table and [Customers] table. You would need to create a new [Trip Codes] table and have two linked record fields: one for the {Customer} and another for the {Trip}. You would also have to manually create the records and fill them with data based on your existing data. (There might be some scripts or extensions to help migrate the data, but I am not familiar with them.) You can then add lookup fields for the email and phone details.

Another option is to stick with your current base schema, but add a “summary” formula field to your [Customers] table that concatenates the customer name, email, and phone details in one field. Then put a rollup of that summary field in your [Trips] table. That would not give you a new record per customer, and would not give you a way to have a unique trip code per customer/trip combination, though.