Having a form feed into multiple tables

[Doug_Tilley]

Hello! New Airtable user here, and I apologize if I’m being one of those people who are asking something very elementary. In reality, I’m just looking to see if what I’m trying to do is even possible.

I have a base that has been set up with different tabs/tables. To keep things simple, one of them is marked AGENTS (with columns marked Agent, City, Phone Number, etc), the next is marked EVENTS (with columns marked Agent, Event Type, Length, etc) and another marked MEDIA (with columns marked as Site, Agent, Type of Media).

I know how to make each table into a form which can be filled out by an “Agent”, so it feeds directly into that table. But is there a way to create a master form which fills into areas of each of these different tables?

I hope that makes sense. Basically, we want to provide a form for our agents to fill in, and have it feed into a variety of tables under certain headings.

Thanks for any help or suggestions.

[JonathanBowen]

Hi @Doug_Tilley - in Airtable a form maps directly to a table, as I’m sure you’ve discovered, so you can’t set up a single AT form to map to multiple tables.

You could merge all of your data into a single table, then one form would cover everything, but, thinking about good base design, I wouldn’t recommend that. Looking at the tables you’ve outlined above, I’m guessing that an Agent can have multiple Events (and multiple Media?), so if this is the case, a single table is a bad idea.

You could use an 3rd party form solution such as Typeform or Jotform (and using Zapier to glue everything together), which could take a single form submission and map the data to 3 different tables simultaneously, but, again, give the tables you’ve outlined it feels like you’ll end up with multiple duplicate Agent records, which you don’t want.

It is hard to tell without understanding your use case in more detail, but it feels like separate forms is a better option here:

• create your agent record once with a good and known primary key - e.g. email address
• create multiple event records using the agent’s email as a foreign key/link field
• same for media?

JB

[Doug_Tilley]

That’s some really useful information. Thanks so much, Jonathan.

And it does seem like separate forms might be the way to go, to avoid the duplication you mentioned. I think with proper instruction, that will still be workable. Just glad I didn’t spend a lot of time trying to make something work that is untenable.

Cheers!

[NYCEDU_Admin]

Hi! This is what I do. It may be similar to what you need.

Here’s the general concept: https://support.airtable.com/hc/en-us/articles/218734758-A-beginner-s-guide-to-many-to-many-relationships#junction.

Here’s my specific application:

I need to track which people have been to which events. Each person can go to multiple events. And each event has multiple attendees. To track which events each person has been to without duplicating people, I do the following.

I have a CONTACTS table, a EVENTS table, and an EVENTS CHECK-IN table.

##CONTACTS
I use the contacts table to keep track of each person. I make sure that each person has one record and one record only. I get their info by having them fill out our “Contact Info Survey” one time.

The form

##EVENTS
Each Event has a single record in my EVENTS table. I manually enter the “Event Name” and “Date”.

##EVENT CHECK-INS
Finally, I have an EVENTS CHECK-In table where attendees can check-in that they’ve attend an event. The EVENTS CHECK-IN table has an “Event” column that links to the EVENTS table and an “Attendee” column that links to the CONTACTS table. It also has an “Event Check-In Record” that’s a formula combining the “Event” name with the “Attendee” name.

I get this info into the table by having every attendee check-in on this form.

Now I have a unique check-in record for each attendee at each event!

##HOW IT ALL LINKS TOGETHER
Back in EVENTS, I now create an “Event Check-In Records” cell that links to my EVENT CHECK-INS table.

I also have an “Event Attendees” cell that looks up the names of every attendee to an event, and a “# of Attendees” cell that counts how many “Event Check-In Records” exist in the “Event Check-In Records” cell.

Here’s what I’ve done for # of Attendees

Back in CONTACTS I duplicated the “Event Check-In Records” column, but I also added an “Events Attended” column to display the Event name via a Lookup

When I have someone check-in to an event who hasn’t filled out the contact info form, I simply have them fill that one out first, then go back and check-in.

And that’s it! I now have completely manageable tables, with only one record per person, and one record per event with instantly updated info on who attended what via two simple forms.

Hope that helps!

[Moe]

@Doug_Tilley

is there a way to create a master form which fills into areas of each of these different tables?

We’ve built a tool that allows you to feed multiple tables from one Airtable form. And it does that with the least amount of work. Because the fields mapping is done inside Airtable itself.

[ScottWorld]

@Moe That is a very cool new extension you built!