One Row Per 1-1 Relationship in Linked Table

[Spencer_Kier]

With the below personas in mind…

1. Client: company we’re working with
2. Event: the event we’re doing for them
3. Contributor: an individual (outside of our company or the client’s) who is helping to put on the event

I am able to store the following relationships between:

1. Clients and Events. One client can have many events.
2. Contributors and Events. One contributor can have many events.
3. Events and Contributors. One event can have many contributors.

However, I also need to be able to store additional information about each Contributor’s performance at each Event (e.g. rating, comments, fee, etc.).

I tried to create a fourth table to store the ‘additional’ information about a Contributor’s Event performance with the below columns, but I can’t seem to get one row for each relationship. Instead, I am seeing multiple Campaigns for each Contributor (which is already stored elsewhere).

• Primary field (auto-numbered; not used)
• Campaign (link to Campaign table)
• Contributor (link to Contributor table)
• (Additional fields)

Suggestions?

[Justin_Barrett]

Welcome to the community, Spencer! :grinning_face_with_big_eyes: Looking over the setup that you described, I’m not sure that I understand the problem you’re encountering. The described design of your fourth table looks like it would work for what you need.

Can you please elaborate on what you mean when you say, “I am seeing multiple Campaigns for each Contributor (which is already stored elsewhere).”? It sounds like you’ve got links created between your first three tables, and my only guess so far is that you don’t want to have to make the same links again in the fourth table. However, you didn’t describe exactly where the other links are made, so it’s hard to offer suggestions on how to optimize the setup.

[Spencer_Kier]

Thanks, Justin! And thanks for responding.

In playing around with this more, the original issue was user error. However, a related question is how do I auto-generate a record in the ‘Event Contributors’ table for each contributor I associate to an event in the Events table?

Here’s the desired workflow:

1. I add a new event to the Events table.
2. I associate multiple contributors to the event (the Event - Contributor relationship is 1-many).
3. In the ‘Event Contributors’ table, for each individual contributor I have associated to the event, auto-generate a record with two linked columns: one for the event, one for the contributor.
4. I go into the ‘Event Contributors’ table and manually add the additional details for that individual contributor’s performance (e.g. comment, rating, etc.).

Is there a way to do this without Zapier?

[Justin_Barrett · Accepted solution]

Spencer_Kier:

Is there a way to do this without Zapier?

Not currently. Airtable doesn’t auto-create records in any situation. Any records created are done manually, whether you’re in the table where those records will live, or you’re “remotely” adding a new record via a linked record field in another table. Zapier can help, though, as can Integromat. I’m pretty sure the number of integration services is increasing, but those two are the most popular.