Adding records to a linked table

[andyh]

I have a customer (athlete) table that is one row per customer. And a comms table, that logs when each customer posts something in our community (circle) via Zapier.

Then, in the customer table I use a rollup to display the date of each customer's last post.

The problem is I keep going around in circles trying to figure out how to link the 2 tables.

Each customer has a unique athleteID  and circleID, so it should be circleID that is the link between the 2 tables (I think?). BUT when I add a row to the comms table, it looks like I need to know the athleteID too. Isn't the whole point that the circleID is the 1-to-many link between the 2 tables, and athleteID shouldn't need to feature in the comms table?

Is it because I'm trying to link tables on a field that isn't the primary key? And if so what's the best way of achieving what I need to?

Thanks!

[Micki_O_Neil · Accepted solution]

The link field has to be the primary field or record ID. 

There are two ways you could do this:

1) Simple but not recommended: make the circleid the primary field in the customer table. It's unique and would make this process quite easy. But then searching by that primary field in other cases would not be intuitive or easy.

2) Use an automation (either within the same Zapier automation or directly in Airtable) to find the customer record with the given circleID and paste that record id into the linked field. If you already have a Zap set up to create the records, you just need to add a prior step to find the associated record on the customer table based on the circleID field.